Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 8288
  • Last Modified:

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
0
R7AF
Asked:
R7AF
  • 2
  • 2
1 Solution
 
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
 
earth man2Commented:
~]$ 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
 
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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