[SQL] Re: Date of creation and of change

[Andreas Tille]

On Wed, 23 Aug 2000, hlefebvre wrote:

> Yes. The keywords NEW / OLD are available only in triggers
> see
> http://www.postgresql.org/users-lounge/docs/7.0/user/c40874113.htm#AEN4286
Well, I believe that, but


CREATE FUNCTION changed_at_timestamp() RETURNS OPAQUE AS '
    BEGIN
        ChangedAt := timestamp(''now'');
        RETURN NEW;
    END;
' LANGUAGE 'plpgsql';

CREATE TABLE WebSeite (
        IdWebSeite int4 DEFAULT nextval('seqwebseite'::text) NOT NULL,
        CreatedAt timestamp DEFAULT now(),
        changedat timestamp DEFAULT now(),
        ...
);

CREATE TABLE Menu (
        IdMenu int4 DEFAULT nextval('seqmenu'::text) NOT NULL,
        CreatedAt timestamp DEFAULT now(),
        ChangedAt timestamp DEFAULT now(),
        ...
);

CREATE TABLE MenuItem (
        IdMenu int4 DEFAULT nextval('seqmenu'::text) NOT NULL,
        CreatedAt timestamp DEFAULT now(),
        ChangedAt timestamp DEFAULT now(),
        ...
);

CREATE TRIGGER webseite_changed_at_timestamp BEFORE INSERT OR UPDATE ON WebSeite
    FOR EACH ROW EXECUTE PROCEDURE changed_at_timestamp();
CREATE TRIGGER menu_changed_at_timestamp BEFORE INSERT OR UPDATE ON Menu
    FOR EACH ROW EXECUTE PROCEDURE changed_at_timestamp();
CREATE TRIGGER menuitem_changed_at_timestamp BEFORE INSERT OR UPDATE ON MenuItem
    FOR EACH ROW EXECUTE PROCEDURE changed_at_timestamp();



web=# insert into menu (IdMenu, ...) values (3, ... );
ERROR:  parser: parse error at or near "changedat"


What's the problem here.  Is there a conflict between the definition with
DEFAULT now() and the TRIGGER BEFORE INSERT OR UPDATE.  Should perhaps
be the DEFAULT in the definition be removed or just the INSERT in
the TRIGGER?  Or is there a completely different problem?

Kind regards

         Andreas.