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 A05539FB393
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri,  4 Aug 2006 12:51:58 -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 56226-09 for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri,  4 Aug 2006 15:51:56 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-
Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.234])
	by postgresql.org (Postfix) with ESMTP id CAFDA9FB1DE
	for <pgsql-sql@postgresql.org>; Fri,  4 Aug 2006 12:51:55 -0300 (ADT)
Received: by wx-out-0506.google.com with SMTP id t13so116625wxc
	for <pgsql-sql@postgresql.org>; Fri, 04 Aug 2006 08:51:55 -0700 (PDT)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references;
	b=nDvSviANWo5f5l2+zmoJODKEyQj9tx5u8JZwPu+UJUZrHW5IgQjp3fhgG+569x7rwvFgz14ggNjLlkLmbfv0dRIwGeIN9DWJhed7J8BX9sBScKmH2L5eOASoAJ+kCfgSO62UQYcmNRwZOaRpXSyHkMiP1X+69S/MAkQWYsUvNy0=
Received: by 10.78.165.13 with SMTP id n13mr1608323hue;
	Fri, 04 Aug 2006 08:51:54 -0700 (PDT)
Received: by 10.78.56.16 with HTTP; Fri, 4 Aug 2006 08:51:54 -0700 (PDT)
Message-ID: <7c33d060608040851x525e7f82l5f058d259c011a8c@mail.gmail.com>
Date: Fri, 4 Aug 2006 22:51:54 +0700
From: "David Garamond" <davidgaramond@gmail.com>
To: "=?ISO-8859-1?Q?Rodrigo_De_Le=F3n?=" <rdeleonp@gmail.com>
Subject: Re: autoupdating mtime column
Cc: pgsql-sql@postgresql.org
In-Reply-To: <a55915760608040839w1a7caad9vd0aeb4e5e5271905@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_32740_20967616.1154706714363"
References: <7c33d060608040516n799af906sf74e1c645a47caff@mail.gmail.com>
	<44D34242.6000901@archonet.com>
	<7c33d060608040811s6dcaa3abia137188076a76781@mail.gmail.com>
	<a55915760608040839w1a7caad9vd0aeb4e5e5271905@mail.gmail.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.2 tagged_above=0 required=5
	tests=DNS_FROM_RFC_ABUSE, HTML_MESSAGE, SPF_PASS
X-Spam-Level: 
X-Archive-Number: 200608/37
X-Sequence-Number: 25995

------=_Part_32740_20967616.1154706714363
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

On 8/4/06, Rodrigo De Le=F3n <rdeleonp@gmail.com> wrote:
>
> How about:
>
> create or replace function
> update_times()
> returns trigger as $$
>         begin
>                 if TG_OP=3D'INSERT' then
>                         new.ctime =3D coalesce(new.ctime,now());
>                         new.mtime =3D coalesce(new.mtime,now());
>                 elsif TG_OP=3D'UPDATE' then
>                         new.ctime =3D old.ctime;
>                         new.mtime =3D 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 informatio=
n
given about which columns are specified in the SET clause, only the NEW and
OLD records.

--
dave

------=_Part_32740_20967616.1154706714363
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

On 8/4/06, <b class=3D"gmail_sendername">Rodrigo De Le=F3n</b> &lt;<a href=
=3D"mailto:rdeleonp@gmail.com">rdeleonp@gmail.com</a>&gt; wrote:<div><span =
class=3D"gmail_quote"></span><blockquote class=3D"gmail_quote" style=3D"bor=
der-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-=
left: 1ex;">
How about:<br><br>create or replace function<br>update_times()<br>returns t=
rigger as $$<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;begin<br>&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;if TG_OP=3D'INSERT' then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;new.ctime =3D coalesce(new.ctime,n=
ow());<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;
new.mtime =3D coalesce(new.mtime,now());<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;elsif TG_O=
P=3D'UPDATE' then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;new.ctime =3D old.ctime;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;new.mtime =3D now();<br>&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;end if;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;return new;
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;end;<br>$$ language plp=
gsql;<br></blockquote></div><br>But that would disallow setting mtime and c=
time to arbitrary values, which I want to permit. But ctime and mtime shoul=
d be set to current time if not specified in INSERT, and mtime to current t=
ime if not specified in UPDATE. I guess what I want is the MySQL's TIMESTAM=
P 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.
<br><br>--<br>dave<br>

------=_Part_32740_20967616.1154706714363--