CODEWITHSUNDEEP
postgresql
Encomium
Encomium

Reputation: 277

PostgreSQL trigger: first condition executes but not the second

A trigger works on the first part of a function but not the second.

I'm trying to set up a trigger that does two things:

  1. Update a field - geom - whenever the fields lat or lon are updated, using those two fields.
  2. Update a field - country - from the geom field by referencing another table.

I've tried different syntaxes of using NEW, OLD, BEFORE and AFTER conditions, but whatever I do, I can only get the first part to work.

Here's the code:

CREATE OR REPLACE FUNCTION update_geometries()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
        update schema.table a set geom = st_setsrid(st_point(a.lon, a.lat), 4326);
        
        update schema.table a set country = b.name 
        from reference.admin_layers_0 b where st_intersects(a.geom,b.geom)
        and a.pk = new.pk; 

        RETURN NEW;
END;
$$;

CREATE TRIGGER 
geom_update
AFTER INSERT OR UPDATE of lat,lon on 
schema.table
FOR EACH STATEMENT EXECUTE PROCEDURE update_geometries();

Upvotes: 0

Views: 43

Answers (1)

JGH
JGH

Reputation: 17836

There is no new on a statement level trigger. (well, there is, but it is always Null)

You can either keep the statement level and update the entire a table, i.e. remove the and a.pk = new.pk, or, if only part of the rows are updated, change the trigger for a row-level trigger and only update the affected rows

CREATE OR REPLACE FUNCTION update_geometries()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
        NEW.geom = st_setsrid(st_point(NEW.lon, NEW.lat), 4326);
        
        SELECT b.name
        INTO NEW.country
        FROM reference.admin_layers_0 b 
        WHERE st_intersects(NEW.geom,b.geom);

        RETURN NEW;
END;
$$;


CREATE TRIGGER 
geom_update
BEFORE INSERT OR UPDATE of lat,lon on 
schema.table
FOR EACH ROW EXECUTE PROCEDURE update_geometries();

Upvotes: 2

Related Questions