Thread: Seeking PL/PGSQL example

Guys,

I'm getting started with PL/PGSQL but want to understand if the following
is even possible:

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?

Sorry if this is too simplistic.  I've googled but must not be phrasing my
question properly.  Currently reading through the manual, but if someone
has any helpful tips I'd appreciate it.

Thanks!
John

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

John Wells wrote:

>Guys,
>
>I'm getting started with PL/PGSQL but want to understand if the following
>is even possible:
>
>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?
>
>
I would assume you would do something like (this may need to be
incomplete but should give you a place to start).

I would use a FOR loop to get the names of the tables (with a select
statement like SELECT table_name FROM information_schema.tables where
table_schema = 'public')

Then I would have to have a for in execute loop which would return next
for each of SELECT table_name as table_name, count(*) from table_name

And run this nested loop this way.

Something like:
CREATE FUNCTION rowcounts() RETURNS SETOF record AS '
DECLARE
  tablename varchar();
BEGIN
  FOR tname IN SELECT table_name FROM information_schema.tables
  LOOP
    FOR IN EXECUTE ''SELECT '' || tname || ''AS table_name, count(*)
from '' ||tname;
       LOOP
         RETURN NEXT;
     END LOOP;
   END LOOP;
END;
' LANGUAGE PLPGSQL;

Not saying this will work but it might be a good start.

Best Wishes,
Chris Travers
Metatron Technology Consulting

On Aug 12, 2005, at 5:26 PM, John Wells wrote:

> I'm getting started with PL/PGSQL but want to understand if the
> following
> is even possible:
>
> 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?
>
> Sorry if this is too simplistic.  I've googled but must not be
> phrasing my
> question properly.  Currently reading through the manual, but if
> someone
> has any helpful tips I'd appreciate it.


There is some code here that shows how to loop through all tables to
grant or revoke access privileges. You can easily adapt it to print
table name and row count.

http://pgedit.com/node/20


John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL