Re: Updating column on row update

2009/11/22 Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>

> On 23/11/2009 4:15 AM, Scott Marlowe wrote:
> > 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.
>
> I do think this comes up often enough that a built-in trigger "update
> named column with result of expression on insert" trigger might be
> desirable. Especially if implemented in C to avoid the need for PL/PgSQL
> and to reduce the CPU cost a smidge.
>
> Hmm. CC'iing -hackers; there was a discussion earlier on it being
> desirable to have more "[EASY]" TODO items, and this might be a good one
> for the job.
>
> So might "CREATE LANGUAGE ... IF NOT EXISTS". Maybe even "CREATE ROLE
> ... IF NOT EXISTS" and "CREATE USER ... IF NOT EXISTS" - I know I'd find
> them really handy.
>
> --
> Craig Ringer
>

I would have thought the IF NOT EXISTS syntax could be handy on every CREATE
command and I wouldn't object to such a thing being implemented in future.

But my reason for the column updating on row update was due to me converting
a MySQL script to PostgreSQL. MySQL had the following syntax available:

`updated_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP

That's effectively what I'm emulating. I think that syntax is actually
quite useful. Another thing I found useful was having COMMENT available on
the same line as the column declaration too. An example of this is:

`parent_id` integer unsigned NOT NULL default '0' COMMENT 'The parent menu
item in the menu tree.',

I really can't think of any other syntactic sugar I'd want from MySQL
though.

Thom