pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Moritz Lennert <mlennert@club.worldonline.be>
To: Christoph Haller <ch@rodos.fzk.de>
Cc: pgsql-sql@postgresql.org
Subject: Re: plpgsql: return results of a dynamic query
Date: Wed, 29 Jan 2003 14:24:22 +0100 (CET)
Message-ID: <18395.164.15.128.4.1043846662.squirrel@http://moritz.homelinux.org> (raw)
In-Reply-To: <3E37D073.7CA9BD80@rodos.fzk.de>
References: <3E37D073.7CA9BD80@rodos.fzk.de>

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

Thanks, that was it !

The only thing I had to change was the quoting in the EXECUTE statement
(double quoting gave me a parser error). So here's the version that works
for me:

DECLARE
  obj RECORD;
  obj2 RECORD;
BEGIN
  DELETE FROM how_many;
  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
     INSERT INTO how_many VALUES ( obj.name , obj2.total ) ;
   END LOOP;
  END LOOP;
RETURN true;
END;

Again thanks for the clear response.

Cheers,
Moritz



view thread (3+ messages)

Message-ID: <18395.164.15.128.4.1043846662.squirrel@http://moritz.homelinux.org>
Permalink:  ../18395.164.15.128.4.1043846662.squirrel@http:%2F%2Fmoritz.homelinux.org/
Also on:    postgresql.org/message-id/18395.164.15.128.4.1043846662.squirrel@http://moritz.homelinux.org

 · 

reply

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, ch@rodos.fzk.de
  Subject: Re: plpgsql: return results of a dynamic query
  In-Reply-To: <18395.164.15.128.4.1043846662.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