Re: Seeking PL/PGSQL example

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