Re: Updating column on row update

2009/11/22 Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>

> On Sun, Nov 22, 2009 at 12:50 PM, Thom Brown <thombrown(at)gmail(dot)com> wrote:
> > Hi,
> > This should be simple, but for some reason I'm not quite sure what the
> > solution is. I want to be able to update the value of a column for rows
> > that have been updated. More specifically, if a row is updated, I want
> it's
> > modified_date column to be populated with the current time stamp. I've
> > looked at triggers and rules, and it looks like I'd need to create a
> > function just to achieve this which seems incredibly clumsy and
> unnecessary.
> > Could someone enlighten me?
>
> Well, you DO have to create a function, but it's not all that clumsy
> really. Also it's quite flexible so you can do lots of complex stuff
> and hide it away in a trigger function.
>
> Example:
>
> -- FUNCTION --
>
> CREATE FUNCTION modtime () RETURNS opaque AS '
> BEGIN
> new.lm :=''now'';
> RETURN new;
> END;
> ' LANGUAGE 'plpgsql';
>
> -- TABLE --
>
> CREATE TABLE dtest (
> id int primary key,
> fluff text,
> lm timestamp without time zone
> );
>
>
> --TRIGGER --
>
> CREATE TRIGGER dtest
> BEFORE UPDATE or INSERT ON dtest FOR EACH ROW EXECUTE PROCEDURE
> modtime(lm);
>
> -- SQL TESTS --
>
> INSERT INTO dtest (id, fluff) VALUES (1,'this is a test');
> INSERT INTO dtest (id, fluff) VALUES (2,'this is another test');
> SELECT * FROM dtest;
> 1 | this is a test | 2003-04-02 10:33:12.577089
> 2 | this is another test | 2003-04-02 10:33:18.591148
> UPDATE dtest SET id=3 WHERE id=1;
> 3 | this is a test | 2003-04-02 10:34:52.219963 [1]
> UPDATE dtest SET fluff='now is the time' WHERE id=2;
> SELECT * FROM dtest WHERE id=2;
> 2 | now is the time | 2003-04-02 10:38:06.259443 [2]
> UPDATE dtest SET lm='2003-04-02 08:30:00' WHERE id=3;
> SELECT * FROM dtest WHERE id=3;
> 3 | this is a test | 2003-04-02 10:36:15.45687 [3]
>
> [1] The timestamp has changed for this record when we changed the id field.
> [2] The timestamp also changes for the fluff field.
> [3] We tried to set lm, but the trigger on that field in dtest
> intercepted the change and forced it
>

Thanks Scott. It's a shame a function has to be used because it then has
the dependency of plpgsql being loaded. I'm attempting to write a database
schema to accompany a PostgreSQL driver for a popular CMS, but I guess I
could get it to load plpgsql in as a language.

The problem now is if the the schema creation script is run against a
database where the language is already installed, I would get an error
saying it already exists. Is there a way to get it to check for it first,
and only create it if it isn't exist? Bear in mind I'd want this to be
compatible at least as far back as 8.1.

Thanks

Thom