Re: [GENERAL] Seeking PL/PGSQL example

[John DeSoi]

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


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [GENERAL] Seeking PL/PGSQL example

[Chris Travers]

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

Re: [GENERAL] Seeking PL/PGSQL example

[Michael Fuhr]

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

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

[GENERAL] Seeking PL/PGSQL example

[John Wells]

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


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match