pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Henshall, Stuart - WCP <SHenshall@westcountrypublications.co.uk>
To: pgsql-sql@postgresql.org, "'bhuvansql@yahoo.com'" <bhuvansql@yahoo.com>
Subject: Re: how can we get total records in pg server?
Date: Wed, 25 Jul 2001 14:13:32 +0100
Message-ID: <E2870D8CE1CCD311BAF50008C71EDE8E01F74641@MAIL_EXCHANGE> (raw)

Hello,
	Here's a quick function to count the total number of rows in none
system tables:
DROP FUNCTION cnt_rows();
CREATE FUNCTION cnt_rows() RETURNS int4 AS '
DECLARE
	r RECORD;
	rb RECORD;
	tot int4;
	sqlstr text;
BEGIN
	tot:=0;
	FOR r IN select * from pg_tables where not tablename like ''pg_%''
LOOP
		sqlstr:=''SELECT count(*) AS cnt FROM '' ||
quote_ident(r.tablename) || '';'';
		FOR rb IN EXECUTE sqlstr LOOP
			RAISE NOTICE ''Number of rows in
%=%'',r.tablename,rb.cnt;
			tot:=tot+rb.cnt;
		END LOOP;
	END LOOP;
	RAISE NOTICE ''Total Number of rows for all none system
tables=%'',tot;
	RETURN tot;
END;
' LANGUAGE 'plpgsql';

You'll also need to have the plpgsql language created for your database
(eg: createlang plpgsql testdb)
then just execute the above script then select cnt_rows(); in psql.
-Stuart


ORIGINAL MESSAGE:
hi all,
consider the pg server with databases
bhuvan
uday
guru

the need is to get TOTAL RECORDS of all the THREE DATABASES or atleast
TOTAL RECORDS of ONE DATABASE.

Seems to be simple.
Thankx in advance and infact i was a newbie. 

Regards, 
Bhuvaneswar.
============================================================================
====
      "There is nothing new under the sun, but there are lots of old things 
 			      we don't know yet."
 				-Ambrose Bierce
============================================================================
====

On Mon, 23 Jul 2001, omid omoomi wrote:

> you mean this ?
> select count(*) from tablefoo;
> 
> 
> >From: Bhuvan A <bhuvansql@yahoo.com>
> >To: pgsql-sql@postgresql.org
> >Subject: [SQL] how can we get total records in pg server?
> >Date: Mon, 23 Jul 2001 20:03:42 +0530 (IST)
> >
> >
> >Hi all,
> >
> >how can we get the COUNT of total records in the db server?
> >
> >hope this could be simple for pg experts.
> >thankx in advance!
> >
> >Regards,
> >Bhuvaneswar.
>
>===========================================================================
=====
> >     Eighty percent of married men cheat in America.  The rest cheat in 
> >Europe.
> >  				-- Jackie Mason
>
>===========================================================================
=====
> >
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 3: 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
> 
> 
> _________________________________________________________________
> Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
> 




view thread (4+ messages)

Message-ID: <E2870D8CE1CCD311BAF50008C71EDE8E01F74641@MAIL_EXCHANGE>
Permalink:  ../E2870D8CE1CCD311BAF50008C71EDE8E01F74641@MAIL_EXCHANGE/
Also on:    postgresql.org/message-id/E2870D8CE1CCD311BAF50008C71EDE8E01F74641@MAIL_EXCHANGE

 · 

reply

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: SHenshall@westcountrypublications.co.uk, bhuvansql@yahoo.com
  Subject: Re: how can we get total records in pg server?
  In-Reply-To: <E2870D8CE1CCD311BAF50008C71EDE8E01F74641@MAIL_EXCHANGE>

* 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