dynamic SQL ERROR: column "age" does not exist

Question

postgres 12

I am trying to loop through a table which has schema , table_names and columns

I want to do various things like finding nulls ,row count etc. I failed at the first hurdle trying to update the col records.

table i am using

CREATE TABLE test.table_study ( 
    table_schema text,
    table_name text,
    column_name text,
    records int,
    No_Nulls int,
    No_Blanks int,
    per_pop int
);

I populate the table with some schema names ,tables and columns from information_schema.columns

insert into test.table_study select table_schema, table_name,  column_name
from information_schema.columns
where table_schema like '%white' 
order by table_schema, table_name,  ordinal_position;

I want to populate the rest with a function

function :-

CREATE OR REPLACE PROCEDURE test.insert_data_population()
as $$
declare s record;
declare t record;
declare c record;

BEGIN

    FOR s IN SELECT distinct table_schema FROM test.table_study
    LOOP
        FOR t IN SELECT distinct table_name FROM test.table_study where table_schema = s.table_schema
        loop
            FOR c IN SELECT column_name FROM test.table_study where table_name = t.table_name
            LOOP        
                                
    execute 'update test.table_study set records = (select count(*) from ' || s.table_schema || '.' || t.table_name || ') where table_study.table_name = '|| t.table_name ||';';
                            
                        END LOOP;
                        END LOOP;
                    END LOOP;
END;
$$
LANGUAGE plpgsql;

I get this error SQL Error [42703]: ERROR: column "age" does not exist. the table age does exist. when I take out the where clause

    execute 'update referralunion.testinsert ti set records = (select count(*) from ' || s.table_schema || '.' || t.table_name || ') ;';

it works, I just cant figure out whats wrong?

Answer 1

Your procedure is structured entirely wrong. What it results in is an attempt to get every column name for every table name in every schema. I would guess results in your column does not exist error. Further is shows procedural thinking. SQL requires think in terms of sets. Below I use basically your query to demonstrate then a revised version which uses a single loop.

-- setup (dropping schema references)
create table table_study ( 
    table_schema text,
    table_name  text,
    column_name text,
    records     int,
    no_nulls    int,
    no_blanks   int,
    per_pop     int
);
    
insert into table_study(table_schema, table_name, column_name) 
     values ('s1','t1','age')
          , ('s2','t1','xyz'); 

-- procedure replacing EXECUTE with Raise Notice.     
create or replace procedure insert_data_population()
as $$
declare 
    s record;
    t record;
    c record;

    line int = 0;
begin

    for s in select distinct table_schema from table_study
    loop
        for t in select distinct table_name from table_study where table_schema = s.table_schema
        loop
            for c in select column_name from table_study where table_name = t.table_name
            loop        
               line = line+1;                 
               raise notice  '%: update  table_study set records = (select count(*) from  %.% where table_study.table_name =  %;'
                           , line, s.table_schema, t.table_name, c.column_name; 
               
            end loop;
        end loop;
    end loop;       
end;                 
$$ 
language plpgsql;

Run procedure

do $$ 
begin 
    call insert_data_population();
end;
$$;

RESULTS

1: update table_study set records = (select count(*) from s2.t1 where table_study.table_name = age;
2: update table_study set records = (select count(*) from s2.t1 where table_study.table_name = xyz;
3: update table_study set records = (select count(*) from s1.t1 where table_study.table_name = age;
4: update table_study set records = (select count(*) from s1.t1 where table_study.table_name = xyz;

Notice lines 2 and 3. Each references a column name that does not exist in the table. This results from the FOR structure with the same table name in different schema.

Revision for Single Select statement with Single For loop.

create or replace 
procedure insert_data_population()
 language plpgsql 
as $$
declare 
    s record;
    line int = 0;
begin

    for s in select distinct table_schema, table_name, column_name from table_study
    loop
        line = line+1;                 
        raise notice  '%: update  table_study set records = (select count(*) from  %.% where table_study.table_name =  %;'
                   ,  line, s.table_schema, s.table_name, s.column_name; 
    end loop;       
end;                 
$$;

do $$ 
begin 
    call insert_data_population();
end;
$$;

RESULTS
1: update table_study set records = (select count(*) from s2.t1 where table_study.table_name = xyz;
2: update table_study set records = (select count(*) from s1.t1 where table_study.table_name = age;

Note: In Postgres DECLARE begins a block. It is not necessary to declared each variable. I would actually consider it bad practice. In theory it could require an end for each declare as each could be considered a nested block. Fortunately Postgres does not require this.