0

I came up with this idea to loop over all tables and column names to count the number of null values in postgres. However, I failed to do that by Error 42702: column reference "table_name" is ambiguous.

create or replace function count_nulls()
    returns table ("table_name" text, "column_name" text, "nulls" int)
    language plpgsql as
$func$
    declare _record information_schema.columns %ROWTYPE;
    begin
        for _record in
            SELECT "table_name", "column_name"
            FROM information_schema.columns
            where "table_schema" = 'public'
        loop
            select quote_literal(_record.table_name) as "table",
                 quote_literal(_record.column_name) as "column", 
                 count(*) as "nulls"
            from quote_literal(_record.table_name)
            where quote_literal(_record.column_name) is null
            group by "table", "column";
        end loop;
        return;
    end;
$func$;

select * from count_nulls();

Any pointers to documentation or keywords for further search is appreciated.

Ali Shakiba
  • 1,255
  • 2
  • 15
  • 29
  • 1
    You need [dynamic SQL](https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN) for this. Alternatively, you can adapt [this answer](https://stackoverflow.com/a/2611745) –  Oct 15 '22 at 08:05

1 Answers1

1

Change this block :

for _record in
    SELECT "table_name", "column_name"
    FROM information_schema.columns
    where "table_schema" = 'public'
loop

to this:

for _record in
    SELECT t1."table_name", t1."column_name"
    FROM information_schema.columns t1
    where t1."table_schema" = 'public'
loop
Ramin Faracov
  • 3,032
  • 1
  • 2
  • 8
  • And you have incorrect syntax on your function. Firstly, your function must be return table but inside the function you have not return query clause. – Ramin Faracov Oct 16 '22 at 15:24