Re: returning values from dynamic SQL to a variable

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 <karl(dot)geog(at)gmail(dot)com>:
> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql