BUG #5352: Bug in PL/PgSQL "SELECT .. INTO" statement parser

The following bug has been logged online:

Bug reference: 5352
Logged by: Oleg
Email address: serovov(at)gmail(dot)com
PostgreSQL version: any
Operating system: any
Description: Bug in PL/PgSQL "SELECT .. INTO" statement parser
Details:

CREATE TABLE test2 (
id BIGINT,
chunk_id BIGINT
);
CREATE TABLE test1 (
id BIGINT
);

CREATE OR REPLACE FUNCTION "bug" () RETURNS pg_catalog.void AS
$body$
DECLARE
row_test1 test1%rowtype;
row_test2 test2%rowtype;
BEGIN
SELECT test1, chunk_id
FROM test1 JOIN test2 ON(chunk.id = test2.chunk_id)
LIMIT 1
INTO row_test1, row_test2;

END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY DEFINER;

Will throw error:
ERROR: LIMIT #,# syntax is not supported
HINT: Use separate LIMIT and OFFSET clauses.
QUERY: SELECT test1, chunk_id FROM test1 JOIN test2 ON(chunk.id =
test2.chunk_id) LIMIT 1, 0, $1
CONTEXT: SQL statement in PL/PgSQL function "bug" near line 8

On Mon, Mar 1, 2010 at 02:22, Oleg <serovov(at)gmail(dot)com> wrote:
> CREATE OR REPLACE FUNCTION "bug" () RETURNS pg_catalog.void AS
> $body$
> DECLARE
>   row_test1 test1%rowtype;
>   row_test2 test2%rowtype;
> BEGIN
>   SELECT test1, chunk_id
>       FROM test1 JOIN test2 ON(chunk.id = test2.chunk_id)
>       LIMIT 1
>       INTO row_test1, row_test2;

*shrug* it works if you put the INTO after SELECT. From the manual:

http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-SELECT-INTO

: 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.

"Oleg" <serovov(at)gmail(dot)com> writes:
> DECLARE
> row_test1 test1%rowtype;
> row_test2 test2%rowtype;
> BEGIN
> SELECT test1, chunk_id
> FROM test1 JOIN test2 ON(chunk.id = test2.chunk_id)
> LIMIT 1
> INTO row_test1, row_test2;

> Will throw error:
> ERROR: LIMIT #,# syntax is not supported

The reason you're getting the weird error is that INTO is defined
to take either a list of scalar variables or a single rowtype variable.
Since row_test1 is a rowtype, the INTO is just "INTO row_test1" and
what's left in the actual SELECT statement is "LIMIT 1, row_test2".

I'm not sure whether we can do anything to make the error message
saner. It would be possible to throw error if the next token is a
comma after we've swallowed an "INTO rowtype_variable" clause, but
I'm afraid that that would break functions that work fine today.
So that cure might be worse than the disease. I seem to recall
having seen similar confusion before, though, so maybe we should
do it. I guess one argument for doing that is that we might someday
change plpgsql to allow multiple rowtype targets, in which case the
interpretation would change anyway...

In any case, the function is wrong as it stands.

regards, tom lane