时间戳

首页 > 解决方案 > 在 PostgreSQL 触发器中循环和测试 cte 的结果

postgresql - 在 PostgreSQL 触发器中循环和测试 cte 的结果

问题描述

PostgreSQL 11.1

我正在使用以下触发器从 chart_gap 表中获取“新”图表编号：

CREATE FUNCTION phoenix.next_chart()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF 
AS $BODY$
BEGIN
  -- Check for empty chart number
  IF NEW.chart_number is null or NEW.chart_number = 0 THEN
    WITH ins AS (
      SELECT chart_number
      FROM   chart_gap
      WHERE  pg_try_advisory_xact_lock(chart_number)
      LIMIT  1
    )
    DELETE FROM chart_gap c
    USING  ins i
    WHERE  i.chart_number = c.chart_number
    RETURNING i.chart_number INTO NEW.chart_number;
  END IF;

  RETURN NEW;
END;
$BODY$;

我将如何添加测试以确保返回的 chart_number 不存在（在表患者中），如果存在，则强制循环以获取表中的下一个图表编号等，直到未使用的 chart_number找到了吗？也就是说，触发器必须返回新的和未使用的图表编号。

TIA

注意：我最初的想法是使用递归 cte，但认为直接循环可能更快？

标签： postgresqltriggersrecursive-cte

解决方案

我希望您有充分的理由不使用序列值。

LOOP您可以简单地用/包围代码块，END LOOP;并在末尾添加一个SELECT count(*)以测试是否有任何行chart_number。只要每笔交易都走这条路，那应该是安全的。

您必须确保INSERT在持有咨询锁的同时发生在同一个事务中。

作为咨询锁定的替代方案，您还可以

SELECT chart_number
FROM chart_gap
FOR UPDATE SKIP LOCKED
LIMIT 1;

这可能更简单。

推荐阅读