Thread: returning values from dynamic SQL to a variable

returning values from dynamic SQL to a variable

From
James Sharrett
Date:
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"

Re: returning values from dynamic SQL to a variable

From
Sergey Konoplev
Date:
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



Re: returning values from dynamic SQL to a variable

From
kgeographer
Date:
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.



Re: returning values from dynamic SQL to a variable

From
Pavel Stehule
Date:
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



Re: returning values from dynamic SQL to a variable

From
Karl Grossner
Date:
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 />