Thursday, August 21, 2008

Re: [NOVICE] count the number of rows in tables

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);


--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

No comments: