Message-ID: <3E37D073.7CA9BD80@rodos.fzk.de>
Date: Wed, 29 Jan 2003 14:00:35 +0100
From: Christoph Haller <ch@rodos.fzk.de>
MIME-Version: 1.0
To: mlennert@club.worldonline.be
Cc: pgsql-sql@postgresql.org
Subject: Re: plpgsql: return results of a dynamic query
Content-Type: text/plain; charset=iso-8859-2
Content-Transfer-Encoding: 7bit

>
> 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 ?

>
You are on the right track.
The documentation says:

The results from SELECT queries are discarded by EXECUTE, and SELECT
INTO is not currently supported within EXECUTE.
So, the only way to extract a result from a dynamically-created SELECT
is to use the FOR-IN-EXECUTE form described later.

So something like the following should do the trick:

DECLARE
  obj RECORD;
  obj2 RECORD;
  countresult BIGINT;
BEGIN
  countresult := 0;
  FOR obj IN SELECT relname AS name FROM pg_class
             WHERE relkind IN ('r')
             AND relname like '%_random' AND relname != 'tout_random'
  LOOP
  FOR obj2 IN
  EXECUTE ''SELECT count(t1.id) AS total FROM '' ||
quote_ident(obj.name) ||
  '' AS t1 JOIN tout_random AS t2 ON t1.id=t2.id WHERE t2.ok=TRUE''
   LOOP
     countresult := countresult + obj2.total;
   END LOOP;
  END LOOP;
RETURN countresult;
END;
' LANGUAGE 'plpgsql' ;

I doubt this is exactly what you wanted.
It looks like you were asking for the results of every count.
The only quick solution I can see for this is
populate a table with the name and count of your 40 tables.

Replace the "countresult := countresult + obj2.total;" line by
INSERT INTO countresults VALUES ( obj.name , obj2.total ) ;
and don't forget to reset the table before by
DELETE FROM countresults ;

I hope this helps for now.

Regards, Christoph