Re: How do we combine and return results from multiple queries in a loop?

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(at)gmail(dot)com> wrote:

> Hi Pavel, I'm running 8.4
>
>
> On Mon, May 16, 2011 at 3:58 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>wrote:
>
>> Hello
>>
>> 2011/5/16 Bernardo Telles <btelles(at)gmail(dot)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(at)yahoo(dot)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(at)postgresql(dot)org
>> >> [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Bernardo
>> Telles
>> >> Sent: Monday, May 16, 2011 1:13 PM
>> >> To: pgsql-general(at)postgresql(dot)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(at)hogranch(dot)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(at)postgresql(dot)org)
>> >> To make changes to your subscription:
>> >> http://www.postgresql.org/mailpref/pgsql-general
>> >>
>> >>
>> >
>>
>
>