How to create trigger to check geometry validity?

Question

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?

Answer 1

Referring to the documentation, I suspect that you want to do something like the following

CREATE TABLE vec_geometry 
(
  id bigserial,
  is_valid boolean, 
  invalid_reason character(128),
  geom geography
);

CREATE FUNCTION geom_valid_check() RETURNS trigger AS $geom_valid_check$
DECLARE
    val valid_detail;
BEGIN
    val := ST_IsValidDetail(NEW.geom::Geometry);
    NEW.is_valid = valid(val);
    NEW.invalid_reason = reason(val);
    RETURN NEW;
END;
$geom_valid_check$
LANGUAGE plpgsql VOLATILE NOT LEAKPROOF;
CREATE TRIGGER vec_geometry_is_valid BEFORE INSERT OR UPDATE ON vec_geometry
    FOR EACH ROW EXECUTE PROCEDURE geom_valid_check();

INSERT INTO vec_geometry VALUES (1,NULL,NULL,ST_GeomFromText('POLYGON((0 0, 1 1, 1 2, 1 1, 0 0))'));

SELECT * FROM  vec_geometry;

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.