Re: Odd behavior of SELECT INTO in PL/pgSQL

Hello,

this is solved in new PostgreSQL 9.0

postgres=# create table test_table(id int);
CREATE TABLE
postgres=# CREATE OR REPLACE FUNCTION select_test()
postgres-#
postgres-# RETURNS void AS
postgres-#
postgres-# $BODY$
postgres$#
postgres$# DECLARE
postgres$#
postgres$# id integer = -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=# 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=#

Regards
Pavel Stehule

2010/7/20 <depstein(at)alliedtesting(dot)com>:
> PostgreSQL 8.4
>
>
>
> Here is a PL/pgSQL procedure:
>
>
>
> CREATE OR REPLACE FUNCTION select_test()
>
>   RETURNS void AS
>
> $BODY$
>
> DECLARE
>
> id integer = -1;
>
> BEGIN
>
> select max(id) into id from test_table;
>
> END
>
> $BODY$
>
>   LANGUAGE 'plpgsql' VOLATILE
>
>   COST 100;
>
>
>
> test_table is some table with an integer column ‘id’.
>
>
>
> The above procedure has an obvious mistake: the variable ‘id’ 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 ‘id’, and the function always returns -1.