CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.1
Marcus Junius Brutus
Marcus Junius Brutus

Reputation: 27296

create a trigger on a view

I have a time-dependent view that uses the now() function and hence changes values with the passage of time.

Simplistic code below:

DROP VIEW IF EXISTS av;
DROP TABLE IF EXISTS A;
CREATE TABLE A (when_epoch_seconds INTEGER, x VARCHAR);

INSERT INTO A(when_epoch_seconds, x) SELECT CAST (EXTRACT (epoch FROM now()) AS INT), 'x';

CREATE VIEW av AS
WITH cte AS
(SELECT MAX(when_epoch_seconds) AS latestEntry FROM a)
SELECT CAST (EXTRACT (epoch FROM now()) AS INT) - cte.latestEntry > 5 AS too_old FROM cte;

SELECT too_old FROM av;

My question is how can I create a trigger that continuously monitors the value of column too_old in view av and, e.g., inserts a row in a "notifications" table whenever that value flips from true to false or vice-versa? Or is there maybe some other mechanism that's more suited to accomplishing the same effect?

Upvotes: 1

Views: 365

Answers (1)

Alberto Solano
Alberto Solano

Reputation: 8227

A view is a "virtual" table, meaning that it reflects the values stored into one or more tables. A trigger is a set of instructions that are executed when a specific event (INSERT, DELETE, etc) happens on a table.

how can I create a trigger that continuously monitors the value of column too_old in view av

You can't create a trigger related to a view. A view doesn't "change" values, a table does. A view is only a result set of records stored into the referencing table(s). EDIT: You can actually create a view (as @MikeSherrill'CatRecall' precisely said), in PostgreSQL 9.1. It is a feature introduced in the latest versions of the engine. Even if you can create a trigger on a view, I think the most correct way is to create it on the base table. I don't see actually the point in creating it on a view.

It doesn't exist a trigger that is always executed, listening for changes of referencing table. If it would be existing, a trigger that runs continuously will affect terribly the performance of your SQL engine, making your SQL engine sluggish and incredibly slow running the queries. That, I think, is the main reason why such thing doesn't exist.

The PostgreSQL engine has a nice feature that could help you, listening for changes, named indeed LISTEN.

Upvotes: 1

Related Questions