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.

On 2010-11-18 03.57, Ray wrote:

On Nov 17, 8:42 pm, Ray<rui.va...@gmail.com> wrote:

-- snip ---

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

This is one strong reason why you should avoid using keywords as object
names. Better to fix that early in the design since names has a tendency
to stick with you a long time.

--
Regards,
Robert "roppert" Gravsjö