Re: counting records of schema

#1

On Tue, 26 Oct 2004 08:03:26 +0200, tom.zschockelt (AT) flender (DOT) com
<tom.zschockelt (AT) flender (DOT) com> wrote:

Quote:

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 ;-).

Quote:

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?

Quote:

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/s...-EXECUTING-DYN
for further information and hints.

Quote:

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 (AT) gmail (DOT) com

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

http://www.postgresql.org/docs/faqs/FAQ.html

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: counting records of schema

comp.databases.postgresql.general comp.databases.postgresql.general

Re: counting records of schema - 10-30-2004 , 05:24 PM