From: David Garamond <davidgaramond@gmail.com>
To: Rodrigo De León <rdeleonp@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: autoupdating mtime column
Date: Fri, 4 Aug 2006 22:51:54 +0700
Message-ID: <7c33d060608040851x525e7f82l5f058d259c011a8c@mail.gmail.com> (raw)
In-Reply-To: <a55915760608040839w1a7caad9vd0aeb4e5e5271905@mail.gmail.com>
References: <7c33d060608040516n799af906sf74e1c645a47caff@mail.gmail.com>
<44D34242.6000901@archonet.com>
<7c33d060608040811s6dcaa3abia137188076a76781@mail.gmail.com>
<a55915760608040839w1a7caad9vd0aeb4e5e5271905@mail.gmail.com>
On 8/4/06, Rodrigo De León <rdeleonp@gmail.com> wrote:
>
> How about:
>
> create or replace function
> update_times()
> returns trigger as $$
> begin
> if TG_OP='INSERT' then
> new.ctime = coalesce(new.ctime,now());
> new.mtime = coalesce(new.mtime,now());
> elsif TG_OP='UPDATE' then
> new.ctime = old.ctime;
> new.mtime = now();
> end if;
> return new;
> end;
> $$ language plpgsql;
>
But that would disallow setting mtime and ctime to arbitrary values, which I
want to permit. But ctime and mtime should be set to current time if not
specified in INSERT, and mtime to current time if not specified in UPDATE. I
guess what I want is the MySQL's TIMESTAMP autoupdate behaviour, whichI
can't seem to be able to emulate in Postgres, because there's no information
given about which columns are specified in the SET clause, only the NEW and
OLD records.
--
dave
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: davidgaramond@gmail.com, rdeleonp@gmail.com
Subject: Re: autoupdating mtime column
In-Reply-To: <7c33d060608040851x525e7f82l5f058d259c011a8c@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