"NEW"处或附近的 Postgresql 触发器语法错误

Question

这是我正在尝试做的:

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

我研究了很多例子，它们看起来都是这样的。他们中的大多数只有几岁。这是否已更改或为什么 NEW 不起作用？我必须在函数中插入还是 postgres 在函数完成后用返回的 NEW 对象插入？

Answer 1

我不确定你想做什么，但你的语法在这里是错误的:

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;

Postgres 将自己插入该行，您无需执行任何操作，因为 RETURN NEW这样做。

此解决方案不处理并发插入，这使得它对多用户环境不安全!您可以通过执行 UPSERT 来解决此问题!