时间戳

首页 > 解决方案 > Postgres 用公共数组元素合并两行

postgresql - Postgres 用公共数组元素合并两行

问题描述

我有一个列名为“ids”的 postgres 表。

+----+--------------+
| id |     ids      |
+----+--------------+
|  1 | {1, 2, 3}    |
|  2 | {2, 7, 10}   |
|  3 | {14, 11, 1}  |
|  4 | {12, 13}     |
|  5 | {15, 16, 12} |
+----+--------------+

我想将行与至少一个公共数组元素合并，并从中创建一个新行（或合并到一个现有行中）。所以最终表格如下所示：

+----+--------------------------+
| id |           ids            |
+----+--------------------------+
|  6 | {1, 2, 3, 7, 10, 14, 11} |
|  7 | {12, 13, 15, 16}         |
+----+--------------------------+

结果表中数组元素的顺序并不重要，但它们必须是唯一的。

这些行是独立于另一个系统添加的。例如，我们可以在 ids 的位置添加一个新行{16, 18, 1}。

现在，为了确保我们将所有行与至少一个公共数组元素组合在一起，我正在我的服务器 (Node.js) 中进行计算。

因此，在创建新行之前，我使用以下方法提取数据库中至少有一个共同项目的所有现有行：

await t.any('SELECT * FROM arraytable WHERE $1 && ids', [16, 18, 1])

这给了我所有至少有 16、18 或 1 的行。然后我将这些行与 [16、18、1] 合并并删除重复项。

随着这个新数组的可用性，我删除了上面提取的所有现有行并将这个新行插入到数据库中。如您所见，大部分工作都是在 Node.js 中完成的。

而不是这个，我试图创建一个触发器，它会在我添加新行后立即为我完成所有这些步骤。我该如何使用触发器来执行此操作。另外，有没有更好的方法？

标签： postgresql

解决方案

手续够吗？

CREATE OR REPLACE PROCEDURE add_ids(new_ids INT[])
AS $$
    DECLARE sum_array INT[];
    BEGIN
        SELECT ARRAY (SELECT UNNEST(ids) FROM table1 WHERE table1.ids && new_ids) INTO sum_array;
        sum_array := sum_array || new_ids;
        SELECT ARRAY(SELECT DISTINCT UNNEST(sum_array)) INTO sum_array;
        DELETE FROM table1 WHERE table1.ids && sum_array;
        INSERT INTO table1(ids) SELECT sum_array;
    END;
$$
LANGUAGE plpgsql;

不幸的是，在触发器中插入行会调用另一个触发器，从而导致无限循环。我不知道解决这个问题。

PS。抱歉，如果创建另一个答案是不好的做法。我想暂时留下它以供参考。问题解决后我会删除它。

由 pewpewlasers 编辑：

为了防止循环，可能需要另一个表。我创建了一个新的临时table2. 可以将新数组添加到此表中。该表将有一个触发器，它进行计算并将其保存到table1. 它还会删除这个临时创建的行。

CREATE OR REPLACE FUNCTION  on_insert_temp() RETURNS TRIGGER AS $f$
DECLARE sum_array BIGINT[];
BEGIN
    SELECT ARRAY (SELECT UNNEST(ids) FROM table1 WHERE table1.ids && NEW.ids) INTO sum_array;
    sum_array := sum_array || NEW.ids;
    SELECT ARRAY(SELECT DISTINCT UNNEST(sum_array)) INTO sum_array;
    DELETE FROM table1 WHERE table1.ids && sum_array;
    INSERT INTO table1(ids) SELECT sum_array;
    DELETE FROM table2 WHERE id = NEW.id;
    RETURN OLD;
END
$f$ LANGUAGE plpgsql;

CREATE TRIGGER on_insert_temp AFTER INSERT ON table2 FOR EACH ROW EXECUTE PROCEDURE  on_insert_temp();

推荐阅读