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

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
> >>
> >>
> >
>