"EXECUTE command-string INTO target USING expression" isn't working

Поиск
Список
Период
Сортировка
От Ken Winter
Тема "EXECUTE command-string INTO target USING expression" isn't working
Дата
Msg-id FA15595A8CBF4B2C962B50F7D9794E68@KenIBM
обсуждение исходный текст
Ответы Re: "EXECUTE command-string INTO target USING expression" isn't working
Список pgsql-general
I swear this used to work, but in PostgreSQL 9.1 it doesn't work any more...

CASE 1: If I write it like this:

    FOR func IN (
        SELECT * FROM information_schema.routines
        WHERE routine_schema = 'tests'
    ) LOOP
        q := 'SELECT tests.' || func.routine_name || '()';
         EXECUTE q INTO r;
       ...
    END LOOP;

on the first time through the loop I get this error:

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function "cre_supers_for_organization_i" line 12 at SQL
statement
SQL statement "INSERT INTO organization (name, status) VALUES (str, 'Closed
Ongoing Group')"
PL/pgSQL function "event" line 32 at SQL statement
SQL statement "SELECT tests.event()"
PL/pgSQL function "run_all_tests" line 16 at EXECUTE statement
SQL state: 42601

CASE 2: If I write it like this:

    FOR func IN (
        SELECT * FROM information_schema.routines
        WHERE routine_schema = 'tests'
    ) LOOP
        q := 'SELECT tests.$1()';
        EXECUTE q INTO r USING func.routine_name;
       ...
    END LOOP;

on the first time through the loop I get this error:

ERROR:  syntax error at or near "$1"
LINE 1: SELECT tests.$1()
                     ^
QUERY:  SELECT tests.$1()
CONTEXT:  PL/pgSQL function "run_all_tests" line 17 at EXECUTE statement
SQL state: 42601

In both cases, each of the functions to be called returns a string, and r is
a VARCHAR variable.

What's wrong with this picture?

~ Thanks in advance for your help
~ Ken



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

Предыдущее
От: Eliot Gable
Дата:
Сообщение: Re: LOCK TABLE is not allowed in a non-volatile function
Следующее
От: Scott Mead
Дата:
Сообщение: Re: Two instances show same databases