pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Christophe Labouisse <labouiss@cybercable.fr>
To: pgsql-sql@postgreSQL.org, Tulassay Zsolt <zsolt@tek.bke.hu>
Subject: Re: [SQL] auto datetime update on UPDATE?
Date: 31 Oct 1999 20:52:38 +0100
Message-ID: <m2n1szz4s9.fsf@localhost.localdomain> (raw)
References: <Pine.LNX.4.10.9910311654240.26318-100000@tek.bke.hu>

Tulassay Zsolt <zsolt@tek.bke.hu> writes:

> Hi,
> 
> i have set up a table, where two fields (creation_dt and modification_dt)
> always get the actual date/time by default when doing an INSERT.
> my question is, how is it possible to let the field modification_dt
> change when doing an UPDATE?
> Is there a way to solve it in SQL?

To do this I use a trigger in plpgsql :

create function liens_maj_fun () returns opaque as '
begin
        new.liens_maj := ''now'';
        return new;
end;
' language 'plpgsql';

create trigger liens_maj_trig before update or insert
    on liens for each row execute procedure liens_maj_fun();


Since it uses plpgsql don't forget to add it when you create your
base:


CREATE FUNCTION plpgsql_call_handler()
   RETURNS opaque
   AS '/usr/local/pgsql/lib/plpgsql.so'
   LANGUAGE 'c';

CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
   HANDLER plpgsql_call_handler
   LANCOMPILER 'PL/pgSQL';


-- 
christophe.labouisse@dial.oleane.com
Le cinéma en Lumière : http://www.lumiere.org/
Vote en cours pour la création du forum fr.rec.arts.sf.starwars
pour en savoir plus : <news:38146a03.373985713@news.mp-poissy.ac-versailles.fr>



view thread (4+ messages)  latest in thread

Message-ID: <m2n1szz4s9.fsf@localhost.localdomain>
Permalink:  ../m2n1szz4s9.fsf@localhost.localdomain/
Also on:    postgresql.org/message-id/m2n1szz4s9.fsf@localhost.localdomain

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: labouiss@cybercable.fr, zsolt@tek.bke.hu
  Subject: Re: [SQL] auto datetime update on UPDATE?
  In-Reply-To: <m2n1szz4s9.fsf@localhost.localdomain>
  References: <Pine.LNX.4.10.9910311654240.26318-100000@tek.bke.hu> <m2n1szz4s9.fsf@localhost.localdomain>

* 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