postgresql - on update trigger

Question

I'm trying to do on update trigger, which will be automatically updating current_timestamp only when column updated_date is not openly updated.

function:

CREATE OR REPLACE FUNCTION dba.update_stamp_pc() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    RAISE NOTICE 'NEW.updated_date: %', NEW.updated_date;
    RAISE NOTICE 'OLD.updated_date: %', OLD.updated_date;
    IF NEW.updated_date=OLD.updated_date OR NEW.updated_date is null then
        NEW.updated_date := current_timestamp;
    END IF;
    RETURN NEW;
END;
$$;

Checks:

aaa=> insert into dba.pc_test1 values(7);
INSERT 0 1
aaa=> select order_id,created_date,updated_date from dba.pc_test1 where order_id=7;
 order_id |         created_date          | updated_date 
----------+-------------------------------+--------------
    7 | 2022-02-23 10:01:23.142326+00 | 
(1 row)
aaa=> update dba.pc_test1 set order_id=7 where order_id=7;
NOTICE:  NEW.updated_date: <NULL>
NOTICE:  OLD.updated_date: <NULL>
UPDATE 1
aaa=> select order_id,created_date,updated_date from dba.pc_test1 where order_id=7;
 order_id |         created_date          |        updated_date         
----------+-------------------------------+-----------------------------
    7 | 2022-02-23 10:01:23.142326+00 | 2022-02-23 10:02:04.5021+00
(1 row)
aaa=> \d dba.pc_test1
                            Table "dba.pc_test1"
Column    |            Type             | Collation | Nullable |         Default         
--------------+-----------------------------+-----------+----------+-------------------------
 order_id     | bigint                      |           | not null | 
 created_date | timestamp(6) with time zone |           | not null | 
 created_by   | character varying(50)       |           | not null | 
 updated_date | timestamp(6) with time zone |           |          | 
 updated_by   | character varying(50)       |           |          | NULL::character varying
Triggers:
    insert_pc_test1 BEFORE INSERT ON dba.pc_test1 FOR EACH ROW EXECUTE FUNCTION dba.insert_stamp_pc()
    update_pc_test1 BEFORE UPDATE ON dba.pc_test1 FOR EACH ROW EXECUTE FUNCTION dba.update_stamp_pc()

aaa=> update dba.pc_test1 set order_id=7 where order_id=7;
NOTICE:  NEW.updated_date: 2022-02-23 10:02:04.5021+00
NOTICE:  OLD.updated_date: 2022-02-23 10:02:04.5021+00
UPDATE 1
aaa=> select order_id,created_date,updated_date from dba.pc_test1 where order_id=7;
 order_id |         created_date          |         updated_date          
----------+-------------------------------+-------------------------------
   7 | 2022-02-23 10:01:23.142326+00 | 2022-02-23 10:03:03.571949+00
(1 row)
aaa=> update dba.pc_test1 set order_id=7,updated_date='2000-01-01' where order_id=7;
NOTICE:  NEW.updated_date: 2000-01-01 00:00:00+00
NOTICE:  OLD.updated_date: 2022-02-23 10:03:03.571949+00
UPDATE 1
aaa=> select order_id,created_date,updated_date from dba.pc_test1 where order_id=7;
 order_id |         created_date          |      updated_date      
----------+-------------------------------+------------------------
7 | 2022-02-23 10:01:23.142326+00 | 2000-01-01 00:00:00+00
(1 row)
aaa=> update dba.pc_test1 set order_id=7,updated_date='2000-01-01' where order_id=7;
NOTICE:  NEW.updated_date: 2000-01-01 00:00:00+00
NOTICE:  OLD.updated_date: 2000-01-01 00:00:00+00
UPDATE 1
aaa=> select order_id,created_date,updated_date from dba.pc_test1 where order_id=7;
 order_id |         created_date          |         updated_date          
----------+-------------------------------+-------------------------------
   7 | 2022-02-23 10:01:23.142326+00 | 2022-02-23 10:03:45.473659+00
(1 row)
aaa=> 

In the last case, updated_date should have value '2000-01-01 00:00:00+00', but because inside the database such value already exists then the current_timestamp is inserted. How to fix it?

Answer 1

I created a sample trigger function and tested it, 100% working. Example:

CREATE OR REPLACE FUNCTION update_test_table() 
returns trigger
language plpgsql
AS $$
begin
    
    if new.action_date = old.action_date or new.action_date is null then
        new.action_date := current_timestamp;
    end if;
   
    return new;
end;
$$;


CREATE TRIGGER trigger_update_test_table
BEFORE UPDATE
ON test_table
FOR EACH ROW
  EXECUTE PROCEDURE update_test_table();