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, the new tables:
Users
:
ID | NAME |
---|---|
1 | ABC |
2 | DEF |
3 | GHI |
Rules
:
ID | RULE_NAME |
---|---|
1 | col_a |
2 | col_b |
3 | col_c |
4 | col_d |
UserRule
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 <col_name> in TableMix is equals 1. (select <col_name> 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 numeric 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.
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 result is always . Where am I going wrong?