代码之家 › 专栏 › 技术社区 › Daniel Gustafsson

PostgreSQL触发器语法错误在“new”处或附近

postgresql

Daniel Gustafsson · 技术社区 · 6 年前

我想做的是:

ALTER TABLE publishroomcontacts ADD COLUMN IF NOT EXISTS contactorder integer NOT NULL default 1;

CREATE OR REPLACE FUNCTION publishroomcontactorder() RETURNS trigger AS $publishroomcontacts$
    BEGIN   
      IF (TG_OP = 'INSERT') THEN
            with newcontactorder as (SELECT contactorder FROM publishroomcontacts WHERE publishroomid = NEW.publishroomid ORDER BY contactorder limit 1)    
            NEW.contactorder = (newcontactorder + 1);
        END IF;
        RETURN NEW;
    END;
$publishroomcontacts$ LANGUAGE plpgsql;

CREATE TRIGGER publishroomcontacts BEFORE INSERT OR UPDATE ON publishroomcontacts
    FOR EACH ROW EXECUTE PROCEDURE publishroomcontactorder();

我已经研究了很多例子,它们都是这样的。他们中的大多数人都是几岁的Tho。这个改变了吗?为什么新的不起作用?我必须在函数中插入,还是Postgres在函数完成后用返回的新对象进行插入?

2 回复 | 直到 6 年前

Kamil Gosciminski 6 年前

我不知道你想做什么,但是你的语法错误:

with newcontactorder as (SELECT contactorder FROM publishroomcontacts WHERE publishroomid = NEW.publishroomid ORDER BY contactorder limit 1)    
NEW.contactorder = (newcontactorder + 1);

如果后面没有选择,则不要使用CTE查询。如果你想增加 contactorder 列 publishroomid 每当添加新的序列(自动递增)机制时,应将其替换为:

NEW.contactorder = COALESCE((
    SELECT max(contactorder)
    FROM publishroomcontacts
    WHERE publishroomid = NEW.publishroomid
    ), 1);

注意更改:

没有CTE,只有带有select查询的变量赋值
使用 MAX() 聚合函数而不是 ORDER BY + LIMIT
裹着 COALESCE(x,1) 功能是正确插入房间的第一个联系人,它将返回 1 如果您的查询返回 NULL

你的扳机应该是这样的

CREATE OR REPLACE FUNCTION publishroomcontactorder() RETURNS trigger AS $publishroomcontacts$
BEGIN   
  IF (TG_OP = 'INSERT') THEN
    NEW.contactorder = COALESCE((
      SELECT max(contactorder) + 1
      FROM publishroomcontacts
      WHERE publishroomid = NEW.publishroomid
      ), 1);
  END IF;
  RETURN NEW;
END;
$publishroomcontacts$ LANGUAGE plpgsql;

后格雷斯 将插入行本身,您无需执行任何操作,因为 RETURN NEW 那样做。

此解决方案不考虑并发插入,这使得它对多用户环境不安全!您可以通过执行 UPSERT !

Laurenz Albe 6 年前

WITH 不是pl/pgsql中的赋值。

pl/pgsql将行解释为sql语句,但这是错误的sql,因为 用 子句后接 NEW.contactorder 而不是 SELECT 或者另一个CTE。

因此产生了错误;这与 NEW 像这样的。

你可能想要类似的东西

SELECT contactorder INTO newcontactorder
FROM publishroomcontacts
WHERE publishroomid = NEW.publishroomid
ORDER BY contactorder DESC  -- you want the biggest one, right?
LIMIT 1;

你得申报 newcontactorder 在 DECLARE 部分。

警告: 如果有两个并发插入,它们可能以相同的方式结束。 新接触顺序 .