This works on 7.3.4:
CREATE TABLE foo (id SERIAL, starttime TIMESTAMP, endtime TIMESTAMP);
CREATE FUNCTION adjust_end_time() RETURNS "trigger" AS '
BEGIN
IF NEW.endtime ISNULL THEN
NEW.endtime := NEW.starttime;
END IF;
RETURN NEW;
END;'
LANGUAGE plpgsql;
CREATE TRIGGER foo_trigger
BEFORE INSERT ON foo
FOR EACH ROW
EXECUTE PROCEDURE adjust_end_time ();
INSERT INTO foo(starttime, endtime) VALUES (now(), now());
INSERT INTO foo(starttime) VALUES (now());
SELECT * FROM foo;
On Monday February 9 2004 9:24, Barbara Lindsey wrote:
> I have a case where I am collecting a "Start Date" and an "End Date".
> I would like to default the "End Date" to the "Start Date" value if only
> the "Start Date" is entered.
> I tried setting this as default on the table, but it was not permitted.
> So, now I am trying to figure out how to do it with a trigger or
> trigger/function combination.
>
> I tried doing it with a rule using "INSTEAD" on INSERT, but that gave an
> "endless recursion" error, which did make ssense after I looked at it
> more closely.
>
> I also tried making a trigger, which called a function that changed the
> value of the NEW.enddate to the NEW.startdate value if NEW.enddate is
> NULL, but I got an error saying that the NEW. values were not available
> yet (doing trigger BEFORE INSERT).
>
> Has anyone else done something like this, and if so, what approach
> worked for you?
> Thanks.
> Barb
