Thread: returning values from dynamic SQL to a variable
I have a PG function ( using plpgsql) that calls a number of sub functions also in plpgsql. I have two main problems that seem to be variations on the same theme of running dynamic SQL from a variable with the EXECUTE statement and returning the results back to a variable defined in the calling function.
Problem 1: return the results of a table query to a variable.
I have a logging table that my sub functions write to. At the beginning of my main function I want to read a run number from the logging table and increment it by one to then pass into my sub functions. I've properly declared the variable (v_runnumber) and the data type is correct. The following statement works fine in the main function and stores the value in the variable.
select max(runnumber) into v_runnumber from MySchema.log_table;
However, MySchema is a parameter that gets passed into the main function because I need this to work for multiple schemas. If I try and make this dynamic by using the following statement:
Sql := 'select max(run number) into v_runnumber from ' || MySchema || '.log_table;';
Execute Sql;
I get the following error message (even though the resulting value in the text variable Sql is valid code):
ERROR: query string argument of EXECUTE is null
SQL state: 22004
Problem 2: returning the results of a function call to a variable.
This is a similar issue to #1 but in this case, I'm calling a function from the main function and trying to get the return value back (a single integer) from the sub function to test for errors. Again, I'm calling the function with dynamical SQL because of the need to take user values from the main function to call the sub functions. The function call:
sql := 'select * from public.elt_set_locking(1,' || quote_literal(tenant) || ',' || quote_literal(app) || ',' || quote_literal(cycle) || ',' || v_runnumber || ');';
execute sql;
Works fine. However when I try and store the value coming back from the function into a main variable with the following call I get an error:
sql := 'select * into v_retcode from public.elt_set_locking(1,' || quote_literal(tenant) || ',' || quote_literal(app) || ',' || quote_literal(cycle) || ',' || v_runnumber || ');';
execute sql;
"EXECUTE of SELECT ... INTO is not implemented"
On Sat, Sep 8, 2012 at 11:39 PM, James Sharrett <jsharrett@tidemark.net> wrote: > Sql := 'select max(run number) into v_runnumber from ' || MySchema || > '.log_table;'; > Execute Sql; > > I get the following error message (even though the resulting value in the > text variable Sql is valid code): > > ERROR: query string argument of EXECUTE is null It means that the MySchema variable is NULL. (it smells like you might have a character case issue here) > sql := 'select * into v_retcode from public.elt_set_locking(1,' || > quote_literal(tenant) || ',' || quote_literal(app) || ',' || > quote_literal(cycle) || ',' || v_runnumber || ');'; > execute sql; > > "EXECUTE of SELECT ... INTO is not implemented" Just remove "into v_retcode" from the sql string and specify it in the EXECUTE like this: sql := 'select * from public.elt_set_locking(...'; EXECUTE sql INTO v_retcode; Here you will find more info about EXECUTE in plpgsql. http://www.postgresql.org/docs/9.1/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN -- Sergey Konoplev a database and software architect http://www.linkedin.com/in/grayhemp Jabber: gray.ru@gmail.com Skype: gray-hemp Phone: +79160686204
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: 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.
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.geog@gmail.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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql
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 />