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
