时间戳

首页 > 解决方案 > Postgresql 在“新”处或附近触发语法错误

postgresql - Postgresql 在“新”处或附近触发语法错误

问题描述

这是我正在尝试做的事情：

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 对象进行插入？

标签： postgresql

解决方案

我不确定您要做什么，但是您的语法在这里是错误的：

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)，它将返回1NULL

您的触发器应如下所示

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!

推荐阅读