From: Jan Wieck <janwieck@Yahoo.com>
To: Dave Wedwick <news@wedwick.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Trigger question
Date: Wed, 14 Feb 2001 14:51:40 -0500 (EST)
Message-ID: <200102141951.OAA04373@jupiter.jw.home> (raw)
In-Reply-To: <3A8754BB.382CBDBE@wedwick.com>
Dave Wedwick wrote:
> Hi!
>
> I have a table with an int4 field called inserttime. Regardless of what
> the user enters in this field, I want a trigger to put now() into it.
>
> What's the syntax for the trigger?
Sample:
CREATE TABLE t1 (
id serial PRIMARY KEY,
inserttime integer,
description text
);
CREATE FUNCTION t1_before_insert () RETURNS opaque AS '
BEGIN
NEW.inserttime := date_part(''epoch'', now());
RETURN NEW;
END;'
LANGUAGE 'plpgsql';
CREATE TRIGGER t1_before_insert BEFORE INSERT ON t1
FOR EACH ROW EXECUTE PROCEDURE t1_before_insert();
CREATE FUNCTION t1_before_update () RETURNS opaque AS '
BEGIN
NEW.inserttime := OLD.inserttime;
RETURN NEW;
END;'
LANGUAGE 'plpgsql';
CREATE TRIGGER t1_before_update BEFORE UPDATE ON t1
FOR EACH ROW EXECUTE PROCEDURE t1_before_update();
These two triggers ensure that the field 'inserttime' is set
to the number of seconds since Jan. 1st 1970 on INSERT and
will never be changed after. Close enough to what you want?
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: janwieck@Yahoo.com, news@wedwick.com
Subject: Re: Trigger question
In-Reply-To: <200102141951.OAA04373@jupiter.jw.home>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox