CODEWITHSUNDEEP
postgresqlviewtriggers
Joseph Duffy
Joseph Duffy

Reputation: 4826

Trigger Not Firing When View's Contents Updated Indirectly

I'm starting to get a little confused about terms and I'm beginning to believe that what I am hoping to achieve is not possible. Is it possible to create a trigger that will fire when the information inside a view is changed, but not via an UPDATE/INSERT/DELETE statement on the view itself, but rather on one of the tables that view is made up of?

If not, is there an efficient way to filter which queries will fire a trigger?

To give a little more context, I have a table of users, and a table of locations. I only care about the latest location of each user within a certain area. I have created a view as follows:

CREATE OR REPLACE VIEW "users_near_coordinate" AS
SELECT DISTINCT ON ("User"."id") "User"."id", "User"."displayName", "UserLocation"."location", "UserLocation"."createdAt"
FROM "User", "UserLocation"
WHERE "UserLocation"."userId" = "User"."id" AND ST_Distance_Sphere(location, ST_MakePoint(long,lat)) <= 5 * 1609.34
ORDER BY "User"."id", "UserLocation"."createdAt" DESC;

I then have a trigger, as follows:

CREATE OR REPLACE FUNCTION notify_user_moved_within_range() RETURNS trigger AS $$
DECLARE
BEGIN
  PERFORM pg_notify('user_nearby_inserted', TG_TABLE_NAME || ':' || NEW);
  RETURN new;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER "user_moved_within_range" INSTEAD OF INSERT ON "users_near_coordinate" FOR EACH ROW EXECUTE PROCEDURE notify_user_moved_within_range();

I have also tried with a combination of AFTER INSERT [...] FOR EACH STATEMENT, but the trigger doesn't ever seem to be fired.

To test, I am inserting a location:

INSERT INTO "public"."UserLocation"("userId", "scenarioId", "location", "createdAt", "updatedAt") VALUES('8041849f-a204-4511-b6fa-74f3b731fd1f', '1', ST_GeomFromText('POINT(long lat)', 4326), now(), now());

Upvotes: 1

Views: 299

Answers (1)

Nick Barnes
Nick Barnes

Reputation: 21346

Views are little more than SQL macros. They don't actually contain any data, and nothing in the view itself changes when the underlying table is updated. Consequently, there is no INSERT event on the view which might fire a trigger; you need to put your trigger on the table.

You can easily define a trigger which fires conditionally, by attaching a WHEN (<condition>) clause to your CREATE TRIGGER statement. Alternatively, you can put the conditional logic in the trigger function itself, i.e.:

IF <condition> THEN
  PERFORM pg_notify(...);
END IF;

Upvotes: 1

Related Questions