upgrade from postgres 8.x to 9.x problem

[Ray <rui.vapps@xxxxxxxxx>]

Hi,

I have a table column name is called begin, tried to move it to
postgresql9 from postgresql8.4. the function failed to call
New.begin.

here is a quick test.

CREATE TABLE test(id serial primary key not null, begin timestamp
without time zone not null);
CREATE TABLE test2(id bigint not null, begin timestamp without time
zone not null);
create or replace function my_test() returns "trigger"
as $$
begin
    insert into test2 values (
        new.id,
        new.begin);
    return null;
end
$$
language plpgsql;
create trigger my_trigger after insert on test for each row execute
procedure my_test();

after create everything, run
INSERT INTO test values (default, now());

on postgresql 8.4
 INSERT INTO test values (default, now());
INSERT 0 1

on postgresql 9
INSERT INTO test values (default, now());
ERROR:  missing FROM-clause entry for table "new"
LINE 3:         new.begin)
                ^
QUERY:  insert into test2 values (
        new.id,
        new.begin)
CONTEXT:  PL/pgSQL function "my_test" line 2 at SQL statement


thanks for any help!

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general