counting records of schema

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