Myujth

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.

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.

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();