18

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?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Kliver Max
  • 5,107
  • 22
  • 95
  • 148

2 Answers2

35

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$;
  • Assign to NEW directly. No WHERE in this case.
  • You must double-quote illegal column names. Better not to use such names to begin with.
    Recent related answer.
  • Code for insert & upgrade is the same. I folded into one code path.

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();

However

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 4
    Thanks, @Erwin you are wizard! Your answer awesome again, it's really nice that you giving not just a answer for the question but also an eplanation. – Kliver Max Aug 13 '14 at 05:19
  • What should I do for partitioned table ? I can't create **BEFORE** trigger and my trigger didn't applied with **AFTER** trigger. I tested with both **NEW** and **OLD**. If I use `UPDATE` query with **AFTER** trigger , that may causes infinite loop. – Cataclysm Jun 29 '19 at 17:13
  • I found the solution for my problem from one of your valueable answer from [This Question](https://stackoverflow.com/questions/14137932/update-a-table-with-a-trigger-after-update) – Cataclysm Jun 29 '19 at 17:30
5

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.

SpartanElite
  • 624
  • 4
  • 13