From: Christian Kindler <christian.kindler@gmx.net>
To: pgsql-sql@postgresql.org
Subject: Re: Count of rows
Date: Fri, 03 Aug 2007 09:20:45 +0200
Message-ID: <20070803072045.152170@gmx.net> (raw)
do this via execute in a stored procedure - something like this (written on the flow - untested!)
returns setof text
declare
my_record record;
my counter as bigint;
begin
for my_record in
select tablename from pg_tables where
schemaname = 'public'
loop
execute into counter
'select count(*) from ' || my_record.tablename ;
return next my_record.tablename || ': ' || counter::text;
end loop;
return null;
end;
On Fri, August 3, 2007 7:35 am, Paul Lambert wrote:
> What's the best way to count how many rows are in each table via SQL? Or
> is it even possible?
>
> I'm trying something like:
>
> select tablename, count(*) from (select tablename from pg_tables where
> schemaname = 'public') as test group by tablename;
>
> But obviously this just gives a count of 1 for each table in the public
> schema.
>
> Can it be done or would I have to write a function?
>
> --
> Paul Lambert
> Database Administrator
> AutoLedgers
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
--
Psssst! Schon vom neuen GMX MultiMessenger gehört?
Der kanns mit allen: http://www.gmx.net/de/go/multimessenger
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: christian.kindler@gmx.net
Subject: Re: Count of rows
In-Reply-To: <20070803072045.152170@gmx.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox