Calling now() in a function

One of our Postgre开发者_JS百科s tables, called rep_event, has a timestamp column that indicates when each row was inserted. But all of the rows have a timestamp value of 2000-01-01 00:00:00, so something isn't set up right.

There is a function that inserts rows into the table, and it is the only code that inserts rows into that table - no other code inserts into that table. (There also isn't any code that updates the rows in that table.) Here is the definition of the function:

CREATE FUNCTION handle_event() RETURNS "trigger"
AS $$
BEGIN
    IF (TG_OP = 'DELETE') THEN
        INSERT INTO rep_event SELECT 'D', TG_RELNAME, OLD.object_id, now();
        RETURN OLD;
    ELSIF (TG_OP = 'UPDATE') THEN
        INSERT INTO rep_event SELECT 'U', TG_RELNAME, NEW.object_id, now();
        RETURN NEW;
    ELSIF (TG_OP = 'INSERT') THEN
        INSERT INTO rep_event SELECT 'I', TG_RELNAME, NEW.object_id, now();
        RETURN NEW;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

Here is the table definition:

CREATE TABLE rep_event
(
operation character(1) NOT NULL,
table_name text NOT NULL,
object_id bigint NOT NULL,
time_stamp timestamp without time zone NOT NULL
)

As you can see, the now() function is called to get the current time. Doing a "select now()" on the database returns the correct time, so is there an issue with calling now() from within a function?

A simpler solution is to just modify your table definition to have NOW() be the default value:

CREATE TABLE rep_event (
    operation character(1) NOT NULL,
    table_name text NOT NULL,
    object_id bigint NOT NULL,
    time_stamp timestamp without time zone NOT NULL DEFAULT NOW()
);

Then you can get rid of the now() calls in your trigger.

Also as a side note, I strongly suggest including the column ordering in your function... IOW;

INSERT INTO rep_event (operation,table_name,object_id,time_stamp) SELECT ...

This way if you ever add a new column or make other table changes that change the internal ordering of the tables, your function won't suddenly break.

Your problem has to be elsewhere, as your function works well. Create test database, paste the code you cited and run:

create table events (object_id bigserial, data text);
create trigger rep_event
  before insert or update or delete on events
  for each row execute procedure handle_event();

insert into events (data) values ('v1'),('v2'),('v3');
delete from events where data='v2';
update events set data='v4' where data='v3';

select * from events;

     object_id | data 
    -----------+------
             1 | v1
             3 | v4

select * from rep_event;

     operation | table_name | object_id |         time_stamp         
    -----------+------------+-----------+----------------------------
     I         | events     |         1 | 2011-07-08 10:31:50.489947
     I         | events     |         2 | 2011-07-08 10:31:50.489947
     I         | events     |         3 | 2011-07-08 10:31:50.489947
     D         | events     |         2 | 2011-07-08 10:32:12.65699
     U         | events     |         3 | 2011-07-08 10:32:33.662936
    (5 rows)

Check other triggers, trigger creation command etc. And change this timestamp without timezone to timestamp with timezone.