On Sun, Nov 22, 2009 at 1:32 PM, Thom Brown <thombrown@xxxxxxxxx> wrote: > 2009/11/22 Scott Marlowe <scott.marlowe@xxxxxxxxx> >> >> On Sun, Nov 22, 2009 at 12:50 PM, Thom Brown <thombrown@xxxxxxxxx> 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. Try this: select * from pg_language ; Pretty sure that exists pretty far back. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
