X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id D654B9FB285 for ; Fri, 11 Aug 2006 15:04:54 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 66142-09 for ; Fri, 11 Aug 2006 18:04:51 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.233]) by postgresql.org (Postfix) with ESMTP id 36DC29FB2EB for ; Fri, 11 Aug 2006 15:04:50 -0300 (ADT) Received: by wx-out-0506.google.com with SMTP id t13so765033wxc for ; Fri, 11 Aug 2006 11:04:50 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=QsoLxTIyZgKlk88/C3SlcPPHzfZvEEG/NZRtLUPJqZFCR9dLQpf1A1PJJPaaSbHtIYlUKUsrBy/Tu5wWR+UajJcrkWeiLM/Sh++oME60PLKFSBYunuHtU5mV5c37SQ84ATpYklJiFE+zyeppn8lgyUEvuuyE/8dx5qUx/JoYNmU= Received: by 10.78.117.10 with SMTP id p10mr2286806huc; Fri, 11 Aug 2006 11:04:47 -0700 (PDT) Received: by 10.78.32.10 with HTTP; Fri, 11 Aug 2006 11:04:47 -0700 (PDT) Message-ID: Date: Fri, 11 Aug 2006 13:04:47 -0500 From: "Aaron Bono" To: "Andrew Hammond" Subject: Re: timestamp (MS SQLServer's rowversion) functionality Cc: pgsql-sql@postgresql.org In-Reply-To: <1155216510.452118.282090@h48g2000cwc.googlegroups.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_78458_27940686.1155319487238" References: <1155216510.452118.282090@h48g2000cwc.googlegroups.com> X-Google-Sender-Auth: c357abaae9ad72bd X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.574 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, HTML_30_40, HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200608/88 X-Sequence-Number: 26046 ------=_Part_78458_27940686.1155319487238 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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.com http://codeelixir.com ================================================================== ------=_Part_78458_27940686.1155319487238 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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.com
   http://codeelixir.com
================================================================== ------=_Part_78458_27940686.1155319487238--