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

问题描述

我有一张桌子:

CREATE TABLE 注释(gid 序列号非空，注释字符变化(250)，the_geom 几何，"rotationAngle" 字符变化 (3) DEFAULT 0,CONSTRAINT annotations_pkey PRIMARY KEY (gid),约束强制执行_dims_the_geom 检查(st_ndims(the_geom)= 2)，约束强制执行_srid_the_geom 检查(st_srid(the_geom)= 4326))

并触发:

CREATE TRIGGER set_angle插入或更新后ON 注释每行执行程序 setangle();

和函数:

创建或替换函数 setAngle() 返回触发器为 $$开始IF TG_OP = 'INSERT' THEN更新注释 SET "rotationAngle" = degree( ST_Azimuth( ST_StartPoint(NEW.the_geom), ST_EndPoint(NEW.the_geom) ) )-90 WHERE gid = NEW.gid;退货；ELSIF TG_OP = 'UPDATE' THEN更新注释 SET "rotationAngle" = degree( ST_Azimuth( ST_StartPoint(NEW.the_geom), ST_EndPoint(NEW.the_geom) ) )-90 WHERE gid = NEW.gid;退货；万一;结尾;$$ 语言 plpgsql;

当新行插入到表中或编辑的行中时，我想将 rotationAngle 字段设置为函数结果.但是当我在表函数中插入新行时不起作用.我的意思是 rotationAngle 值没有改变.

有什么问题?

解决方案

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

简化触发功能:

创建或替换函数 set_angle()返回触发作为$func$开始新.旋转角度":= 度(ST_方位角(ST_StartPoint(NEW.the_geom), ST_EndPoint(NEW.the_geom))) - 90;退货；结尾$func$ LANGUAGE plpgsql;

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

使用 BEFORE 触发器.这样你就可以直接编辑触发行的列之前它们被保存:

CREATE TRIGGER set_angle之前插入或更新注释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屋！