pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedplpgsql: return results of a dynamic query
3+ messages / 2 participants
[nested] [flat]
* plpgsql: return results of a dynamic query
@ 2003-01-29 09:21 Moritz Lennert <mlennert@club.worldonline.be>
0 siblings, 0 replies; 3+ messages in thread
From: Moritz Lennert @ 2003-01-29 09:21 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: plpgsql: return results of a dynamic query
@ 2003-01-29 13:00 Christoph Haller <ch@rodos.fzk.de>
2003-01-29 13:24 ` Re: plpgsql: return results of a dynamic query Moritz Lennert <mlennert@club.worldonline.be>
0 siblings, 1 reply; 3+ messages in thread
From: Christoph Haller @ 2003-01-29 13:00 UTC (permalink / raw)
To: mlennert@club.worldonline.be; +Cc: pgsql-sql
>
> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: plpgsql: return results of a dynamic query
2003-01-29 13:00 Re: plpgsql: return results of a dynamic query Christoph Haller <ch@rodos.fzk.de>
@ 2003-01-29 13:24 ` Moritz Lennert <mlennert@club.worldonline.be>
0 siblings, 0 replies; 3+ messages in thread
From: Moritz Lennert @ 2003-01-29 13:24 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; +Cc: pgsql-sql
>>
>> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-01-29 13:24 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-01-29 09:21 plpgsql: return results of a dynamic query Moritz Lennert <mlennert@club.worldonline.be>
2003-01-29 13:00 Re: plpgsql: return results of a dynamic query Christoph Haller <ch@rodos.fzk.de>
2003-01-29 13:24 ` Moritz Lennert <mlennert@club.worldonline.be>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox