From: Moritz Lennert <mlennert@club.worldonline.be>
To: pgsql-sql@postgresql.org
Subject: plpgsql: return results of a dynamic query
Date: Wed, 29 Jan 2003 10:21:20 +0100 (CET)
Message-ID: <51767.164.15.128.4.1043832080.squirrel@http://moritz.homelinux.org> (raw)
Hello,
I'am a complete newbie to plpgsql, so if this information is explained
somewhere where I haven't looked, yet, please point me to the doc... (I
didn't find the answer in the PostgreSQL Programmer's Guide.)
I use PG 7.3.1.
I'm trying to retrieve a row count from several tables (40) and would like
to create a function that does this automatically for the 40 and displays
the results. So, I loop through the tables:
DECLARE
obj RECORD;
BEGIN
FOR obj IN SELECT relname AS name FROM pg_class
WHERE relkind IN ('r')
AND relname like '%_random' AND relname != 'tout_random'
LOOP
then I need to do the select count for each table in the lines of
SELECT count(t1.id) AS total FROM || obj.name || AS t1 JOIN tout_random
AS t2 ON t1.id=t2.id WHERE t2.ok=TRUE;
and return the result of each of these select counts.
Could someone indicate how to return the results of these queries ?
Am I right that in order to do this dynamic query, I have to use an
EXECUTE statement ? Can I return the results of an EXECUTE statement ?
Thanks,
Moritz
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mlennert@club.worldonline.be
Subject: Re: plpgsql: return results of a dynamic query
In-Reply-To: <51767.164.15.128.4.1043832080.squirrel@http://moritz.homelinux.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox