Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TvH3t-00076E-KH for pgsql-sql@arkaria.postgresql.org; Wed, 16 Jan 2013 00:41:01 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1TvH3t-0005sV-5A for pgsql-sql@arkaria.postgresql.org; Wed, 16 Jan 2013 00:41:01 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TvH3s-0005sP-3K for pgsql-sql@postgresql.org; Wed, 16 Jan 2013 00:41:00 +0000 Received: from smtp1.stanford.edu ([171.67.219.81] helo=smtp.stanford.edu) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TvH3q-00047z-0m for pgsql-sql@postgresql.org; Wed, 16 Jan 2013 00:40:59 +0000 Received: from smtp.stanford.edu (localhost.localdomain [127.0.0.1]) by localhost (Postfix) with SMTP id E195D12D18F; Tue, 15 Jan 2013 16:40:54 -0800 (PST) Received: from [171.67.138.24] (karlq-atl-D3500.Stanford.EDU [171.67.138.24]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) (Authenticated sender: karlg) by smtp.stanford.edu (Postfix) with ESMTPSA id 7311912D2F2; Tue, 15 Jan 2013 16:40:54 -0800 (PST) Message-ID: <50F5F700.30202@gmail.com> Date: Tue, 15 Jan 2013 16:40:32 -0800 From: Karl Grossner Reply-To: karl.geog@gmail.com Organization: Stanford University User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:17.0) Gecko/20130107 Thunderbird/17.0.2 MIME-Version: 1.0 To: Pavel Stehule CC: pgsql-sql@postgresql.org Subject: Re: returning values from dynamic SQL to a variable References: <1358269730873-5740324.post@n5.nabble.com> In-Reply-To: Content-Type: multipart/alternative; boundary="------------030302040200010508050402" X-Pg-Spam-Score: 1.2 (+) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org This is a multi-part message in MIME format. --------------030302040200010508050402 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit Pavel - RETURN QUERY EXECUTE worked, many thanks for responding so quickly. The docs show no relevant examples, so for anyone else, something like this create or replace function getRowsE( OUT element character(1), OUT name character varying(100), OUT sum numeric ) returns setof record as $BODY$ declare r record; i integer; usesql text; begin for r in select * from mytable where id is not null order by id loop i := r.graphid; usesql := 'bunch of sql where ' || i || 'something or other, producing element, name, sum'; RETURN QUERY EXECUTE usesql; end loop; return; end; $BODY$ language 'plpgsql'; On 1/15/2013 10:23 AM, Pavel Stehule wrote: > Hello > > you can use RETURN QUERY EXECUTE statement > > http://www.postgresql.org/docs/9.1/interactive/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING > > Regards > > Pavel Stehule > > 2013/1/15 kgeographer : >> I have a related problem and tried the PERFORM...EXECUTE pattern suggested >> but no matter where I put PERFORM I get 'function not found' errors. >> >> I want to loop through id values returned by a query and execute another >> with each i as a parameter. Each subquery will return 6-8 rows. This is a >> simplified example, in the real app the subquery is doing some aggregation >> work. >> >> Tried many many things including this pattern below and read everything I >> could find, but no go. Any help appreciated. >> >> ++++++++++++++++ >> create or replace function getRowsA() returns setof record as $$ >> declare >> r record; >> loopy record; >> i integer; >> sql text; >> begin >> for r in select * from cities loop >> i := r.id; >> sql := 'select city,topic,weight from v_doctopic where city = ' || i; >> EXECUTE sql; >> return next loopy; >> end loop; >> return; >> end; >> $$ language 'plpgsql'; >> >> select * from getRowsA() AS foo(city int, topic int, weight numeric) >> >> >> >> ----- >> karlg >> -- >> View this message in context: http://postgresql.1045698.n5.nabble.com/returning-values-from-dynamic-SQL-to-a-variable-tp5723322p5740324.html >> Sent from the PostgreSQL - sql mailing list archive at Nabble.com. >> >> >> -- >> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql --------------030302040200010508050402 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Pavel -

RETURN QUERY EXECUTE worked, many thanks for responding so quickly. The docs show no relevant examples, so for anyone else, something like this

create or replace function getRowsE(
=C2=A0=C2=A0=C2=A0 OUT element character(1), OUT name character var= ying(100), OUT sum numeric
) returns setof record as $BODY$
declare
=C2=A0r record;
=C2=A0i integer;
=C2=A0usesql text;
begin
=C2=A0for r in select * from mytable where id is not null order by = id loop
=C2=A0 i :=3D r.graphid;
=C2=A0 usesql :=3D 'bunch of sql where ' || i || 'something or othe= r, producing element, name, sum';
=C2=A0 RETURN QUERY EXECUTE usesql;
=C2=A0end loop;
=C2=A0return;
end;
$BODY$ language 'plpgsql';
On 1/15/2013 10:23 AM, Pavel Stehule wrote:
Hello

you can use RETURN QUERY EXECUTE statement

http://www.postgresql.org/docs/9.1/interactive/plpgsql-control-stru=
ctures.html#PLPGSQL-STATEMENTS-RETURNING

Regards

Pavel Stehule

2013/1/15 kgeographer <karl.geog@gmail.com>:

I have a related problem and tried the PERFORM...E=
XECUTE pattern suggested
but no matter where I put PERFORM I get 'function not found' errors.

I want to loop through id values returned by a query and execute another
with each i as a parameter. Each subquery will return 6-8 rows. This is a
simplified example, in the real app the subquery is doing some aggregatio=
n
work.

Tried many many things including this pattern below and read everything I
could find, but no go. Any help appreciated.

++++++++++++++++
create or replace function getRowsA() returns setof record as $$
declare
 r record;
 loopy record;
 i integer;
 sql text;
begin
 for r in select * from cities loop
  i :=3D r.id;
  sql :=3D 'select city,topic,weight from v_doctopic where city =3D ' || =
i;
  EXECUTE sql;
  return next loopy;
 end loop;
 return;
end;
$$ language 'plpgsql';

select * from getRowsA() AS foo(city int, topic int, weight numeric)



-----
karlg
--
View this message in context: http://postgresql.1045698.n5.nabbl=
e.com/returning-values-from-dynamic-SQL-to-a-variable-tp5723322p5740324.h=
tml
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

--------------030302040200010508050402--