Skip site navigation (1) Skip section navigation (2)

Re: count the number of rows in tables

From: Frank Bax <fbax(at)sympatico(dot)ca>
To:
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: count the number of rows in tables
Date: 2008-08-21 14:44:44
Message-ID: 48AD7F5C.8090209@sympatico.ca (view raw or flat)
Thread:
Lists: pgsql-novice
A B wrote:
> I'd like to fill this table
> 
> foo (name varchar(100), rows integer);
> 
> with the number of rows for each table that my account has access to.
> But EXECUTE with SELECT ... INTO is not yet implemented.
> So how do I re-write this function?
> 
> create or replace function countrows() RETURNS void AS $$
> DECLARE
> 	rec RECORD;
> 	nr INTEGER;
> BEGIN
> 	FOR rec IN SELECT tablename FROM pg_tables WHERE tableowner='myaccount'
> 	LOOP
> 		EXECUTE 'SELECT count(*) INTO nr FROM '||rec.tablename;
> 		EXECUTE 'INSERT INTO foo (name,rows) VALUES ('||rec.tablename||','||nr||')';
> 	END LOOP;
> END;	
> $$ LANGUAGE plpgsql;
> 



insert into foo (name,rows) (select 'employee',count(*) from employee);


In response to

pgsql-novice by date

Next:From: Decibel!Date: 2008-08-22 05:38:34
Subject: Re: Triggers and Function.
Previous:From: A BDate: 2008-08-21 13:42:59
Subject: count the number of rows in tables

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group