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 <depst...@alliedtesting.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.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
