• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 789
  • Last Modified:

Triggers: how to use id with FOR EACH ROW

Take the trigger below. In PostgreSQL 8.3, how can I use the id of the row that the procedure is executed on in the function test_update()?
CREATE TRIGGER test_update_trigger
  ON testtable
  EXECUTE PROCEDURE test_update();

Open in new window

1 Solution
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/docs/8.3/interactive/plpgsql-trigger.html

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.
earth man2Commented:
note BEFORE UPDATE and use of
  new.updated := now()
  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"
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!';
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)
R7AFAuthor Commented:
I thought I needed two questions for this problem, but well...
For reference: http://www.experts-exchange.com/Q_24345532.html 

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now