Reputation: 5299
I have a table:
CREATE TABLE annotations
(
gid serial NOT NULL,
annotation character varying(250),
the_geom geometry,
"rotationAngle" character varying(3) DEFAULT 0,
CONSTRAINT annotations_pkey PRIMARY KEY (gid),
CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2),
CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 4326)
)
And trigger:
CREATE TRIGGER set_angle
AFTER INSERT OR UPDATE
ON annotations
FOR EACH ROW
EXECUTE PROCEDURE setangle();
And function:
CREATE OR REPLACE FUNCTION setAngle() RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
UPDATE annotations SET "rotationAngle" = degrees( ST_Azimuth( ST_StartPoint(NEW.the_geom), ST_EndPoint(NEW.the_geom) ) )-90 WHERE gid = NEW.gid;
RETURN NEW;
ELSIF TG_OP = 'UPDATE' THEN
UPDATE annotations SET "rotationAngle" = degrees( ST_Azimuth( ST_StartPoint(NEW.the_geom), ST_EndPoint(NEW.the_geom) ) )-90 WHERE gid = NEW.gid;
RETURN NEW;
END IF;
END;
$$ LANGUAGE plpgsql;
And when new row inserted in table or row edited i want to field rotationAngle
setted with function result.
But when i inserting a new row in table function not work. I mean thath rotationAngle
value not changed.
What can be wrong?
Upvotes: 19
Views: 52569
Reputation: 658472
You are triggering an endless loop. Simplify the trigger function:
CREATE OR REPLACE FUNCTION set_angle()
RETURNS trigger
LANGUAGE plpgsql AS
$func$
BEGIN
NEW."rotationAngle" := degrees(
ST_Azimuth(
ST_StartPoint(NEW.the_geom)
, ST_EndPoint(NEW.the_geom)
)
) - 90;
RETURN NEW;
END
$func$;
NEW
directly. No WHERE
in this case.Use a BEFORE
trigger. This way you can edit columns of the triggering row directly before they are saved:
CREATE TRIGGER set_angle BEFORE INSERT OR UPDATE ON annotations FOR EACH ROW EXECUTE PROCEDURE set_angle();
If you are just trying to persist a functionally dependent value in the table (and there are no other considerations): Don't. Use a view or a generated column instead:
Then you don't need any of this.
Upvotes: 38
Reputation: 624
There are multiple things wrong here.
1) When you insert a row 'A' the function setAngle()
is called. But in the function you are calling another update
within the function which will trigger the function again, and again, and so on...To fix this don't issue a update! Just update the NEW records value independently and return it.
Upvotes: 5