代码之家 › 专栏 › 技术社区 › daviestar

如何使我对一个id的查询对多个id有效

postgresql

daviestar · 技术社区 · 6 年前

假设我有一张桌子:

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 *

编辑

为了澄清,我现在要选择许多“根” 诺迪亚 以及他们所有的后代。对于示例用例:选择许多文件和文件夹并同时移动到垃圾箱。

谢谢。

2 回复 | 直到 6 年前

FXD 6 年前

如果你不实际使用一个函数,这是很直接的。

顺便说一下,我把它改成了 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 *

Hambone 6 年前

如果没有运行这些更新的更大上下文,就很难知道这是否正确。大概是在一个过程/功能内还是通过一个应用程序?

不管怎样,我认为您的最终语法很好——只是您需要确保传递的数据类型是一个数组:

update nodes
set trashed_at = now()
where id in (1, 2, 3);

在功能上与:

update nodes
set trashed_at = now()
where id = any(array[1, 2, 3]);

所以,回到你最初的陈述:

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

我想你可以简化为:

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

只要确定 node_ids 是一个64位整数数组。

因此,假设这是一个过程,下面是一些例子:

DECLARE
  node_ids bigint[];
BEGIN

  node_ids := array[1, 2, 3, 4];

  -- or perhaps

  select array_agg (bar)
  into node_ids
  from foo
  where baz = x;

  UPDATE nodes
  SET trashed_at = now()
  WHERE nodes.id = ANY(node_ids)
  RETURNING *;
END;

在imo中,将in列表作为参数传递总是很困难的,但是对于postgresql数组,这不仅是可能的,而且非常直接。