Re: Seeking PL/PGSQL example - Mailing list pgsql-general

On Fri, Aug 12, 2005 at 05:26:50PM -0400, John Wells wrote:
>
> With PL/PGSQL, how would I roll through all tables in my database and
> print the table name, along with the row count of that table?

You can get the schemas and tables from the system catalogs or from
the Information Schema (the latter available in 7.4 and later).

http://www.postgresql.org/docs/8.0/static/catalogs.html
http://www.postgresql.org/docs/8.0/static/information-schema.html

To loop through query results, see "Looping Through Query Results"
in the PL/pgSQL documentation.  See also "Executing Dynamic Commands"
and "RETURN NEXT".

http://www.postgresql.org/docs/8.0/static/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING
http://www.postgresql.org/docs/8.0/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
http://www.postgresql.org/docs/8.0/static/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING

For row counts you can use COUNT; if an estimate will suffice, you
could use pg_class.reltuples.

http://www.postgresql.org/docs/8.0/static/functions-aggregate.html
http://www.postgresql.org/docs/8.0/static/catalog-pg-class.html

--
Michael Fuhr