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

[Bartosz Dmytrak <bdmytrak@xxxxxxxxx>]

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@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