Re: setting default value by "trigger"

From: Barbara Lindsey <blindsey(at)cog(dot)ufl(dot)edu>
To: "Ed L(dot)" <pgsql(at)bluepolka(dot)net>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: setting default value by "trigger"
Date: 2004-02-09 17:57:48
Message-ID: 4027CA1C.9060502@cog.ufl.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Ahh! I didn't have the RETURN NEW; line in the function.
Thanks!

--------------------------------------------------------------------------------

Ed L. wrote:
> 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
>

--
Barbara E. Lindsey,
COG RDC
Phone: (352) 392-5198 ext. 314 Fax: (352) 392-8162

----
CONFIDENTIALITY NOTICE: The information contained in this electronic
message is legally privileged and confidential and intended only for the
use of the individual(s) or entity(ies) named above. If the reader of
this message is not the intended recipient, you are hereby notified that
any dissemination, distribution, or copying of this email or any of it's
components is strictly prohibited. If you have received this email in
error, please contact the sender.
----

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2004-02-09 18:04:37 Re: Increasing Max Connections Mac OS 10.3
Previous Message scott.marlowe 2004-02-09 17:56:12 Re: Quad Xeon vs. Dual Itanium