Re: returning values from dynamic SQL to a variable

Поиск
Список
Период
Сортировка
От Sergey Konoplev
Тема Re: returning values from dynamic SQL to a variable
Дата
Msg-id CAL_0b1vmtwqqjK4B2o7p9n3CWnV0SqGw6qms2zjm0oRfak=Myg@mail.gmail.com
обсуждение исходный текст
Ответ на returning values from dynamic SQL to a variable  (James Sharrett <jsharrett@tidemark.net>)
Ответы Re: returning values from dynamic SQL to a variable  (kgeographer <karl.geog@gmail.com>)
Список 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



В списке pgsql-sql по дате отправления:

Предыдущее
От: David Johnston
Дата:
Сообщение: Re: Query with LIMIT clause
Следующее
От: Gary Stainburn
Дата:
Сообщение: weird join producing too many rows