I'm trying create a update for a new structure of tables from a old table, but not using functions. I'm trying create a script to it.
The old table is like this:
-- old table (OldTable)
name| col_a | col_b | col_c |
--------|---------|---------|--------|
ABC | 0 |NULL | 1 |
DEF | 1 | 1 | 1 |
GHI | NULL | 1 | 0 |
And, new tables:
-- Table Users
ID | NAME
----| ---------
1 | ABC
2 | DEF
3 | GHI
-- Table Rules
ID | RULE_NAME
--- | ------------------
1 | col_a
2 | col_b
3 | col_c
4 | col_d
-- Table UserRules
ID_USER | ID_RULE
------------- |-------------
So, I'm needing populate the table UserRules with the result from a select on TableMix where the user name is equals to the user name on table User and the value for column in TableMix is equals 1. (select from OldTable where OldTable.name = Users.name).
Well, I'm trying this:
DO $$
DECLARE rules CURSOR FOR SELECT column_name FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'OldTable'
AND data_type = 'numeric' AND column_name NOT IN ('foo','bar');
-- "foo" and "bar" are another numerc cols, but not define rules.
DECLARE users CURSOR FOR SELECT name FROM public.Users;
BEGIN
FOR ruleName IN rules LOOP
FOR userName IN users LOOP
EXECUTE format('SELECT COALESCE(%I,col,$1) FROM public.OldTable
WHERE name = ''$2''', ruleName, username);
-- insert on... populate the table Rules after get the id of the rule in table Rules if the result of select in OldTable equals 1 (or true if has a "where ... = 1")
END LOOP;
END LOOP;
END $$
After this, I don't have progress…
Someone help me? Thans!
UPDATE
This way I can get the real value:
DO $$
DECLARE
rules CURSOR FOR SELECT column_name FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'oldtable' AND data_type = 'numeric'
AND column_name NOT IN ('foo','bar');
names CURSOR FOR SELECT username FROM public.users;
res integer;
BEGIN
FOR rulename IN rules LOOP
EXECUTE format('SELECT %s FROM oldtable WHERE nome = %L', rulename, 'USER NAME') INTO res;
-- IF res > 0 THEN
RAISE NOTICE '%', res;
--END IF;
END LOOP;
END $$
Result the real integer value.
But if I use a loop for usernames, it's doesn't work.
DO $$
DECLARE
rules CURSOR FOR SELECT column_name FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'oldtable' AND data_type = 'numeric'
AND column_name NOT IN ('foo','bar');
names CURSOR FOR SELECT username FROM public.users;
res integer;
BEGIN
FOR name IN names LOOP
FOR rulename IN rules LOOP
EXECUTE format('SELECT %s FROM oldtable WHERE nome = %L', rulename, name) INTO res;
-- IF res > 0 THEN
RAISE NOTICE '%', res;
--END IF;
END LOOP;
END LOOP;
END $$
So, the resul is always .
Where am I going wrong?
Best Answer
I think you might be looking for the
USING
clause:You use
$1
,$2
, etc for placement-parameters, passed byUSING
. You use%I
for itentifiers expanded byformat
.Update: If you want to coalesce the column name
ruleName
if it's null, perhaps you want something more like:??
You need to think about order of evaluation. What's part of the generated SQL, and what's evaluated in order to create that SQL?