trigger:
CREATE TRIGGER "tr_update_ts" BEFORE INSERT OR UPDATE
ON "public"."test" FOR EACH ROW
EXECUTE PROCEDURE "public"."update_ts"();
and the function is:
CREATE OR REPLACE FUNCTION "public"."update_ts" () RETURNS trigger AS
$body$
DECLARE
BEGIN
NEW.ts := now();
RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
Why is this not working? No error thrown, but the ts is still null...
I tested your code and it works:
First, let's create table:
# create table test (x int4, ts timestamptz);
CREATE TABLE
Now, Let's add function:
# CREATE OR REPLACE FUNCTION "public"."update_ts" () RETURNS trigger AS
>> $body$
>> DECLARE
>> BEGIN
>> NEW.ts := now();
>> RETURN NEW;
>> END;
>> $body$
>> LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
CREATE FUNCTION
And finally, add a trigger:
# CREATE TRIGGER "tr_update_ts" BEFORE INSERT OR UPDATE
>> ON "public"."test" FOR EACH ROW
>> EXECUTE PROCEDURE "public"."update_ts"();
CREATE TRIGGER
So, now, let's test ON INSERT part of it:
# insert into test (x) values (1);
INSERT 0 1
# select * from test;
x | ts
---+-------------------------------
1 | 2009-09-12 19:54:50.812139+02
(1 row)
Clearly it works.
Now, the update:
# update test set x = 2;
UPDATE 1
# select * from test;
x | ts
---+-------------------------------
2 | 2009-09-12 19:54:57.463933+02
(1 row)
ts has been changed. Clearly the code you shown works, so the error must be someplace else.
Show us "\d test" output from psql, and \df+ of the trigger function.
What is the full syntax of your create function? Here is the full syntax of the function that I created, and it is working as expected.
create function update_timestamp() RETURNS trigger AS $$
BEGIN
NEW.ts := now();
RETURN NEW;
END;
$$ language plpgsql;
