Обсуждение: plpgsql: return results of a dynamic query
Hello,
I'am a complete newbie to plpgsql, so if this information is explained
somewhere where I haven't looked, yet, please point me to the doc... (I
didn't find the answer in the PostgreSQL Programmer's Guide.)
I use PG 7.3.1.
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 ?
Thanks,
Moritz
>
> 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
>>
>> 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
>>
>> 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