4

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?

1 Answer 1

4

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.

2
  • Is there a way to avoid double check on geometry validity? Dec 21, 2015 at 3:57
  • @SS_Rebelious have altered the answer to do the check once.
    – MickyT
    Dec 21, 2015 at 19:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.