Thread: "EXECUTE command-string INTO target USING expression" isn't working
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
Hi,
I have create small proof of concept (pg v. 9.1.3):
1. to map Your dynamic function:
CREATE OR REPLACE FUNCTION public.testReturnDynamic(OUT "retValue" TEXT)
RETURNS text
AS
$BODY$
BEGIN
"retValue" = 'aaa';
END;
$BODY$
LANGUAGE plpgsql SECURITY DEFINER;
2. to test function
SELECT public.testReturnDynamic();
3. to do the shortcut of Your loop
DO
$$
DECLARE
t TEXT;
routine TEXT;
dynSQL TEXT;
BEGIN
routine = 'public.testReturnDynamic';
dynSQL = 'SELECT * FROM ' || routine || '();';
EXECUTE dynSQL INTO t;
RAISE NOTICE 'OUTPUT: %', t;
END;
$$
and received:
NOTICE: OUTPUT: aaa
so, works as expected.
Maybe one of Your functions has more then one column, or returns something different then text?
message in error looks like problem inside executed procedure.
Regards,
Bartek
Bartek
2012/4/18 Ken Winter <ken@sunward.org>
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general