CODEWITHSUNDEEP
postgresqlsyntax
Peter Mølgaard Pallesen
Peter Mølgaard Pallesen

Reputation: 1941

postgres create "delete_expired_rows"- trigger

I have some code that does not delete rows when there sys_period ends and I move them to a historic table. So I would like to make a trigger that after an update of a row looks at all the rows and removes each row that does where the sys_period end is not infinity. My initial guess is the following. But I'm not a master in SQL syntax can you help me?

CREATE TRIGGER {0}_delete_old_rows_trigger
        AFTER UPDATE ON "{0}"
        BEGIN 
            DELETE FROM "{0}"
            WHERE upper_inf(sys_period) is FALSE
        END

Upvotes: 1

Views: 81

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247340

This looks like it could be handled with a query rewrite rule:

CREATE RULE upd_to_del AS ON UPDATE TO tab
   WHERE NOT upper_inf(NEW.sys_period)
   DO INSTEAD
      DELETE FROM tab WHERE id = NEW.id;

Upvotes: 1

Related Questions