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