CURRENT_DATE in postgresql triggers

Question

I have before insert trigger in postgres

CREATE OR REPLACE FUNCTION add_requestdate() RETURNS TRIGGER AS $$
DECLARE

BEGIN

    new.requestdate := now();

    RETURN NEW;
END;

$$ LANGUAGE plpgsql;

CREATE TRIGGER addrequestdate
BEFORE INSERT ON requests FOR EACH ROW EXECUTE PROCEDURE add_requestdate ();

But it doesn't work! I have solution that works...

CREATE OR REPLACE FUNCTION add_requestdate() RETURNS TRIGGER AS $$
DECLARE

BEGIN

    UPDATE requests SET requestdate=now() WHERE id=NEW.id;

    RETURN NEW;
END;

$$ LANGUAGE plpgsql;

CREATE TRIGGER addrequestdate
BEFORE INSERT ON requests FOR EACH ROW EXECUTE PROCEDURE add_requestdate ();

I have only one question. Why new.requestdate := now(); doesn't work?

What error gives? In my version of PostgreSQL 9.0, the first example works without problem. You can put the table definition, and the SQL command that you run? And your version of PostgreSQL is? — user731136
– user731136, Commented Jan 7, 2012 at 17:35
Please define "doesn't work!". The second trigger can only have an effect if requests.id is not unique - on other rows with the same id. Is id unique? Are there additional other triggers involved? — Erwin Brandstetter
– Erwin Brandstetter, Commented Jan 8, 2012 at 2:32

Pavel Stehule · Accepted Answer · 2012-01-07 17:42:44Z

it working - probably you have some other dependency - it strange, your workable solution cannot to work, because before trigger doesn't see a tuple in table, and if you use after trigger, then you start recursion..

postgres=# create table x(a int, b date, c timestamp);
CREATE TABLE
postgres=# create function fxx()
postgres-# returns trigger as $$
postgres$# begin
postgres$#   new.b := now();
postgres$#   new.c := now();
postgres$#   return new;
postgres$# end;
postgres$# $$ language plpgsql;
CREATE FUNCTION
postgres=# create trigger xxxx before insert on x for each row execute procedure fxx();  
CREATE TRIGGER
postgres=# insert into x(a) values(10);
INSERT 0 1
postgres=# insert into x(a) values(209);
INSERT 0 1
postgres=# select * from x;
  a  │     b      │             c              
─────┼────────────┼────────────────────────────
  10 │ 2012-01-07 │ 2012-01-07 18:36:57.665283
 209 │ 2012-01-07 │ 2012-01-07 18:37:00.853442
(2 rows)

so, you can use CURRENT_DATE instead now(), it is preferable solution.

Collectives™ on Stack Overflow

CURRENT_DATE in postgresql triggers

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related