Thread: Weird misinterpretation of EXECUTE in PL/pgSQL

PostgreSQL 8.2.1

This is OK:

test=# create function test1() returns int language plpgsql as $$begin execute 'select a, b'; end$$;
CREATE FUNCTION
test=# select test1();
ERROR:  column "a" does not exist
LINE 1: select a, b
               ^
QUERY:  select a, b
CONTEXT:  PL/pgSQL function "test1" line 1 at execute statement

This is weird:

test=# create function test2() returns int language plpgsql as $$begin execute 'select a', 'b'; end$$;
/* Several arguments separated by comma -- doesn't make sense */
CREATE FUNCTION
test=# select test2();
ERROR:  query "SELECT  'select a', 'b'" returned 2 columns
CONTEXT:  PL/pgSQL function "test2" line 1 at execute statement

In the presence of a comma-separated list of arguments it seems to
intepret the EXECUTE command wildly differently.  What is going on here?

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Peter Eisentraut <peter_e@gmx.net> writes:
> In the presence of a comma-separated list of arguments it seems to
> intepret the EXECUTE command wildly differently.  What is going on here?

Basically it evaluates the string-producing argument by sticking SELECT
on the front and handing it to the main SQL engine.  So what you've got
there is a SELECT that produces two result columns, where the EXECUTE
code was only expecting to get one.  (Look at exec_eval_expr in pl_exec.c)

It's a syntax error in any case; not sure if we can easily produce a
better error message, or what a better error message would be.

            regards, tom lane