2009/11/22 Craig Ringer <cr...@postnewspapers.com.au> > On 23/11/2009 4:15 AM, Scott Marlowe wrote: > > On Sun, Nov 22, 2009 at 12:50 PM, Thom Brown <thombr...@gmail.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
