Keep a table column at the earliest related value of a column in another table

Question

I have two related tables. Lets call them event_types and occurrences with the following columns:

event_types
-------------
id VARCHAR(30)
name VARCHAR(50)
first_event Timestamp

and

occurrences
-------------
id VARCHAR(30)    
event_type_id VARCHAR(30)
timestamp Timestamp
postal_number Integer
city VARCHAR(50)
street VARCHAR(50)

I want to add a trigger which updates the first_event in case a new occurrence comes up with an earlier timestamp.

Use case: The reason is that I need to query this event_type table frequently with conditions on the first_event and sort it by this field to get the ones inside a time frame.

I tried with something like this:

CREATE OR REPLACE FUNCTION set_to_minimum_time() RETURNS TRIGGER AS
$$
    BEGIN
        IF NEW.timestamp < SELECT first_event from event_types where id=NEW.event_type_id THEN
            UPDATE event_types 
            SET first_event = NEW.timestamp
            WHERE id = NEW.event_type_id
        END IF
    END;
$$ 
LANGUAGE PLPGSQL;

CREATE TRIGGER after_trigger_time_updater
    AFTER INSERT OR UPDATE OR DELETE
    ON occurrences
    FOR EACH ROW
    EXECUTE PROCEDURE set_to_minimum_time();

This fails already due to the select statement in the if clause with syntax error. So how can I achieve such an update via trigger?

I am using Postgres > 9.5 and 10.

One further note: Such updates on occurrences won't come very often.

score 1 · Accepted Answer · answered Nov 15 '18 at 16:14

1

You could avoid the syntax error by putting parentheses around the SELECT statement.

But you made another mistake: you defined the trigger ON DELETE, and there is no NEW record in that case. It doesn't make sense to have this trigger ON DELETE anyway, does it?

The SQL statement in the trigger could be simpler:

UPDATE event_types 
SET first_event = NEW.timestamp
WHERE id = NEW.event_type_id
  AND first_event < NEW.timestamp;

That performs the check in the same statement.

answered Nov 15 '18 at 16:14

Laurenz Albe

209,280
17
206
263

That should have come to my mind as well. Maybe it was just a long day. Thank you. Final solution in edit – Stoecki Nov 16 '18 at 09:32
It is better to post your final solution as an answer, that is less confusing. – Laurenz Albe Nov 16 '18 at 09:36

score 0 · Answer 2 · answered Nov 16 '18 at 09:39

My final solution after Laurenz Albe's help:

CREATE OR REPLACE FUNCTION maybe_update_event_type_time() RETURNS TRIGGER   AS $maybe_update_event_type_time$
    BEGIN
        UPDATE event_types t
        SET t.first_event = NEW.timestamp
        WHERE t.id = NEW.event_type_id
          AND t.first_event > NEW.timestamp;
        RETURN NULL;
    END;
$maybe_update_event_type_time$ LANGUAGE PLPGSQL;

CREATE TRIGGER after_trigger_anomaly_time_updater
    AFTER INSERT OR UPDATE
    ON occurrences
    FOR EACH ROW
    EXECUTE PROCEDURE maybe_update_event_type_time();

Keep a table column at the earliest related value of a column in another table

2 Answers2