需要帮助编写 PostgreSQL 触发器函数

Question

我有两个表代表两种不同类型的图像。我正在使用 PostGIS 来表示这些图像的边界。下面是这些表的一个简化示例：

CREATE TABLE img_format_a (
    id SERIAL PRIMARY KEY,
    file_path VARCHAR(1000),
    boundary GEOGRAPHY(POLYGON, 4326)
);

CREATE TABLE img_format_p (
    id SERIAL PRIMARY KEY,
    file_path VARCHAR(1000),
    boundary GEOGRAPHY(POLYGON, 4326)
);

我还有一个交叉引用表，我希望其中包含彼此重叠的图像的所有 ID。每当“A”类型的图像插入数据库时​​，我想检查它是否与“P”类型的任何现有图像重叠（反之亦然），并将相应的条目插入到 img_a_img_p交叉引用表。该表应该表示多对多关系。

我的第一反应是编写一个触发器来管理这个img_a_img_p表。我以前从未创建过触发器，所以请告诉我这是否是一件愚蠢的事情，但这对我来说似乎很有意义。所以我创建了以下触发器：

CREATE TRIGGER update_a_p_cross_reference
    AFTER INSERT OR DELETE OR UPDATE OF boundary
    ON img_format_p FOR EACH ROW
    EXECUTE PROCEDURE check_p_cross_reference();

我遇到困难的部分是编写触发器函数。我的代码是用 Java 编写的，我看到有像 PL/pgSQL 这样的工具，但我不确定这是否是我应该使用的，或者我是否需要这些特殊的附加组件之一。

基本上，我需要触发器做的就是每次将新图像插入 img_format_a 或 img_format_p 时更新交叉引用表。插入新图像时，我想使用 ST_Intersects 等 PostGIS 函数来确定新图像是否与其他表中的任何图像重叠。对于 ST_INTERSECTS 返回 true 的每个图像对，我想在 img_a_img_p 中插入一个新条目，其中包含两个图像的 ID。有人可以帮我弄清楚如何编写这个触发函数吗？这是一些伪代码：

SELECT * FROM img_format_p P
    WHERE ST_Intersects(A.boundary, P.boundary);

for each match in selection {
    INSERT INTO img_a_img_p VALUES (A.id, P.id);
}

Answer 1

您可以将通常的 INSERT ... SELECT 习惯用法包装在 PL/pgSQL 函数中，如下所示：

create function check_p_cross_reference() returns trigger as
$
begin
    insert into img_a_img_p (img_a_id, img_p_id)
    select a.id, p.id
    from img_format_a, img_format_p
    where p.id = NEW.id
      and ST_Intersects(a.boundary, p.boundary);
    return null;
end;
$ language plpgsql;

触发器有两个额外的变量，新和旧：

新
数据类型记录；保存行级触发器中 INSERT/UPDATE 操作的新数据库行的变量。该变量在语句级触发器和 DELETE 操作中为 NULL。

旧
数据类型记录；保存旧数据库行的变量，用于行级触发器中的 UPDATE/DELETE 操作。该变量在语句级触发器和 INSERT 操作中为 NULL。

因此，您可以使用 NEW.id 访问即将输入的新 img_format_p 值。您（当前）不能使用普通 SQL 语言作为触发器：

目前无法用普通 SQL 函数语言编写触发器函数。

但 PL/pgSQL 非常接近。这作为 AFTER INSERT 触发器是有意义的：

CREATE TRIGGER update_a_p_cross_reference
AFTER INSERT
ON img_format_p FOR EACH ROW
EXECUTE PROCEDURE check_p_cross_reference();

可以使用 img_a_img_p 上的外键和级联删除。您也可以使用触发器进行更新：

CREATE TRIGGER update_a_p_cross_reference
AFTER INSERT OR UPDATE OF boundary
ON img_format_p FOR EACH ROW
EXECUTE PROCEDURE check_p_cross_reference();

但您可能希望在插入新条目之前清除旧条目，例如：

delete from img_a_img_p where img_p_id = NEW.id;

在 INSERT...SELECT 语句之前。