note BEFORE UPDATE and use of
new.updated := now()
and
return new;
~]$ psql treacle;
Welcome to psql 8.2.4, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
treacle=> CREATE TABLE testtable
treacle-> (
treacle(> id serial NOT NULL,
treacle(> label text,
treacle(> created timestamp with time zone DEFAULT now(),
treacle(> updated timestamp with time zone DEFAULT now(),
treacle(> deleted boolean,
treacle(> description text
treacle(> )
treacle-> WITH (OIDS=FALSE);
NOTICE: CREATE TABLE will create implicit sequence "testtable_id_seq" for serial column "testtable.id"
CREATE TABLE
treacle=> CREATE FUNCTION test_update() RETURNS trigger AS $$
treacle$> begin
treacle$> new.updated := now();
treacle$> return new;
treacle$> end
treacle$> $$ LANGUAGE plpgsql;
CREATE FUNCTION
treacle=> CREATE TRIGGER test_update_trigger
treacle-> BEFORE UPDATE
treacle-> ON testtable
treacle-> FOR EACH ROW
treacle-> EXECUTE PROCEDURE test_update();
CREATE TRIGGER
treacle=> insert into testtable values ( default, 'First', default, default, false, ' Record' );
INSERT 0 1
treacle=> select * from testtable;
id | label | created | updated | deleted | description
----+-------+-------------
1 | First | 2009-04-24 07:04:06.0572+01 | 2009-04-24 07:04:06.0572+01 | f | Record
(1 row)
treacle=> update testtable set description = ' Hello World second time!';
UPDATE 1
treacle=> select * from testtable;
id | label | created | updated | deleted | description
----+-------+-------------
1 | First | 2009-04-24 07:04:06.0572+01 | 2009-04-24 07:07:08.139084+01 | f | Hello World second time!
(1 row)
Main Topics
Browse All Topics
by: cminearPosted on 2009-04-23 at 08:46:35ID: 24216458
If you want to access the 'id' column from the row, you can use either OLD.id or NEW.id inside the function test_update(). (If the update has not changed the value of the 'id' column, these will be the same; if the update has changed this column, then it is up to you to decide which value you want to use.) See docs on Trigger Procedures here: http://www.postgresql.org/
If you want the row id relative to the set of rows which have set off the trigger (that is, 5 records in a table of 1000 are updated, and you want 1, 2, 3, 4, or 5 available to each execution of test_update), my initial response is that it is not possible, or at least PostgreSQL does not make that information available to the trigger function natively (unless they haven't documented the feature). And every thought for a work-around solution will likely not hold up in practice (mainly fail with concurrent triggers).
So I hope the first suggestion is what you were looking for.