Example of trigger update with parameter

Can somebody give me a simple example of a trigger function that uses a parameter in Postgresql. In a table I have a column 'updated'. When a record is updated, the updated-value for that row should automatically get a new timestamp. For some reason the code below doesn't work. I guess because of the parameter.
CREATE TABLE testtable
(
  id serial NOT NULL,
  label text,
  created timestamp with time zone NOT NULL DEFAULT now(),
  updated timestamp with time zone NOT NULL DEFAULT now(),
  deleted boolean,
  description text,
  CONSTRAINT test1_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);
ALTER TABLE test OWNER TO postgres;
 
CREATE FUNCTION test_update() RETURNS trigger AS $$
begin
	update testtable set updated = now() where id=TG_ARGV[0];
end
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER test_update_trigger
AFTER UPDATE
ON testtable
FOR EACH ROW
EXECUTE PROCEDURE test_update(id);

Open in new window

pgadmin3-error.png
LVL 13
R7AFAsked:
Who is Participating?
 
earth man2Connect With a Mentor Commented:
~]$ 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)
0
 
tcs224694Commented:
update testtable set updated = now()+timezone where id=TG_ARGV[0];
0
 
earth man2Commented:
CREATE FUNCTION test_update() RETURNS trigger AS $$
begin
  update testtable set testtable.updated = now() where id = old.id;
  return new;
end;
$$ LANGUAGE plpgsql;
 
0
 
R7AFAuthor Commented:
The problem I have has to do with PG8.3, which doesn't allow implicit casting. I'm not sure if I'm passing the id correctly.

ERROR: invalid input syntax for integer: "id"
SQL status:22P02
Context:SQL statement "update testtable set updated = now() where id = CAST ( $1  as integer)"
PL/pgSQL function "test_update" line 2 at SQL statement
-- TRIGGER
 
CREATE TRIGGER test_update_trigger
  AFTER UPDATE
  ON testtable
  FOR EACH ROW
  EXECUTE PROCEDURE test_update('id');
 
-- TRIGGER FUNCTION
 
CREATE OR REPLACE FUNCTION test_update()
  RETURNS trigger AS
$BODY$
begin
	update testtable set updated = now() where id = CAST (TG_ARGV[0] as integer);
	return new;
end
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION test_update() OWNER TO postgres;

Open in new window

0
 
R7AFAuthor Commented:
Thank you! It took me some time to see that you changed the update function, but it works now and this is exactly what I need.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.