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

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