Re: returning values from dynamic SQL to a variable

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