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

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