Thread: EXECUTE does not process parameters

The SQL-level EXECUTE statement does not process parameters in its
arguments.  So for example, the following does not work:

create table t1 (a int);

prepare p1 as insert into t1 (a) values ($1);

create function f2(x int) returns int
language sql
as $$
execute p1($1);  -- HERE
select null::int;
$$;

select f2(2);
ERROR:  there is no parameter $1

Another variant of this problem that is perhaps more interesting in
practice is that you can't pass bind parameters to an EXECUTE call via
PQexecParams().

It seems this needs some special treatment in transformStmt().  Any
reason not to do that?

There is an adjacent question why EXECUTE is not allowed as the final
data-returning statement in an SQL-language function.  So I suspect the
answer to all this is that no one has ever seriously tried this before.

Come to think of it, it would probably also be useful if PREPARE did
parameter processing, again in order to allow use with PQexecParams().
There might be other statements currently omitted as well.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
> The SQL-level EXECUTE statement does not process parameters in its
> arguments.  So for example, the following does not work:
> ...
> It seems this needs some special treatment in transformStmt().  Any
> reason not to do that?

I have a vague recollection of having poked at this years ago and
found out that there were some interesting corner cases to worry about.
I think they had to do with parameters being passed through multiple
execution layers, but I don't remember for sure.  All I can say is
that this is probably trickier than it looks.

            regards, tom lane