postgresql - 如何使我对一个 ID 的查询适用于多个 ID

Question

假设我有一张 table :

CREATE TABLE nodes (
  id         SERIAL PRIMARY KEY,
  parent_id  INTEGER REFERENCES nodes(id),
  trashed_at timestamptz
)

我有这个查询 nodes_trash_node(node_id INTEGER):

UPDATE nodes SET
  trashed_at = now()
WHERE nodes.id = node_id
OR nodes.id IN (SELECT id FROM nodes_descendants(node_id))
RETURNING *

nodes_descendants 函数在邻接表结构上运行，如下所示:

CREATE OR REPLACE FUNCTION nodes_descendants(node_id INTEGER, depth INTEGER)
RETURNS TABLE (id INTEGER) AS $$
  WITH RECURSIVE tree AS (
    SELECT id, array[node_id]::integer[] as ancestors
    FROM   nodes
    WHERE  parent_id = node_id
    UNION ALL
    SELECT nodes.id, tree.ancestors || nodes.parent_id
    FROM   nodes, tree
    WHERE  nodes.parent_id = tree.id
    AND    (depth = 0 OR cardinality(tree.ancestors) < depth)
  )
  SELECT id FROM tree;
$$ LANGUAGE sql;

(取自 here )。

但是我现在想将我的查询转换为获取 node_id 的列表，但我正在努力寻找正确的语法。像这样的东西:

UPDATE nodes SET
  trashed_at = now()
WHERE nodes.id = ANY(node_ids)
OR nodes.id IN (???)
RETURNING *

编辑

澄清一下，我现在想选择许多“根”node_id 及其所有后代。对于示例用例:选择多个文件和文件夹并同时移至垃圾箱。

谢谢。

Answer 1

如果您实际不使用某个函数，它是直截了当的。

顺便说一句，我已将其更改为正确的 INNER JOIN。
请不要使用表产品(即交叉连接)后跟 WHERE，因为有一天您会错误地跳过它。

WITH RECURSIVE tree AS (
    SELECT  id
    FROM   nodes
    WHERE  <Type your condition here>
    UNION ALL
    SELECT nodes.id
    FROM   nodes
    JOIN tree ON nodes.parent_id = tree.id
)
UPDATE nodes SET
  trashed_at = now()
WHERE nodes.id IN (SELECT id from Tree)
RETURNING *