pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedreturning values from dynamic SQL to a variable
5+ messages / 5 participants
[nested] [flat]
* returning values from dynamic SQL to a variable
@ 2012-09-08 19:39 James Sharrett <jsharrett@tidemark.net>
0 siblings, 1 reply; 5+ messages in thread
From: James Sharrett @ 2012-09-08 19:39 UTC (permalink / raw)
To: pgsql-sql
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"
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: returning values from dynamic SQL to a variable
@ 2012-09-10 11:15 Sergey Konoplev <gray.ru@gmail.com>
parent: James Sharrett <jsharrett@tidemark.net>
0 siblings, 1 reply; 5+ messages in thread
From: Sergey Konoplev @ 2012-09-10 11:15 UTC (permalink / raw)
To: James Sharrett <jsharrett@tidemark.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: returning values from dynamic SQL to a variable
@ 2013-01-15 17:08 kgeographer <karl.geog@gmail.com>
parent: Sergey Konoplev <gray.ru@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: kgeographer @ 2013-01-15 17:08 UTC (permalink / raw)
To: pgsql-sql
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-tp5723322p57...
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: returning values from dynamic SQL to a variable
@ 2013-01-15 18:23 Pavel Stehule <pavel.stehule@gmail.com>
parent: kgeographer <karl.geog@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Pavel Stehule @ 2013-01-15 18:23 UTC (permalink / raw)
To: kgeographer <karl.geog@gmail.com>; +Cc: pgsql-sql
Hello
you can use RETURN QUERY EXECUTE statement
http://www.postgresql.org/docs/9.1/interactive/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RE...
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-tp5723322p57...
> 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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: returning values from dynamic SQL to a variable
@ 2013-01-16 00:40 Karl Grossner <karl.geog@gmail.com>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Karl Grossner @ 2013-01-16 00:40 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: pgsql-sql
Pavel -
RETURN QUERY EXECUTE worked, many thanks for responding so quickly. The
docs show no relevant examples, so for anyone else, something like this
create or replace function getRowsE(
OUT element character(1), OUT name character varying(100), OUT sum
numeric
) returns setof record as $BODY$
declare
r record;
i integer;
usesql text;
begin
for r in select * from mytable where id is not null order by id loop
i := r.graphid;
usesql := 'bunch of sql where ' || i || 'something or other,
producing element, name, sum';
RETURN QUERY EXECUTE usesql;
end loop;
return;
end;
$BODY$ language 'plpgsql';
On 1/15/2013 10:23 AM, Pavel Stehule wrote:
> Hello
>
> you can use RETURN QUERY EXECUTE statement
>
> http://www.postgresql.org/docs/9.1/interactive/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RE...
>
> 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-tp5723322p57...
>> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2013-01-16 00:40 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-09-08 19:39 returning values from dynamic SQL to a variable James Sharrett <jsharrett@tidemark.net>
2012-09-10 11:15 ` Sergey Konoplev <gray.ru@gmail.com>
2013-01-15 17:08 ` kgeographer <karl.geog@gmail.com>
2013-01-15 18:23 ` Pavel Stehule <pavel.stehule@gmail.com>
2013-01-16 00:40 ` Karl Grossner <karl.geog@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox