I want to create trigger to check geometry validity in spatial table. I have a table:
CREATE TABLE vec_geometry
(
id bigserial
is_valid boolean,
invalid_reason character(128),
geom geography
)
When I try to create trigger function I get the error:
CREATE FUNCTION geom_valid_check() RETURNS trigger AS
$BODY$
WITH test as (SELECT * FROM ST_IsValidDetail(NEW.geom))
UPDATE vec_geometry
SET is_valid = (SELECT valid FROM test),
invalid_reason = (SELECT reason FROM test)
WHERE id = NEW.id;
RETURN NULL;
$BODY$
LANGUAGE plpgsql VOLATILE NOT LEAKPROOF;
An error has occurred:
ERROR: syntax error at or near "WITH"
LINE 3: WITH test as (SELECT * FROM ST_IsValidDetail(NEW.geom))
How to rewrite function to make it work?
Best Answer
Referring to the documentation, I suspect that you want to do something like the following
You will notice that it is not trying to update the table directly, but is changing the NEW row before it is inserted or updated into the table.