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?

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?