How do we combine and return results from multiple queries in a loop?
Hi there,
We'd like to use a plpgsql function to use results from query A to execute
several queries B, C, etc., and return the results of all B, C, etc queries
as one result set. Would placing 'RETURN QUERY' inside a loop automatically
concatenate all 'return query' results in the function's return? If not, how
would we go about getting this result?
Here's an example. The function finds all zip_code records with the given
code, and then searches all locations for a state of that original zip_code.
(I know the current query could be done via a join, but my ACTUAL query
would require this functionality.)
begin;
create OR REPLACE function t() returns setof locations as
$$
declare z zip_codes%rowtype;
begin
for z in select * from zip_codes where code like '%32301%'
LOOP
return query select * from locations where locations.state like
z.state; #query B
# All I want to do is return the results from all of the above
queries as one
# result set.
END LOOP;
return;
end
$$
language 'plpgsql';
commit;
Any idea how I do that?
On 05/15/11 8:53 PM, Bernardo Telles wrote:
Hi there,
We'd like to use a plpgsql function to use results from query A to
execute several queries B, C, etc., and return the results of all B,
C, etc queries as one result set. Would placing 'RETURN QUERY' inside
a loop automatically concatenate all 'return query' results in the
function's return? If not, how would we go about getting this result?
all the queries would have to have the same fields to do this. if they
do, then you can write it as a join or union.
in your example case, its easy.
select * from locations l join zipcode z on l.state = z.state where
z.zipcode like '32301%';
this also would be more efficient than the way you proposed
now, if you're thinking of a DIFFERENT problem thats more complex to
solve, well, without knowing the actual problem there's not much I can
suggest.
Hi John,
Thanks for the quick response. I'll elaborate on the *actual* problem.
Basically, I want to call:
select * from partiesWithin("DAYTONA", "FL", 5);
The partiesWithin() function finds all zip codes (and zip_code centroids),
then searches a 5 (or n) mile radius around those centroids for parties.
Since each zip code has a 'point' column which is a PostGIS feature, I need
to iterate through each of those points, and search for parties within 5
miles of each of the centroids, returning a concatenated query of all
parties that were found in any of the queries. Someone mentioned that one
way to do that is to use a temporary table inside the partiesWithin
function. Any thoughts?
On Mon, May 16, 2011 at 1:28 AM, John R Pierce <pierce@hogranch.com> wrote:
Show quoted text
On 05/15/11 8:53 PM, Bernardo Telles wrote:
Hi there,
We'd like to use a plpgsql function to use results from query A to execute
several queries B, C, etc., and return the results of all B, C, etc queries
as one result set. Would placing 'RETURN QUERY' inside a loop automatically
concatenate all 'return query' results in the function's return? If not, how
would we go about getting this result?all the queries would have to have the same fields to do this. if they do,
then you can write it as a join or union.in your example case, its easy.
select * from locations l join zipcode z on l.state = z.state where
z.zipcode like '32301%';this also would be more efficient than the way you proposed
now, if you're thinking of a DIFFERENT problem thats more complex to solve,
well, without knowing the actual problem there's not much I can suggest.--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Please read section “39.6.1. Returning From a Function” in the pl/pgsql section of the documentation (actually, you should read the entire section on pl/pgsql programming).
“RETURN QUERY appends the results of executing a query to the function's result set.” [when used with RETURNING SETOF *]
Concatenate and “append” are synonyms in this context; otherwise the above quote from section 39.6.1 is basically a word-for-word answer to your question.
David J.
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Bernardo Telles
Sent: Monday, May 16, 2011 1:13 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] How do we combine and return results from multiple queries in a loop?
Hi John,
Thanks for the quick response. I'll elaborate on the actual problem. Basically, I want to call:
select * from partiesWithin("DAYTONA", "FL", 5);
The partiesWithin() function finds all zip codes (and zip_code centroids), then searches a 5 (or n) mile radius around those centroids for parties. Since each zip code has a 'point' column which is a PostGIS feature, I need to iterate through each of those points, and search for parties within 5 miles of each of the centroids, returning a concatenated query of all parties that were found in any of the queries. Someone mentioned that one way to do that is to use a temporary table inside the partiesWithin function. Any thoughts?
On Mon, May 16, 2011 at 1:28 AM, John R Pierce <pierce@hogranch.com> wrote:
On 05/15/11 8:53 PM, Bernardo Telles wrote:
Hi there,
We'd like to use a plpgsql function to use results from query A to execute several queries B, C, etc., and return the results of all B, C, etc queries as one result set. Would placing 'RETURN QUERY' inside a loop automatically concatenate all 'return query' results in the function's return? If not, how would we go about getting this result?
all the queries would have to have the same fields to do this. if they do, then you can write it as a join or union.
in your example case, its easy.
select * from locations l join zipcode z on l.state = z.state where z.zipcode like '32301%';
this also would be more efficient than the way you proposed
now, if you're thinking of a DIFFERENT problem thats more complex to solve, well, without knowing the actual problem there's not much I can suggest.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Wow, you guys are some fast-acting dudes (and yes, I *am* an adult, but a
kid at heart).
David, yup, that's exactly the part of the documentation that I read, and
that is confusing me, because when I try it at home, it's not working. In
fact, the exact example that I'm showing in the first email uses that
assumption, but it seems to not be working :-/
But I'll take another look at the query tonight and see if I'm missing
something.
On Mon, May 16, 2011 at 1:55 PM, David Johnston <polobo@yahoo.com> wrote:
Show quoted text
Please read section “39.6.1. Returning From a Function” in the pl/pgsql
section of the documentation (actually, you should read the entire section
on pl/pgsql programming).“RETURN QUERY appends the results of executing a query to the function's
result set.” [when used with RETURNING SETOF *]Concatenate and “append” are synonyms in this context; otherwise the above
quote from section 39.6.1 is basically a word-for-word answer to your
question.David J.
*From:* pgsql-general-owner@postgresql.org [mailto:
pgsql-general-owner@postgresql.org] *On Behalf Of *Bernardo Telles
*Sent:* Monday, May 16, 2011 1:13 PM
*To:* pgsql-general@postgresql.org
*Subject:* Re: [GENERAL] How do we combine and return results from
multiple queries in a loop?Hi John,
Thanks for the quick response. I'll elaborate on the *actual* problem.
Basically, I want to call:select * from partiesWithin("DAYTONA", "FL", 5);
The partiesWithin() function finds all zip codes (and zip_code centroids),
then searches a 5 (or n) mile radius around those centroids for parties.
Since each zip code has a 'point' column which is a PostGIS feature, I need
to iterate through each of those points, and search for parties within 5
miles of each of the centroids, returning a concatenated query of all
parties that were found in any of the queries. Someone mentioned that one
way to do that is to use a temporary table inside the partiesWithin
function. Any thoughts?On Mon, May 16, 2011 at 1:28 AM, John R Pierce <pierce@hogranch.com>
wrote:On 05/15/11 8:53 PM, Bernardo Telles wrote:
Hi there,
We'd like to use a plpgsql function to use results from query A to execute
several queries B, C, etc., and return the results of all B, C, etc queries
as one result set. Would placing 'RETURN QUERY' inside a loop automatically
concatenate all 'return query' results in the function's return? If not, how
would we go about getting this result?all the queries would have to have the same fields to do this. if they do,
then you can write it as a join or union.in your example case, its easy.
select * from locations l join zipcode z on l.state = z.state where
z.zipcode like '32301%';this also would be more efficient than the way you proposed
now, if you're thinking of a DIFFERENT problem thats more complex to solve,
well, without knowing the actual problem there's not much I can suggest.--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Hello
2011/5/16 Bernardo Telles <btelles@gmail.com>:
Wow, you guys are some fast-acting dudes (and yes, I am an adult, but a kid
at heart).David, yup, that's exactly the part of the documentation that I read, and
that is confusing me, because when I try it at home, it's not working. In
fact, the exact example that I'm showing in the first email uses that
assumption, but it seems to not be working :-/
What PostgreSQL version do you have?
It's not supported on older versions
regards
Pavel Stehule
Show quoted text
But I'll take another look at the query tonight and see if I'm missing
something.On Mon, May 16, 2011 at 1:55 PM, David Johnston <polobo@yahoo.com> wrote:
Please read section “39.6.1. Returning From a Function” in the pl/pgsql
section of the documentation (actually, you should read the entire section
on pl/pgsql programming).“RETURN QUERY appends the results of executing a query to the function's
result set.” [when used with RETURNING SETOF *]Concatenate and “append” are synonyms in this context; otherwise the above
quote from section 39.6.1 is basically a word-for-word answer to your
question.David J.
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Bernardo Telles
Sent: Monday, May 16, 2011 1:13 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] How do we combine and return results from multiple
queries in a loop?Hi John,
Thanks for the quick response. I'll elaborate on the actual problem.
Basically, I want to call:select * from partiesWithin("DAYTONA", "FL", 5);
The partiesWithin() function finds all zip codes (and zip_code centroids),
then searches a 5 (or n) mile radius around those centroids for parties.
Since each zip code has a 'point' column which is a PostGIS feature, I need
to iterate through each of those points, and search for parties within 5
miles of each of the centroids, returning a concatenated query of all
parties that were found in any of the queries. Someone mentioned that one
way to do that is to use a temporary table inside the partiesWithin
function. Any thoughts?On Mon, May 16, 2011 at 1:28 AM, John R Pierce <pierce@hogranch.com>
wrote:On 05/15/11 8:53 PM, Bernardo Telles wrote:
Hi there,
We'd like to use a plpgsql function to use results from query A to execute
several queries B, C, etc., and return the results of all B, C, etc queries
as one result set. Would placing 'RETURN QUERY' inside a loop automatically
concatenate all 'return query' results in the function's return? If not, how
would we go about getting this result?all the queries would have to have the same fields to do this. if they
do, then you can write it as a join or union.in your example case, its easy.
select * from locations l join zipcode z on l.state = z.state where
z.zipcode like '32301%';this also would be more efficient than the way you proposed
now, if you're thinking of a DIFFERENT problem thats more complex to
solve, well, without knowing the actual problem there's not much I can
suggest.--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Hi Pavel, I'm running 8.4
On Mon, May 16, 2011 at 3:58 PM, Pavel Stehule <pavel.stehule@gmail.com>wrote:
Show quoted text
Hello
2011/5/16 Bernardo Telles <btelles@gmail.com>:
Wow, you guys are some fast-acting dudes (and yes, I am an adult, but a
kid
at heart).
David, yup, that's exactly the part of the documentation that I read, and
that is confusing me, because when I try it at home, it's not working. In
fact, the exact example that I'm showing in the first email uses that
assumption, but it seems to not be working :-/What PostgreSQL version do you have?
It's not supported on older versions
regards
Pavel Stehule
But I'll take another look at the query tonight and see if I'm missing
something.On Mon, May 16, 2011 at 1:55 PM, David Johnston <polobo@yahoo.com>
wrote:
Please read section “39.6.1. Returning From a Function” in the pl/pgsql
section of the documentation (actually, you should read the entiresection
on pl/pgsql programming).
“RETURN QUERY appends the results of executing a query to the function's
result set.” [when used with RETURNING SETOF *]Concatenate and “append” are synonyms in this context; otherwise the
above
quote from section 39.6.1 is basically a word-for-word answer to your
question.David J.
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of BernardoTelles
Sent: Monday, May 16, 2011 1:13 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] How do we combine and return results frommultiple
queries in a loop?
Hi John,
Thanks for the quick response. I'll elaborate on the actual problem.
Basically, I want to call:select * from partiesWithin("DAYTONA", "FL", 5);
The partiesWithin() function finds all zip codes (and zip_code
centroids),
then searches a 5 (or n) mile radius around those centroids for parties.
Since each zip code has a 'point' column which is a PostGIS feature, Ineed
to iterate through each of those points, and search for parties within 5
miles of each of the centroids, returning a concatenated query of all
parties that were found in any of the queries. Someone mentioned thatone
way to do that is to use a temporary table inside the partiesWithin
function. Any thoughts?On Mon, May 16, 2011 at 1:28 AM, John R Pierce <pierce@hogranch.com>
wrote:On 05/15/11 8:53 PM, Bernardo Telles wrote:
Hi there,
We'd like to use a plpgsql function to use results from query A toexecute
several queries B, C, etc., and return the results of all B, C, etc
queries
as one result set. Would placing 'RETURN QUERY' inside a loop
automatically
concatenate all 'return query' results in the function's return? If not,
how
would we go about getting this result?
all the queries would have to have the same fields to do this. if they
do, then you can write it as a join or union.in your example case, its easy.
select * from locations l join zipcode z on l.state = z.state where
z.zipcode like '32301%';this also would be more efficient than the way you proposed
now, if you're thinking of a DIFFERENT problem thats more complex to
solve, well, without knowing the actual problem there's not much I can
suggest.--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Okay, as it turns out. the query was in fact running as expected (i.e.
concatenating results from RETURN QUERY). I just had a horribly wrong
initial query with which to loop...wow I feel stupid for raising all the
fuss.
Thanks again, everyone, for all your help!
On Mon, May 16, 2011 at 10:17 PM, Bernardo Telles <btelles@gmail.com> wrote:
Show quoted text
Hi Pavel, I'm running 8.4
On Mon, May 16, 2011 at 3:58 PM, Pavel Stehule <pavel.stehule@gmail.com>wrote:
Hello
2011/5/16 Bernardo Telles <btelles@gmail.com>:
Wow, you guys are some fast-acting dudes (and yes, I am an adult, but a
kid
at heart).
David, yup, that's exactly the part of the documentation that I read,
and
that is confusing me, because when I try it at home, it's not working.
In
fact, the exact example that I'm showing in the first email uses that
assumption, but it seems to not be working :-/What PostgreSQL version do you have?
It's not supported on older versions
regards
Pavel Stehule
But I'll take another look at the query tonight and see if I'm missing
something.On Mon, May 16, 2011 at 1:55 PM, David Johnston <polobo@yahoo.com>
wrote:
Please read section “39.6.1. Returning From a Function” in the pl/pgsql
section of the documentation (actually, you should read the entiresection
on pl/pgsql programming).
“RETURN QUERY appends the results of executing a query to the
function's
result set.” [when used with RETURNING SETOF *]
Concatenate and “append” are synonyms in this context; otherwise the
above
quote from section 39.6.1 is basically a word-for-word answer to your
question.David J.
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of BernardoTelles
Sent: Monday, May 16, 2011 1:13 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] How do we combine and return results frommultiple
queries in a loop?
Hi John,
Thanks for the quick response. I'll elaborate on the actual problem.
Basically, I want to call:select * from partiesWithin("DAYTONA", "FL", 5);
The partiesWithin() function finds all zip codes (and zip_code
centroids),
then searches a 5 (or n) mile radius around those centroids for
parties.
Since each zip code has a 'point' column which is a PostGIS feature, I
need
to iterate through each of those points, and search for parties within
5
miles of each of the centroids, returning a concatenated query of all
parties that were found in any of the queries. Someone mentioned thatone
way to do that is to use a temporary table inside the partiesWithin
function. Any thoughts?On Mon, May 16, 2011 at 1:28 AM, John R Pierce <pierce@hogranch.com>
wrote:On 05/15/11 8:53 PM, Bernardo Telles wrote:
Hi there,
We'd like to use a plpgsql function to use results from query A toexecute
several queries B, C, etc., and return the results of all B, C, etc
queries
as one result set. Would placing 'RETURN QUERY' inside a loop
automatically
concatenate all 'return query' results in the function's return? If
not, how
would we go about getting this result?
all the queries would have to have the same fields to do this. if they
do, then you can write it as a join or union.in your example case, its easy.
select * from locations l join zipcode z on l.state = z.state where
z.zipcode like '32301%';this also would be more efficient than the way you proposed
now, if you're thinking of a DIFFERENT problem thats more complex to
solve, well, without knowing the actual problem there's not much I can
suggest.--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general