Re: plpgsql: return results of a dynamic query

>
> 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')
ANDrelname 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