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