DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=YtcIwkflea2eZ5lt7u5L8XQdSgl47fil2uGtFvnBdtMk8Kqhc8dV8maQ1JGYRC6InP/EjU0c6T3wEs/fIWaIIJE7GZedHVT4akmJQKWColbtdUdUmVB98iwxgp+391eX846idmeZEcwvaj4cuVGI/r+Q3lZx2aYB8LulTevFHkg=
Message-ID: <5a0a9d6f0608111140u1034ffabgf25b94922c7fd601@mail.gmail.com>
Date: Fri, 11 Aug 2006 14:40:58 -0400
From: "Andrew Hammond" <andrew.george.hammond@gmail.com>
To: "Aaron Bono" <postgresql@aranya.com>, pgsql-sql@postgresql.org
Subject: Re: timestamp (MS SQLServer's rowversion) functionality
In-Reply-To: <bf05e51c0608111104r6da9323cu544355efdc70faff@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <ebcj9j$239r$1@news.hub.org>
	<1155216510.452118.282090@h48g2000cwc.googlegroups.com>
	<bf05e51c0608111104r6da9323cu544355efdc70faff@mail.gmail.com>

On 8/11/06, Aaron Bono <postgresql@aranya.com> wrote:

> I put a create_dt and modify_dt column on every table and set the default to
> now().  Then I use this trigger:
>
> CREATE OR REPLACE FUNCTION "public"."modify_date_stamp_fn" () RETURNS SETOF
> opaque AS
> '
> BEGIN
>     -- if a trigger insert or update operation occurs
>     IF TG_OP = ''INSERT'' OR TG_OP = ''UPDATE'' THEN
>         -- assigns the current timestamp
>         -- into the mod_time column
>         NEW.modify_dt := now();
>
>         -- displays the new row on an insert/update
>         RETURN NEW;
>     END IF;
> END;
> '
> LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
>
> CREATE TRIGGER "mytable_modify_dt_tr" BEFORE UPDATE
>     ON "public"."mytable" FOR EACH ROW
>     EXECUTE PROCEDURE "public"."modify_date_stamp_fn"();


That's pretty close. Couple of things though.
0) Dollar quoting is readability++ so you might as well get in the habit.
1) Your trigger function should properly return trigger rather than
SETOF opaque (but that's a pretty cute hack, I gotta admit).
2) While you're at it, you probably want to enforce the immutability
of create_dt on updates. This requires an AFTER trigger.
3) If you're not going to call the function from anything but the
insert/update, there's no reason to check if it's and insert or update
(unless you want to be paranoid). You're not currently calling it for
inserts, but we can change that.
4) This function is properly a security a definer. Not a big deal
until (and if) someone decides to implement column level privs.
5) See  http://www.postgresql.org/docs/current/static/plpgsql-trigger.html
for further documentation.

CREATE OR REPLACE FUNCTION "public"."modify_date_stamp_fn" () RETURNS
trigger AS $modify_date_stamp$
BEGIN
    IF TG_OP = ''INSERT'' THEN NEW.create_dt := now();
    ELSE
        IF NEW.create_dt <> OLD.create_dt THEN
            RAISE EXCEPTION 'Not allowed to change create_dt. Bad
programmer!!!';
        END IF;  -- no changes allowed
    END IF;
    NEW.modify_dt := now();          -- always stamp updates
    RETURN NEW;
END;
$modify_date_stamp$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY DEFINER;

CREATE TRIGGER "mytable_modify_dt_tr" AFTER INSERT OR UPDATE
    ON "public"."mytable" FOR EACH ROW
    EXECUTE PROCEDURE "public"."modify_date_stamp_fn"();

Drew