Re: How do we combine and return results from multiple queries in a loop? - Mailing list 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
>>
>>
>