[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: [GENERAL] counting records of schema
From:       Ian Barwick <barwick () gmail ! com>
Date:       2004-10-30 22:24:43
Message-ID: 1d581afe04103015244a20e0f2 () mail ! gmail ! com
[Download RAW message or body]

On Tue, 26 Oct 2004 08:03:26 +0200, tom.zschockelt@flender.com
<tom.zschockelt@flender.com> wrote:
> I need to know how many records are in a specific schema of a database. 
>  
> I've tried with a function but there are still problems 

Providing details of the problems usually helps ;-).
  
> Can you give me some hints : 
>  
> -- Function: count_records(myschema varchar) 
>  
> -- DROP FUNCTION count_records("varchar"); 
>  
> CREATE OR REPLACE FUNCTION count_records("varchar") 
>   RETURNS int8 AS 
> $BODY$DECLARE 
>  anzahl bigint := 0; 
>  summe bigint := 0; 
>  ds RECORD; 
>  tabellenname varchar(100); 
> BEGIN 
>  FOR ds IN select * from pg_tables where schemaname = myschema LOOP 

'myschema' is not defined anywhere - I presume it's the alias for the
function's VARCHAR argument?

>   tabellenname := quote_ident(ds.schemaname) || '.' ||
> quote_ident(ds.tablename); 
>   EXECUTE 'SELECT count(*) FROM ' || tabellenname INTO quote_ident(anzahl); 

You can't (directly) extract the result of a dynamically-created
SELECT using EXECUTE, see
http://www.postgresql.org/docs/7.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
for further information and hints.

>   summe := summe + anzahl; 
>  END LOOP; 
>  return summe; 
> END;$BODY$ 
>   LANGUAGE 'plpgsql' VOLATILE; 

BTW you don't need to specify VOLATILE here - it's the default - and
STABLE might be the appropriate choice anyway.

HTH

Ian Barwick
barwick@gmail.com

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faqs/FAQ.html
[prev in list] [next in list] [prev in thread] [next in thread]