Re: PL/pgsSQL EXECUTE USING INTO

While testing the recent issue with unknown params in EXECUTE USING, I
accidentally did this:

postgres=# DO $$
DECLARE
t text;
BEGIN
EXECUTE 'SELECT ''foo'' || $1' USING 'bar' INTO t;
RAISE NOTICE '%', t;
END;
$$;
NOTICE: <NULL>
DO

The mistake I made? I put the USING and INTO clauses in wrong order,
INTO needs to go first. We should throw an error on that, but it looks
like the INTO clause is just silently ignored.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Thu, Aug 19, 2010 at 4:29 AM, Heikki Linnakangas
<heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
> While testing the recent issue with unknown params in EXECUTE USING, I
> accidentally did this:
>
> postgres=# DO $$
> DECLARE
>  t text;
> BEGIN
>  EXECUTE 'SELECT ''foo'' || $1' USING 'bar' INTO t;
>  RAISE NOTICE '%', t;
> END;
> $$;
> NOTICE:  <NULL>
> DO
>
> The mistake I made? I put the USING and INTO clauses in wrong order, INTO
> needs to go first. We should throw an error on that, but it looks like the
> INTO clause is just silently ignored.

Another option would be to make it work as expected.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

Excerpts from Heikki Linnakangas's message of jue ago 19 04:29:19 -0400 2010:
> While testing the recent issue with unknown params in EXECUTE USING, I
> accidentally did this:
>
> postgres=# DO $$
> DECLARE
> t text;
> BEGIN
> EXECUTE 'SELECT ''foo'' || $1' USING 'bar' INTO t;
> RAISE NOTICE '%', t;
> END;
> $$;
> NOTICE: <NULL>
> DO
>
> The mistake I made? I put the USING and INTO clauses in wrong order,
> INTO needs to go first. We should throw an error on that, but it looks
> like the INTO clause is just silently ignored.

Can't we just accept either order?

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
> While testing the recent issue with unknown params in EXECUTE USING, I
> accidentally did this:

> EXECUTE 'SELECT ''foo'' || $1' USING 'bar' INTO t;

> The mistake I made? I put the USING and INTO clauses in wrong order,
> INTO needs to go first. We should throw an error on that, but it looks
> like the INTO clause is just silently ignored.

This is more interesting than it looks. It appears that the plpgsql
parser interprets the USING's argument expression as being
'bar' INTO t
so it generates a plplgsql expression with query
SELECT 'bar' INTO t
and the only reason that you don't get a failure is that
exec_simple_check_plan fails to notice the intoClause, so it thinks
this represents a "simple expression", which means it evaluates the
'bar' subexpression and ignores the INTO altogether. That's
certainly a bug in exec_simple_check_plan :-(

I think that accepting this order of the clauses would require some
duplication of code in the stmt_dynexecute production. It might be
worth doing anyway, because if you made this mistake then certainly
others will.

regards, tom lane