Re: timestamp (MS SQLServer's rowversion) functionality

Tomski wrote:
> Hello!
> As many of you know, SQL Server (2000) has peculiar data type "timestamp"
> which is not SQL standard timestamp. In fact it is "rowversion" type. It
> makes tha field to be updated with current timestamp when row is updated or
> inserted.
> Is there any similiar functionality in PostgreSQL? If not, how to achieve
> that?
> I need such fields in many tables. Maybe triggers could help? Do I have to
> write functions for each trigger for each table? Or can it be done by one
> function with parameters? Partial or final solutions are welcome :)

Create your table with a column of type timestamp and DEFAULT (now())
and you have the on insert functionality. You need to use triggers to
get the on update fuctionality (and also for inserts if you don't trust
the application to leave it default). I think this is actually covered
by an example in the triggers documentation for postgres. If not then
there's certainly a full code solution in the archives of this list.
Please do some research before asking questions to the list.

Drew

On 10 Aug 2006 06:28:30 -0700, Andrew Hammond <
andrew(dot)george(dot)hammond(at)gmail(dot)com> wrote:
>
> Tomski wrote:
> > Hello!
> > As many of you know, SQL Server (2000) has peculiar data type
> "timestamp"
> > which is not SQL standard timestamp. In fact it is "rowversion" type. It
> > makes tha field to be updated with current timestamp when row is updated
> or
> > inserted.
> > Is there any similiar functionality in PostgreSQL? If not, how to
> achieve
> > that?
> > I need such fields in many tables. Maybe triggers could help? Do I have
> to
> > write functions for each trigger for each table? Or can it be done by
> one
> > function with parameters? Partial or final solutions are welcome :)
>
> Create your table with a column of type timestamp and DEFAULT (now())
> and you have the on insert functionality. You need to use triggers to
> get the on update fuctionality (and also for inserts if you don't trust
> the application to leave it default). I think this is actually covered
> by an example in the triggers documentation for postgres. If not then
> there's certainly a full code solution in the archives of this list.
> Please do some research before asking questions to the list.

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"();

==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================

On 8/11/06, Aaron Bono <postgresql(at)aranya(dot)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