Recursive trigger into function

Question

I have problems with recursive trigger: the table that fires the trigger and give a specific error for that; the trigger inserts a record, that fires the trigger, that fires on the table, that fires the trigger,...

So, I must create a trigger that collects the date of insertion or modification of a row. The date field is the one that must be filled when there is a modification or insertion of a row. The problem is that it tells me "HINT: Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate. "

This came out when I added OR UPDATE in the trigger. I leave my code for your consideration. How can I improve my code to avoid it being a recursive trigger? thanks!

*table_0 is a table to registrer changes.

ALTER TABLE table_gas ADD COLUMN update_t VARCHAR(20);


CREATE TABLE table_0  (
    id_gas          INT NOT NULL,
    update_t        VARCHAR(20)
 );
 

CREATE OR REPLACE FUNCTION inserted() RETURNS TRIGGER AS $$
BEGIN
    IF (TG_OP='INSERT') THEN
        INSERT INTO table_0 VALUES(NEW.id_gas, TO_CHAR(NOW(), 'yyyy-mm-dd hh:mm'));
        UPDATE table_gas x
            SET update_t=TO_CHAR(NOW(), 'yyyy-mm-dd hh:mm')
            FROM table_0 y
            WHERE x.id_gas = y.id_gas;
            RETURN NEW;
            
    ELSEIF (TG_OP='UPDATE') THEN
        INSERT INTO table_0 VALUES(NEW.id_gas, TO_CHAR(NOW(), 'yyyy-mm-dd hh:mm'));
        UPDATE table_gas x
            SET update_t=TO_CHAR(NOW(), 'yyyy-mm-dd hh:mm')
            FROM table_0 y
            WHERE x.id_gas = y.id_gas;
            RETURN NEW;
            
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;


CREATE TRIGGER insert_d
    BEFORE INSERT OR UPDATE ON table_gas
    FOR EACH ROW EXECUTE FUNCTION inserted();

    
INSERT INTO table_gas(id_gas, id_city, id_car)
          VALUES(34, 398,'28ha')

Answer 1

As you are using a BEFORE trigger (which is good) you can directly assign the values you want to the NEW record. There is no need to run an UPDATE.

I also don't see a reason to distinguish between update and insert inside the trigger function. You are doing the same thing in both cases.

You should also really fix your data types. Storing a timestamp in a varchar column is a really, really bad idea.

So the trigger function can be simplified to:

CREATE OR REPLACE FUNCTION inserted() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO table_0 VALUES(NEW.id_gas, now());
  new.update_t := now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Online example