Thread: 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@gmail.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@gmail.com> wrote: > CREATE OR REPLACE FUNCTION "bug" () RETURNS pg_catalog.void AS > $body$ > DECLARE > =C2=A0 row_test1 test1%rowtype; > =C2=A0 row_test2 test2%rowtype; > BEGIN > =C2=A0 SELECT test1, chunk_id > =C2=A0 =C2=A0 =C2=A0 FROM test1 JOIN test2 ON(chunk.id =3D test2.chunk_id) > =C2=A0 =C2=A0 =C2=A0 LIMIT 1 > =C2=A0 =C2=A0 =C2=A0 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#PLPGS= QL-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@gmail.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