By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,571 Members | 955 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,571 IT Pros & Developers. It's quick & easy.

setting default value by "trigger"

P: n/a
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.
----
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 22 '05 #1
Share this Question
Share on Google+
3 Replies

P: n/a
This sure looks a lot like what I already tried, but I will try it again...

d 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.
----
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 22 '05 #2

P: n/a
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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 22 '05 #3

P: n/a
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.
----
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 22 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.