Versions:

Creating triggers

TimescaleDB supports the full range of PostgreSQL triggers, and creating, altering, or dropping triggers on the hypertable will similarly propagate these changes to all of a hypertable's constituent chunks.

In the following example, let's say you want to create a new table error_conditions with the same schema as conditions, but designed to only store records which are deemed erroneous, where an application signals a sensor error by sending a temperature or humidity having a value >= 1000.

So, we'll take a two-step approach. First, let's create a function that will insert data deemed erroneous into this second table:

CREATE OR REPLACE FUNCTION record_error()
  RETURNS trigger AS $record_error$
BEGIN
 IF NEW.temperature >= 1000 OR NEW.humidity >= 1000 THEN
   INSERT INTO error_conditions
     VALUES(NEW.time, NEW.location, NEW.temperature, NEW.humidity);
 END IF;
 RETURN NEW;
END;
$record_error$ LANGUAGE plpgsql;

Second, create a trigger that will call this function whenever a new row is inserted into the hypertable.

CREATE TRIGGER record_error
  BEFORE INSERT ON conditions
  FOR EACH ROW
  EXECUTE PROCEDURE record_error();

Now, all data is inserted into the conditions data, but any row deemed erroneous is also added to the error_conditions table.

TimescaleDB supports the full gamut of triggers: BEFORE INSERT, AFTER INSERT, BEFORE UPDATE, AFTER UPDATE, BEFORE DELETE, AFTER DELETE. For additional information, see the PostgreSQL docs.

Found an issue on this page?

Report an issue!

Keywords

Related Content