Alex Rivera | Logout

Loop on tables with PL/pgSQL in Postgres 9.0+

Asked 2013-03-07T08:26:37.277
40

I want to loop through all my tables to count rows in each of them. The following query gets me an error:

DO $$
DECLARE
    tables CURSOR FOR
        SELECT tablename FROM pg_tables
        WHERE tablename NOT LIKE 'pg_%'
        ORDER BY tablename;
    tablename varchar(100);
    nbRow int;
BEGIN
    FOR tablename IN tables LOOP
        EXECUTE 'SELECT count(*) FROM ' || tablename INTO nbRow;
        -- Do something with nbRow
    END LOOP;
END$$;

Errors:

ERROR:  syntax error at or near ")"
LINE 1: SELECT count(*) FROM (sql_features)
                                          ^
QUERY:  SELECT count(*) FROM (sql_features)
CONTEXT:  PL/pgSQL function inline_code_block line 8 at EXECUTE statement

sql_features is a table's name in my DB. I already tried to use quote_ident() but to no avail.

postgresql variables loops plpgsql tablename
Edit
Report

1 Answer

34

The cursor returns a record, not a scalar value, so "tablename" is not a string variable.

The concatenation turns the record into a string that looks like this (sql_features). If you had selected e.g. the schemaname with the tablename, the text representation of the record would have been (public,sql_features).

So you need to access the column inside the record to create your SQL statement:

DO $$
DECLARE
    tables CURSOR FOR
        SELECT tablename
        FROM pg_tables
        WHERE tablename NOT LIKE 'pg_%'
        ORDER BY tablename;
    nbRow int;
BEGIN
    FOR table_record IN tables LOOP
        EXECUTE 'SELECT count(*) FROM ' || table_record.tablename INTO nbRow;
        -- Do something with nbRow
    END LOOP;
END$$;

You might want to use WHERE schemaname = 'public' instead of not like 'pg_%' to exclude the Postgres system tables.

answered 2013-03-07T08:46:10.410

Your Answer