User-Agent: Microsoft-MacOutlook/14.2.3.120616
Date: Sat, 08 Sep 2012 18:23:36 -0400
Subject: returning values to variables from dynamic SQL
From: James Sharrett <jsharrett@tidemark.net>
To: <pgsql-sql@postgresql.org>
Message-ID: <CC713DA8.32CC%jsharrett@tidemark.net>
Thread-Topic: returning values to variables from dynamic SQL
Mime-version: 1.0
Content-type: multipart/alternative;
	boundary="B_3429973421_9601075"

> This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

--B_3429973421_9601075
Content-type: text/plain;
	charset="US-ASCII"
Content-transfer-encoding: 7bit

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"


--B_3429973421_9601075
Content-type: text/html;
	charset="US-ASCII"
Content-transfer-encoding: quoted-printable

<html><head></head><body style=3D"word-wrap: break-word; -webkit-nbsp-mode: s=
pace; -webkit-line-break: after-white-space; color: rgb(0, 0, 0); font-size:=
 14px; font-family: Calibri, sans-serif; "><div><div>I have a PG function ( =
using plpgsql) that calls a number of sub functions also in plpgsql. &nbsp;I=
 have two main problems that seem to be variations on the same theme of runn=
ing dynamic SQL from a variable with the EXECUTE statement and returning the=
 results back to a variable defined in the calling function.</div><div><br><=
/div><div>Problem 1: &nbsp;return the results of a table query to a variable=
.</div><div><br></div><div>I have a logging table that my sub functions writ=
e to. &nbsp;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 fun=
ctions. &nbsp;I've properly declared the variable (v_runnumber) and the data=
 type is correct. &nbsp;The following statement works fine in the main funct=
ion and stores the value in the variable.</div><div><br></div><div>&nbsp; se=
lect max(runnumber) into v_runnumber from MySchema.log_table;</div><div><br>=
</div><div>However, MySchema is a parameter that gets passed into the main f=
unction because I need this to work for multiple schemas. &nbsp;If I try and=
 make this dynamic by using the following statement:</div><div><br></div><di=
v>Sql :=3D 'select max(run number) into v_runnumber from ' || MySchema || '.lo=
g_table;';</div><div>Execute Sql;</div><div><br></div><div>I get the followi=
ng error message (even though the resulting value in the text variable Sql i=
s valid code):</div><div><br></div><div><p style=3D"margin: 0px; font-size: 12=
px; font-family: Monaco; ">ERROR: query string argument of EXECUTE is null</=
p><p style=3D"margin: 0px; font-size: 12px; font-family: Monaco; ">SQL state: =
22004</p><p style=3D"margin: 0px; font-size: 12px; font-family: Monaco; "><br>=
</p><p style=3D"margin: 0px; font-size: 12px; font-family: Monaco; "><br></p><=
p style=3D"margin: 0px; font-size: 12px; font-family: Monaco; ">Problem 2: ret=
urning the results of a function call to a variable.</p><p style=3D"margin: 0p=
x; font-size: 12px; font-family: Monaco; "><br></p><p style=3D"margin: 0px; fo=
nt-size: 12px; font-family: Monaco; ">This is a similar issue to #1 but in t=
his case, I'm calling a function from the main function and trying to get th=
e return value back (a single integer) from the sub function to test for err=
ors. &nbsp;Again, I'm calling the function with &nbsp;dynamical SQL because =
of the need to take user values from the main function to call the sub funct=
ions. &nbsp;The function call:</p><p style=3D"margin: 0px; font-size: 12px; fo=
nt-family: Monaco; "><br></p><p style=3D"margin: 0px; font-size: 12px; font-fa=
mily: Monaco; "></p><p style=3D"margin: 0px; ">sql :=3D 'select * from public.el=
t_set_locking(1,' || quote_literal(tenant) || ',' &nbsp;|| quote_literal(app=
) || ',' &nbsp;|| quote_literal(cycle) || ',' &nbsp;|| v_runnumber || ');';<=
/p><p style=3D"margin: 0px; ">execute sql;</p><p></p></div><div><br></div><div=
>Works fine. &nbsp;However when I try and store the value coming back from t=
he function into a main variable with the following call I get an error:</di=
v><div><br></div><div><div>sql :=3D 'select * into v_retcode from public.elt_s=
et_locking(1,' || quote_literal(tenant) || ',' &nbsp;|| quote_literal(app) |=
| ',' &nbsp;|| quote_literal(cycle) || ',' &nbsp;|| v_runnumber || ');';</di=
v><div>&nbsp;execute sql;</div></div><div><br></div><div>"EXECUTE of SELECT =
... INTO is not implemented"</div></div></body></html>

--B_3429973421_9601075--