use variable to determine the name of a column in the select in Postgres

Question

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?

• Craig Ringer: Copied and pasted to stackoverflow.com/q/26122833/398670
• GustavoAdolfo: PS: The first error occurs because the column name is printed with parentheses. "SELECT (<col_name>) from ...."
• GustavoAdolfo: Yes, because there are people who are not in both groups.

Answer 1

I think you might be looking for the USING clause:

EXECUTE format('SELECT COALESCE(%I,col,1ドル) FROM public.TableMix 
 WHERE name = 2ドル', ruleName)
 USING (username, whateverTheSecondParameterIs); 

You use 1ドル, 2ドル, etc for placement-parameters, passed by USING. You use %I for itentifiers expanded by format.

Update: If you want to coalesce the column name ruleName if it's null, perhaps you want something more like:

EXECUTE format('SELECT %I FROM public.TableMix 
 WHERE name = 1ドル', coalesce(ruleName, 'col'))
 USING (username); 

??

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?

• GustavoAdolfo: Thanks! But the initial problem persist: column name is printed with parentheses. "SELECT COALESCE(<col_name>) from ...."
• Craig Ringer: @GustavoAdolfo Hang on, are you trying to coalesce the column name, i.e. "if the column name is null, use the column name col instead"? If so, you have to do that outside the dynamic SQL string, in the format argument list.
• GustavoAdolfo: pfff!! Yes man! Just the obvious... :) Sorry guys, after a while I could not see the obvious... And, I do not even know pgsql (yet). :( Now... how get the result and progress this work? I no have idea how to do this without giving a lot of laps.
• Craig Ringer: @GustavoAdolfo EXECUTE ... INTO. See the pl/pgsql manual, "dynamic SQL" section.
• Craig Ringer: @GustavoAdolfo Probably best accept this answer if it's helped then, and follow up with a new question if you're stuck on something else later.