How to output data about tables in a loop in a postgresql database?

Question

I need to output data about all tables in the format

Table name | Number of columns | Number of fields

using an anonymous function

I do this:

do
$$
declare
table_count integer := 0;
columns_count integer := 0;
rows_count integer := 0;
table_record record;
begin
for table_record in SELECT table_name
FROM information_schema.tables
WHERE table_schema='public'
  AND table_type='BASE TABLE'
loop
    SELECT COUNT(COLUMN_NAME) INTO columns_count
    FROM table_record
    WHERE TABLE_CATALOG = 'ucheb' AND TABLE_SCHEMA = 'public';
    
    select count(*) into rows_count from table_record;
    

    raise notice '% | % | %', table_record, columns_count, rows_count;
    
end loop;
end;
$$ LANGUAGE plpgsql;

It turns out this error:

ERROR:  relation "table_record" does not exist
LINE 1: ...ELECT COUNT(COLUMN_NAME)                     FROM table_reco...
                                                             ^
QUERY:  SELECT COUNT(COLUMN_NAME)                     FROM table_record
    WHERE TABLE_CATALOG = 'ucheb' AND TABLE_SCHEMA = 'public'
CONTEXT:  PL/pgSQL function inline_code_block line 13 at SQL statement
SQL state: 42P01

What am I doing wrong??

Writing ``` table_record.table_name ``` doesn't help – Efobo Feb 19 '23 at 18:42 — Efobo, Feb 19 '23 at 18:42

score 0 · Answer 1 · answered Feb 21 '23 at 01:54

There are several issues with your code block.

A variable cannot be used in a SQL statement where a database object is required. In this case each of your from table_record actually references the table or view named table_record. To reference the value of a variable in a statement you need dynamic SQL.
You cannot count the number of columns in a table via select. The count(v) function returns the number or rows with non-null column values. In this case the number of non-nulls values in the column COLUMN_NAME.
You are accessing table_record incorrectly. A variable the of type record you essentially define a tuple (row) which the exact structure is determined in the populating query. In this case you need to reference table_record.table_name.

The question then becomes what you want to do with the results. Specifically, do you need exact row count or will an estimate do? If you need exact row counts then you need to rewrite you block to correct the above. Further the resulting block will need a full table scan (most likely as you have no where) on every table in the schema. This will be slow and keep in mind that the row counts may change before the block finishes. On the other hand if estimated row count will do you can get what you are after with a single query (and not not need plpgsql at all).

with table_columns(table_catalog, table_schema,table_name,num_columns) as 
     ( select table_catalog, table_schema,table_name, max(ordinal_position) 
         from information_schema.columns 
        where (table_catalog, table_schema) = 
              ('ucheb','public') 
        group by table_catalog, table_schema, table_name
     ) 
select t.table_name, c.num_columns, s.n_live_tup "num of rows (est)" 
  from information_schema.tables t
  join pg_catalog.pg_stat_all_tables s 
    on (s.schemaname, relname) = (t.table_schema , t.table_name)
  join table_columns c
    on (c.table_catalog, c.table_schema, c.table_name) = 
       (t.table_catalog, t.table_schema, t.table_name) 
 where (t.table_catalog, t.table_schema, t.table_type) =
       ('ucheb', 'public','BASE TABLE')
order by 1;

NOTE: It gets number of columns in the table as max(ordinal_position) from information_schema.columns view (this is needed with the query or dynamic sql function. As far as row count it is retrieved from pg_catalog.pg_stat_all_tables. If the tables are well vacuumed this should be close to the true number of rows.

How to output data about tables in a loop in a postgresql database?

1 Answers1