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 <btel...@gmail.com> wrote: > Hi Pavel, I'm running 8.4 > > > On Mon, May 16, 2011 at 3:58 PM, Pavel Stehule <pavel.steh...@gmail.com>wrote: > >> Hello >> >> 2011/5/16 Bernardo Telles <btel...@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 <pol...@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-ow...@postgresql.org >> >> [mailto:pgsql-general-ow...@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 <pie...@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 >> >> >> >> >> > >> > >
