pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Count of rows
2+ messages / 2 participants
[nested] [flat]

* Count of rows
@ 2007-08-03 05:35 Paul Lambert <paul.lambert@autoledgers.com.au>
  0 siblings, 0 replies; 2+ messages in thread

From: Paul Lambert @ 2007-08-03 05:35 UTC (permalink / raw)
  To: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Count of rows
@ 2007-08-03 07:20 Christian Kindler <christian.kindler@gmx.net>
  0 siblings, 0 replies; 2+ messages in thread

From: Christian Kindler @ 2007-08-03 07:20 UTC (permalink / raw)
  To: pgsql-sql


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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2007-08-03 07:20 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-08-03 05:35 Count of rows Paul Lambert <paul.lambert@autoledgers.com.au>
2007-08-03 07:20 Re: Count of rows Christian Kindler <christian.kindler@gmx.net>

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