From: | "Ken Winter" <ken(at)sunward(dot)org> |
---|---|
To: | "PostgreSQL pg-general List" <pgsql-general(at)postgresql(dot)org> |
Subject: | "EXECUTE command-string INTO target USING expression" isn't working |
Date: | 2012-04-18 20:31:08 |
Message-ID: | FA15595A8CBF4B2C962B50F7D9794E68@KenIBM |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Mead | 2012-04-18 21:00:27 | Re: Two instances show same databases |
Previous Message | Eliot Gable | 2012-04-18 19:58:37 | Re: LOCK TABLE is not allowed in a non-volatile function |