0

Im making a INSERT/UPDATE trigger to populate a geometry column from a text column containing a WKT string. But now its overwriting all rows!

CREATE TABLE public.teams
(
  name text,
  coverage_wkt text,
  coverage_geom geometry(Polygon,4326)
)

CREATE TRIGGER trigger_insert_or_update_on_teams
AFTER INSERT OR UPDATE ON teams
FOR EACH ROW
WHEN (pg_trigger_depth() = 0)
EXECUTE PROCEDURE set_geom_columns();

CREATE FUNCTION set_geom_columns() RETURNS trigger AS $set_geom_columns$
BEGIN
    IF NEW.coverage_wkt IS NOT NULL THEN        
        NEW.name := 'im changed forever';       
        NEW.coverage_geom := ST_GeomFromText(NEW.coverage_wkt,4326);            
    END IF;        
    RETURN NEW;
END; $set_geom_columns$ LANGUAGE plpgsql;

INSERT INTO teams (coverage_wkt) VALUES ('POLYGON((17 59, 19 59, 19 60, 18 60, 17 59))');
INSERT INTO teams (coverage_wkt) VALUES ('POLYGON((18 59, 19 59, 19 60, 18 60, 18 59))');

Running the commands above leads to:

 name |                 coverage_wkt                 |   coverage_geom
      | POLYGON((17 59, 19 59, 19 60, 18 60, 17 59)) | 010300002...
      | POLYGON((18 59, 19 59, 19 60, 18 60, 18 59)) | 010300002...

The populated geometry column holds identical geometries: the last one inserted! That's leading me to believe its affecting all rows each time it is triggered. But from the docs I read:

A trigger that is marked FOR EACH ROW is called once for every row that the operation modifies

Furthermore, while the coverage_geom is actually persistent after "RETURN NEW" I cannot set NEW.name the same way. I don't know if it is related.

1
  • 1
    You need a BEFORE trigger if you want to change the values of the new record Commented Dec 29, 2016 at 12:57

1 Answer 1

1

"You need a BEFORE trigger if you want to change the values of the new record –" As pointed out by a_horse_with_no_name.

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.