0

I would like to automatically timestamp any inserted or updated rows in an postgre v. 9.3 data table. I tried following the code here: Postgresql Current timestamp on Update to the letter, but it is not working for me:

db=> CREATE OR REPLACE FUNCTION upd_timestamp() RETURNS TRIGGER
LANGUAGE plpgsql
AS
$$
BEGIN
NEW.lastmodified = CURRENT_TIMESTAMP;
RETURN NEW;
END;
$$;
CREATE FUNCTION

db=> CREATE TRIGGER dealscheduleT
AFTER UPDATE or INSERT ON dealschedule FOR EACH STATEMENT EXECUTE PROCEDURE upd_timestamp();
CREATE TRIGGER

db=> insert into dealschedule (deals_id) values (55);
ERROR:  record "new" is not assigned yet
DETAIL:  The tuple structure of a not-yet-assigned record is indeterminate.
CONTEXT:  PL/pgSQL function upd_timestamp() line 3 at assignment

Here is what I currently have in the table dealschedule:

 deals_id |     starttimes      | interested |      endtimes       | lastmodified
----------+---------------------+------------+---------------------+--------------
      227 | {08:00:00,13:12:00} | {426,427}  | {13:00:00,16:00:00} |
(1 row)

And here is the full description of the table:

              Table "public.dealschedule"
    Column    |            Type             | Modifiers
--------------+-----------------------------+-----------
 deals_id     | integer                     |
 starttimes   | time without time zone[]    |
 interested   | integer[]                   |
 endtimes     | time without time zone[]    |
 lastmodified | timestamp without time zone |
Indexes:
    "dealschedule_deals_id_key" UNIQUE CONSTRAINT, btree (deals_id)
Triggers:
    dealschedulet AFTER INSERT OR UPDATE ON dealschedule FOR EACH STATEMENT EXECUTE PROCEDURE upd_timestamp()

What am I doing wrong? What does the error mean?

Community
  • 1
  • 1
sunny
  • 3,853
  • 5
  • 32
  • 62
  • 2
    Statement level triggers don't have a `new` or `old` record. You want a row level trigger: http://www.postgresql.org/docs/current/static/plpgsql-trigger.html#PLPGSQL-DML-TRIGGER –  Mar 17 '15 at 21:42
  • @a_horse_with_no_name thank you for the link. I made the change as you suggested. Now I do not get an error when I execute an insert or update on this table, but the lastmodified column remains empty rather than timestamped. It seems that my trigger is not triggering or my function is not executing...any advice? – sunny Mar 17 '15 at 21:50
  • 2
    You also want an **before** trigger, you can't change a value in an **after** trigger (this is also explained in the manual) –  Mar 17 '15 at 22:04

0 Answers0