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

Re: count the number of rows in tables

From: Frank Bax <fbax(at)sympatico(dot)ca>
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: (view raw, whole thread or download thread mbox)
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 $$
> 	rec RECORD;
> 	nr INTEGER;
> 	FOR rec IN SELECT tablename FROM pg_tables WHERE tableowner='myaccount'
> 		EXECUTE 'SELECT count(*) INTO nr FROM '||rec.tablename;
> 		EXECUTE 'INSERT INTO foo (name,rows) VALUES ('||rec.tablename||','||nr||')';
> 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-2017 The PostgreSQL Global Development Group