From: | Bernardo Telles <btelles(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | How do we combine and return results from multiple queries in a loop? |
Date: | 2011-05-16 03:53:30 |
Message-ID: | BANLkTi=kyPfpDV4H2isoHPX0VWxRSREYzw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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?
Here's an example. The function finds all zip_code records with the given
code, and then searches all locations for a state of that original zip_code.
(I know the current query could be done via a join, but my ACTUAL query
would require this functionality.)
begin;
create OR REPLACE function t() returns setof locations as
$$
declare z zip_codes%rowtype;
begin
for z in select * from zip_codes where code like '%32301%'
LOOP
return query select * from locations where locations.state like
z.state; #query B
# All I want to do is return the results from all of the above
queries as one
# result set.
END LOOP;
return;
end
$$
language 'plpgsql';
commit;
Any idea how I do that?
From | Date | Subject | |
---|---|---|---|
Next Message | John R Pierce | 2011-05-16 05:28:09 | Re: How do we combine and return results from multiple queries in a loop? |
Previous Message | Szymon Guz | 2011-05-15 19:26:14 | Re: Column names getting lower-case in SELECT statements when issued via JDBC |