Re: upgrade from postgres 8.x to 9.x problem

On Nov 17, 8:42 pm, Ray <rui.va...@gmail.com> wrote:
> 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!

figured out. the begin is keyword and need to double quoted.