PostgreSQL更新触发器 [英] PostgreSQL Update trigger

问题描述

我有一张桌子:

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

并触发:

CREATE TRIGGER set_angle
AFTER INSERT OR UPDATE
ON annotations
FOR EACH ROW
EXECUTE PROCEDURE setangle();

和功能:

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;

当在表或编辑的行中插入新行时，我想将功能结果设置为rotationAngle字段. 但是当我在表功能中插入新行时无法正常工作.我的意思是rotationAngle的值不变.

有什么问题吗?

解决方案

正如 @SpartanElite指出的，您正在触发无休止的循环.

简化触发功能:

CREATE OR REPLACE FUNCTION set_angle()
  RETURNS TRIGGER AS
$func$
BEGIN
   NEW."rotationAngle" := degrees(
                             ST_Azimuth(
                                ST_StartPoint(NEW.the_geom)
                              , ST_EndPoint(NEW.the_geom)
                             )
                          ) - 90;
   RETURN NEW;
END
$func$ LANGUAGE plpgsql;

• 直接分配给NEW.在这种情况下没有WHERE.
• 您必须用双引号将非法列名引起来.最好不要使用此类名称开头.
  最近的相关答案.
• 插入代码&升级是一样的.我折叠成一个代码路径.

使用 BEFORE 触发器.这样，您可以直接在保存之前 编辑触发行的列:

CREATE TRIGGER set_angle
BEFORE INSERT OR UPDATE ON annotations
FOR EACH ROW EXECUTE PROCEDURE set_angle();

但是

如果，您只是试图将功能相关的值保留在表中(并且没有其他注意事项):不要.改用视图或生成的列:

• 将常见查询存储为列?

那么您就不需要这些了.<​​/p>

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?

解决方案

As @SpartanElite pointed out, you are triggering an endless loop.

Simplify the trigger function:

CREATE OR REPLACE FUNCTION set_angle()
  RETURNS TRIGGER AS
$func$
BEGIN
   NEW."rotationAngle" := degrees(
                             ST_Azimuth(
                                ST_StartPoint(NEW.the_geom)
                              , ST_EndPoint(NEW.the_geom)
                             )
                          ) - 90;
   RETURN NEW;
END
$func$ LANGUAGE plpgsql;

• 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.

这篇关于PostgreSQL更新触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！