CODEWITHSUNDEEP
postgresql
Ali Shakiba
Ali Shakiba

Reputation: 1374

Looping through all tables and columns in postgresql and applying a select query to each one

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.

Upvotes: 0

Views: 153

Answers (1)

Ramin Faracov
Ramin Faracov

Reputation: 3313

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

Upvotes: 1

Related Questions