Thread: How do we combine and return results from multiple queries in a loop?
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.
Re: How do we combine and return results from multiple queries in a loop?
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 05/15/11 8:53 PM, Bernardo Telles wrote: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.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?
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
Re: How do we combine and return results from multiple queries in a loop?
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
Re: How do we combine and return results from multiple queries in a loop?
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.
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
>
> 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
>>
>>
>
Re: How do we combine and return results from multiple queries in a loop?
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 kidWhat PostgreSQL version do you have?
> 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 :-/
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 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
>>
>>
>
Re: How do we combine and return results from multiple queries in a loop?
Thanks again, everyone, for all your help!
Hi Pavel, I'm running 8.4On 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 kidWhat PostgreSQL version do you have?
> 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 :-/
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 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
>>
>>
>