Re: Odd behavior of SELECT INTO in PL/pgSQL

Hello,

this is solved in new PostgreSQL 9.0

postgres=3D# create table test_table(id int);
CREATE TABLE
postgres=3D# CREATE OR REPLACE FUNCTION select_test()
postgres-#
postgres-#   RETURNS void AS
postgres-#
postgres-# $BODY$
postgres$#
postgres$# DECLARE
postgres$#
postgres$# id integer =3D -1;
postgres$#
postgres$# BEGIN
postgres$#
postgres$# select max(id) into id from test_table;
postgres$#
postgres$# END
postgres$#
postgres$# $BODY$
postgres-#
postgres-#   LANGUAGE 'plpgsql' VOLATILE
postgres-#
postgres-#   COST 100;
CREATE FUNCTION
postgres=3D# select select_test();
ERROR:  column reference "id" is ambiguous
LINE 1: select max(id)         from test_table
                   ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.
QUERY:  select max(id)         from test_table
CONTEXT:  PL/pgSQL function "select_test" line 8 at SQL statement
postgres=3D#

Regards
Pavel Stehule



2010/7/20  <depstein@alliedtesting.com>:
> PostgreSQL 8.4
>
>
>
> Here is a PL/pgSQL procedure:
>
>
>
> CREATE OR REPLACE FUNCTION select_test()
>
> =C2=A0 RETURNS void AS
>
> $BODY$
>
> DECLARE
>
> id integer =3D -1;
>
> BEGIN
>
> select max(id) into id from test_table;
>
> END
>
> $BODY$
>
> =C2=A0 LANGUAGE 'plpgsql' VOLATILE
>
> =C2=A0 COST 100;
>
>
>
> test_table is some table with an integer column =E2=80=98id=E2=80=99.
>
>
>
> The above procedure has an obvious mistake: the variable =E2=80=98id=E2=
=80=99 has the same
> name as a column in test_table. The select statement should have generated
> an error, preferably at function creation time.
>
>
>
> What actually happens is that no error is thrown, the select result is not
> assigned to the variable =E2=80=98id=E2=80=99, and the function always re=
turns -1.