Postgres:具有选择和通知功能

Question

所以，我有一个表格描述了空间中一些实体的位置(为了一维的简单性，原始问题是相关的)

CREATE TABLE positions(
id VARCHAR (32) UNIQUE NOT NULL,
position VARCHAR (50) NOT NULL
);

我创建了一个触发器，它会在每次更新时通知

CREATE TRIGGER position_trigger AFTER UPDATE ON positions
FOR EACH ROW
EXECUTE PROCEDURE notify_position();

和一个函数：

CREATE FUNCTION notify_position() RETURNS trigger AS $$
DECLARE
BEGIN
PERFORM pg_notify(
'positions',
TG_TABLE_NAME || ',id,' || NEW.id || ',position,' || NEW.position
);
RETURN new;
END;
$$ LANGUAGE plpgsql;

如何将功能更改为仅在同一位置的实体很少时才通知。

例如，在更新后考虑一个表格

id  |positions
-------------
id1 |10
id2 |10
id3 |11

我需要用字符串"id1，id2"调用通知

可能，我需要以某种方式选择与更新的实体具有相同位置的所有实体，并创建一个列表(包含逗号分隔 id 的字符串)。我该怎么做？

Answer 1

根据我对你的问题的理解，您希望在更新实体的位置时发生以下情况：

• 如果当前没有实体处于新位置，则不执行任何操作。
• 否则，使用当前处于新位置的实体 ID 的逗号分隔列表调用通知。

您可以在触发器函数中实现此目的，方法是使用string_agg聚合函数构造匹配实体的 ID 列表，然后在调用pg_notify之前检查其长度：

CREATE FUNCTION notify_position() RETURNS trigger AS $$
DECLARE
colocated_ids TEXT;
BEGIN
-- Construct list of IDs of co-located entities.
colocated_ids := (
SELECT string_agg(id, ',')
FROM positions
WHERE position = NEW.position AND id != NEW.id
);
-- Notify listeners if some co-located entities were found.
IF length(colocated_ids) > 0 THEN
PERFORM pg_notify('positions', colocated_ids);
END IF;
RETURN new;
END;
$$ LANGUAGE plpgsql;

请注意，如果不进行AND id != NEW.id检查，更新的实体的 ID 也将显示在列表中。您可以通过使此触发器触发BEFORE UPDATE而不是AFTER UPDATE来避免这种情况。