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

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

2012/4/18 Ken Winter <ken(at)sunward(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>