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

["Ken Winter" <ken@xxxxxxxxxxx>]

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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general