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

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

Responses

Browse pgsql-general by date

  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