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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
