CODEWITHSUNDEEP
postgresqltriggers
Alexander Kleinhans
Alexander Kleinhans

Reputation: 6248

Postgres triggers and producers (column "new" of relation does not exist)

I am trying to create a trigger and procedure to update a last_changed_timestamp column upon UPDATE and INSERT.

I can register the function and trigger just fine, but when I try to update a record I receive the error.

CREATE OR REPLACE FUNCTION update_my_table_last_changed_timestamp()
        RETURNS trigger AS
$BODY$
BEGIN
        UPDATE my_table SET NEW.last_changed_timestamp = NOW();  
        RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;


CREATE TRIGGER trigger_update_my_table_last_changed_timestamp 
  BEFORE UPDATE
  ON my_table
  FOR EACH ROW
  EXECUTE PROCEDURE update_my_table_last_changed_timestamp();

column "new" of relation "my_table" does not exist

I also do not fully understand how update_my_table_last_changed_timestamp knows which row it's suppose to update, nor if there were parameters passed to it, how the I would get those variables from the trigger to the procedure.

Upvotes: 1

Views: 612

Answers (2)

hoi ja
hoi ja

Reputation: 267

If you still want to access a (other )table in the update trigger.

You can add to beginning of your trigger body the following:

EXECUTE format('SET search_path TO %I', TG_TABLE_SCHEMA);

For some reason with the update trigger it can happen that you're not on the correct search_path (i believe some old psql version have this)

Upvotes: 0

klin
klin

Reputation: 121654

Modify the NEW record, there is no need to update.

BEGIN
    NEW.last_changed_timestamp = NOW();  
    RETURN NEW;
END;

Read in the documentation: Overview of Trigger Behavior

Upvotes: 2

Related Questions