PostgreSQL trigger: first condition executes but not the second

Question

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:

Update a field - geom - whenever the fields lat or lon are updated, using those two fields.
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();

JGH · Accepted Answer · 2021-05-25 18:30:49Z

2

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

answered May 25, 2021 at 18:30

JGH

18.2k5 gold badges40 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Encomium Over a year ago

Thank you for that information @JGH. Yeah, I had the feeling that RETURN NEW wasn't doing anything here, but didn't know how to manipulate the NEW object to get back what I wanted. Appreciate the great and concise answer.

JGH Over a year ago

it wasn't the return but the "join" condition that was failing. On a side note, you may want to add a few validations before setting/using the geometry (what if lat or long is null? out of bound? etc) otherwise an error will rollback the entire update (including other fields)

Encomium Over a year ago

Thanks. There are constraints on those fields to limit entry to valid WSG84, but you're right, the whole update rolls back when someone attempts to enter invalid values. If that gets problematic, I'll update the trigger to handle that too.

Collectives™ on Stack Overflow

PostgreSQL trigger: first condition executes but not the second

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related