From: | Sven Willenberger <sven(at)dmv(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: handing created and updated fields |
Date: | 2005-01-10 16:16:03 |
Message-ID: | 1105373764.28628.13.camel@lanshark.dmv.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Mon, 2005-01-10 at 15:45 +0100, Daniel Martini wrote:
> Hi,
>
> Citing "Jim C. Nasby" <decibel(at)decibel(dot)org>:
> > ON INSERT: force created and updated to be current_timestamp
> > ON UPDATE: deny updated created. force updated to be set to
> > current_timestamp
> [snip]
> > Does anyone have an example of the best way to handle this scenario?
>
> Something along the lines of the following should work (but test first
> anyways, though I have copied smaller parts of this from the definitions
> in one of my databases here, I have made modifications to fit your
> specific task, so typos/errors might have sneaked in):
>
> create function update_trigger() returns trigger as
> 'begin
> new.created := old.created;
> new.updated := CURRENT_TIMESTAMP;
> return new;
> end;'
> language 'plpgsql';
>
> create trigger update_trigger BEFORE UPDATE ON your_table_name
> FOR EACH ROW EXECUTE PROCEDURE update_trigger();
>
> create function insert_trigger() returns trigger as
> 'begin
> new.created := CURRENT_TIMESTAMP;
> new.updated := CURRENT_TIMESTAMP;
> return new;
> end;'
> language 'plpgsql';
>
> create trigger insert_trigger BEFORE INSERT ON your_table_name
> FOR EACH ROW EXECUTE PROCEDURE insert_trigger();
>
> HTH,
> Regards,
> Daniel
These could also be combined into one trigger since they are nearly
identical anyway:
CREATE FUNCTION combined_trigger() RETURNS TRIGGER AS '
BEGIN
NEW.update := CURRENT_TIMESTAMP;
IF TG_OP = ''INSERT'' THEN
NEW.created := CURRENT_TIMESTAMP;
ELSE
NEW.created := OLD.created;
END IF;
RETURN NEW;
END;
' LANGUAGE plpgsql;
CREATE TRIGGER combined_trigger BEFORE INSERT OR UPDATE on
your_table_name FOR EACH ROW EXECUTE PROCEDURE combined_trigger();
Sven
From | Date | Subject | |
---|---|---|---|
Next Message | Brendan Jurd | 2005-01-10 16:28:08 | Function for retreiving datatype |
Previous Message | lol | 2005-01-10 16:03:31 | PostgreSQL 8 on windows very slow |