Create a TRIGGER with PostgreSQL

Question

I would like to create my first trigger on my PostgreSQL database but I got a syntax error.

The trigger should be able to delete each row from token_jwt_usertoken table where date_information is inferior than now() - interval '2 month'. In other words, if the row is expired from at least 2 months, the row should be deleted.

This is my command :

CREATE FUNCTION after_update_token() RETURNS TRIGGER AS $after_update_token$


BEGIN
    IF old.date_information < now() - INTERVAL '2 month'
      THEN
        DELETE FROM token_jwt_usertoken WHERE old.date_information < now() - INTERVAL '2 month';
    END IF;
END;

$after_update_token$ LANGUAGE plpgsql;

CREATE TRIGGER after_update_token AFTER UPDATE ON token_jwt_usertoken FOR EACH ROW EXECUTE FUNCTION after_update_token();

I get :

[2019-02-07 17:14:01] [42601] ERROR: syntax error at or near "FUNCTION" [2019-02-07 17:14:01] Position : 92

Then, I don't know if my request is well-written in my trigger.

Thank you !

Lockman

The trigger function and the TRIGGER itself are separately-created objects. See the docs for examples. – Disc

you're missing when clause – Weasner

In PostgreSQL you have to create a function or procedure and call it. There is no BEGIN allowed in the command. – Carlist

@NickBarnes I edited my question but I have a syntax issue yet. – Lockman

You've typed FOR EACH ROW EXECUTE FUNCTION but I think the correct syntax is FOR EACH ROW EXECUTE PROCEDURE. – Ranie

@lau: Yes, that's it. Both will work in Postgres 11, but older versions only accept EXECUTE PROCEDURE. – Disc

I didn't even know it changed on version 11. It's just that the one FUNCTION matching Position : 92 is the latter one, not the one in CREATE FUNCTION – Ranie

Answer 1

Seems like you have to declare a function and call that like they say:

CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
    ON table
    [ FROM referenced_table_name ]
    [ NOT DEFERRABLE | [ DEFERRABLE ] { INITIALLY IMMEDIATE | INITIALLY DEFERRED } ]
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    [ WHEN ( condition ) ]
    EXECUTE PROCEDURE function_name ( arguments )

where event can be one of:

    INSERT
    UPDATE [ OF column_name [, ... ] ]
    DELETE
    TRUNCATE

https://www.postgresql.org/docs/9.1/sql-createtrigger.html

Antislavery

Answer 2

According to @lau answer, this is the code which solve my issue :

CREATE FUNCTION after_update_token() RETURNS TRIGGER AS $after_update_token$
  BEGIN
    IF to_timestamp(old.date_information, 'YYYY/MM/DD') < now() - INTERVAL '2 month'
      THEN
        DELETE FROM token_jwt_usertoken WHERE to_timestamp(old.date_information, 'YYYY/MM/DD') < now() - INTERVAL '2 months';
    END IF;
END;

$after_update_token$ LANGUAGE plpgsql;

CREATE TRIGGER after_update_token AFTER UPDATE ON token_jwt_usertoken FOR EACH ROW EXECUTE PROCEDURE after_update_token();

I had to write : FOR EACH ROW EXECUTE PROCEDURE instead of FOR EACH ROW EXECUTE FUNCTION

Lockman