Обсуждение: counting records of schema
hi,
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
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
tabellenname := quote_ident(ds.schemaname) || '.' || quote_ident(ds.tablename);
EXECUTE 'SELECT count(*) FROM ' || tabellenname INTO quote_ident(anzahl);
summe := summe + anzahl;
END LOOP;
return summe;
END;$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION count_records("varchar") OWNER TO postgres;
best regards
tom
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