Re: BUG #14549: pl/pgsql parser

I think there are two potential problems in the origin SQL
"SELECT 1, 2, 3 INTO vara, varb AS varc".

1. like "SELECT 1, 2, 3 INTO a, b"

Query result is "1, 2, 3", target is "a, b". The problem is that the query's
result columns don't match the structure of the target.

I think there is a difference between behavior and document at this point.

2. like "SELECT 1, 2, 3 INTO a, b, c AS x"

This SQL is equivalent to "SELECT 1, 2, 3 AS x INTO a, b, c".
There are other equivalent SQLs sush as
"SELECT INTO a, b, c 1, 2, 3 AS x",
"SELECT 1, 2, INTO a, b, c 3 AS x" and
"SELECT 1, INTO a, b, c 2, 3 AS x".

This is in conformity with document.

Thanks,
Wei Congrui

2017-02-18 0:54 GMT+08:00 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:

> Wei Congrui <crvv(dot)mail(at)gmail(dot)com> writes:
> > In the document,
> > https://www.postgresql.org/docs/9.6/static/plpgsql-
> statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW
> > "If a row or a variable list is used as target, the query's result
> > columns must exactly match the structure of the target as to
> > number and data types, or else a run-time error occurs. When
> > a record variable is the target, it automatically configures itself
> > to the row type of the query result columns."
>
> > I think this is a bug according to the document.
>
> I don't think that's the relevant point. What is relevant is the
> next paragraph:
>
> "The INTO clause can appear almost anywhere in the SQL
> command. Customarily it is written either just before or just after the
> list of select_expressions in a SELECT command, or at the end of the
> command for other command types. It is recommended that you follow this
> convention in case the PL/pgSQL parser becomes stricter in future
> versions."
>
> What's happening in Stefan's example
>
> SELECT 1, 2, 3 INTO vara, varb AS varc;
>
> is that "INTO vara, varb" is pulled out as being the INTO clause, and
> what's left is
>
> SELECT 1, 2, 3 AS varc;
>
> which is a perfectly legal SQL statement so no error is reported.
>
> To make this throw an error, we'd need to become stricter about the
> placement of INTO (as the manual hints), or become stricter about the
> number of SELECT output columns matching the number of INTO target
> variables, or possibly both. Any such change would doubtless draw
> complaints from people whose code worked fine before. It might be
> a good idea anyway, but selling backwards-compatibility breakage
> to the Postgres community is usually a hard sell.
>
> regards, tom lane
>