Re: returning values from dynamic SQL to a variable

Pavel - <br /><br /> RETURN QUERY EXECUTE worked, many thanks for responding so quickly. The docs show no relevant
examples,so for anyone else, something like this<br /><font face="courier     new,courier,monaco,monospace,sans-serif"
size="2"><br/> create or replace function getRowsE(<br />     OUT element character(1), OUT name character
varying(100),OUT sum numeric<br /> ) returns setof record as $BODY$<br /> declare<br />  r record;<br />  i integer;<br
/> usesql text;<br /> begin<br />  for r in select * from mytable where id is not null order by id loop<br />   i :=
r.graphid;<br/>   usesql := 'bunch of sql where ' || i || 'something or other, producing element, name, sum';<br />  
RETURNQUERY EXECUTE usesql;<br />  end loop;<br />  return;<br /> end;</font><br /><font face="courier
new,courier,monaco,monospace,sans-serif"size="2">$BODY$ language 'plpgsql';<br /><br /><br /></font><div
class="moz-cite-prefix">On1/15/2013 10:23 AM, Pavel Stehule wrote:<br /></div><blockquote
cite="mid:CAFj8pRBqxje9RNmnJribA82bvX4DsqSpL=syQyUw5A8+DCm+SQ@mail.gmail.com"type="cite"><pre wrap="">Hello 

you can use RETURN QUERY EXECUTE statement

<a class="moz-txt-link-freetext"
href="http://www.postgresql.org/docs/9.1/interactive/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING">http://www.postgresql.org/docs/9.1/interactive/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING</a>

Regards

Pavel Stehule

2013/1/15 kgeographer <a class="moz-txt-link-rfc2396E"
href="mailto:karl.geog@gmail.com"><karl.geog@gmail.com></a>:
</pre><blockquote type="cite"><pre wrap="">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 $$
declarer record;loopy record;i integer;sql text;
beginfor 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: <a class="moz-txt-link-freetext"
href="http://postgresql.1045698.n5.nabble.com/returning-values-from-dynamic-SQL-to-a-variable-tp5723322p5740324.html">http://postgresql.1045698.n5.nabble.com/returning-values-from-dynamic-SQL-to-a-variable-tp5723322p5740324.html</a>
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.


--
Sent via pgsql-sql mailing list (<a class="moz-txt-link-abbreviated"
href="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a>)
To make changes to your subscription:
<a class="moz-txt-link-freetext"
href="http://www.postgresql.org/mailpref/pgsql-sql">http://www.postgresql.org/mailpref/pgsql-sql</a>
</pre></blockquote></blockquote><br />