PostgreSQL Update trigger

Question

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 · Accepted Answer · 2022-11-17 02:34:59Z

38

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:

Store common query as column?

Then you don't need any of this.

edited Nov 17, 2022 at 2:34

answered Aug 12, 2014 at 20:13

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Kliver Max Over a year ago

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.

Cataclysm Over a year ago

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 Over a year ago

I found the solution for my problem from one of your valueable answer from This Question

SpartanElite · Accepted Answer · 2014-08-12 20:22:36Z

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.

edited Aug 12, 2014 at 20:22

answered Aug 12, 2014 at 19:59

SpartanElite

6244 silver badges13 bronze badges

Collectives™ on Stack Overflow

PostgreSQL Update trigger

2 Answers 2

However

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

However

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related