Loop on tables with PL/pgSQL in Postgres 9.0+

by

I can’t remember the last time I actually needed to use an explicit cursor for looping in PL/pgSQL.
Use the implicit cursor of a FOR loop, that’s much cleaner:

DO
$$
DECLARE
   rec   record;
   nbrow bigint;
BEGIN
   FOR rec IN
      SELECT *
      FROM   pg_tables
      WHERE  tablename NOT LIKE 'pg\_%'
      ORDER  BY tablename
   LOOP
      EXECUTE 'SELECT count(*) FROM '
        || quote_ident(rec.schemaname) || '.'
        || quote_ident(rec.tablename)
      INTO nbrow;
      -- Do something with nbrow
   END LOOP;
END
$$;

You need to include the schema name to make this work for all schemas (including those not in your search_path).

Also, you actually need to use quote_ident() or format() with %I or a regclass variable to safeguard against SQL injection. A table name can be almost anything inside double quotes. See:

Minor detail: escape the underscore (_) in the LIKE pattern to make it a literal underscore: tablename NOT LIKE 'pg\_%'

How I might do it:

DO
$$
DECLARE
    tbl   regclass;
    nbrow bigint;
BEGIN
   FOR tbl IN
      SELECT c.oid
      FROM   pg_class     c
      JOIN   pg_namespace n ON n.oid = c.relnamespace
      WHERE  c.relkind = 'r'
      AND    n.nspname NOT LIKE 'pg\_%'         -- system schema(s)
      AND    n.nspname <> 'information_schema'  -- information schema
      ORDER  BY n.nspname, c.relname
   LOOP
      EXECUTE 'SELECT count(*) FROM ' || tbl INTO nbrow;
      -- raise notice '%: % rows', tbl, nbrow;
   END LOOP;
END
$$;

Query pg_catalog.pg_class instead of tablename, it provides the OID of the table.

The object identifier type regclass is handy to simplify. n particular, table names are double-quoted and schema-qualified where necessary automatically (also prevents SQL injection).

This query also excludes temporary tables (temp schema is named pg_temp% internally).

To only include tables from a given schema:

    AND    n.nspname="public" -- schema name here, case-sensitive

More Related Contents:

  1. Can I make a plpgsql function return an integer without using a variable?
  2. Cursor based records in PostgreSQL
  3. Loop through columns of RECORD
  4. What are ‘$$’ used for in PL/pgSQL
  5. INSERT with dynamic table name in trigger function
  6. The forgotten assignment operator “=” and the commonplace “:=”
  7. Truncating all tables in a Postgres database
  8. Define table and column names as arguments in a plpgsql function?
  9. PostgreSQL: ERROR: 42601: a column definition list is required for functions returning “record”
  10. How do you use variables in a simple PostgreSQL script?
  11. How to set value of composite variable field using dynamic SQL
  12. How do I do large non-blocking updates in PostgreSQL?
  13. Is there a way to disable function overloading in Postgres
  14. PostgreSQL function returning multiple result sets
  15. Dynamic SQL (EXECUTE) as condition for IF statement
  16. User defined variables in PostgreSQL
  17. Returning from a function with OUT parameter
  18. SELECT raises exception in PL/pgSQL function
  19. Test for null in function with varying parameters
  20. Return SETOF rows from PostgreSQL function
  21. Error when setting n_distinct using a plpgsql variable
  22. Dynamically generate columns for crosstab in PostgreSQL
  23. Function to loop through and select data from multiple tables
  24. PL/pgSQL functions: How to return a normal table with multiple columns using an execute statement
  25. SQL Sub queries in check constraint
  26. ERROR: unterminated quoted string at or near
  27. How can I send some http request from postgresql function or trigger
  28. Select from a table variable
  29. Hashing a String to a Numeric Value in PostgreSQL
  30. Format specifier for integer variables in format() for EXECUTE?

Leave a Comment