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