From: Aaron Bono <postgresql@aranya.com>
To: Andrew Hammond <andrew.george.hammond@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: timestamp (MS SQLServer's rowversion) functionality
Date: Fri, 11 Aug 2006 13:04:47 -0500
Message-ID: <bf05e51c0608111104r6da9323cu544355efdc70faff@mail.gmail.com> (raw)
In-Reply-To: <1155216510.452118.282090@h48g2000cwc.googlegroups.com>
References: <ebcj9j$239r$1@news.hub.org>
<1155216510.452118.282090@h48g2000cwc.googlegroups.com>
On 10 Aug 2006 06:28:30 -0700, Andrew Hammond <
andrew.george.hammond@gmail.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.comhttp://codeelixir.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: postgresql@aranya.com, andrew.george.hammond@gmail.com
Subject: Re: timestamp (MS SQLServer's rowversion) functionality
In-Reply-To: <bf05e51c0608111104r6da9323cu544355efdc70faff@mail.gmail.com>
* 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