Re: setting default value by "trigger"

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