PostgreSQL递归CTE结果排序 [英] Postgresql recursive CTE results ordering

问题描述

我正在研究将数据拉出层次结构的查询

I'm working on a query to pull data out of a hierarchy

例如

CREATE table org (
   id   INT PRIMARY KEY,
   name TEXT NOT NULL,
   parent_id INT);

INSERT INTO org (id, name) VALUES (0, 'top');
INSERT INTO org (id, name, parent_id) VALUES (1, 'middle1', 0);
INSERT INTO org (id, name, parent_id) VALUES (2, 'middle2', 0);
INSERT INTO org (id, name, parent_id) VALUES (3, 'bottom3', 1);

WITH RECURSIVE parent_org (id, parent_id, name) AS (
  SELECT id, parent_id, name
  FROM org
  WHERE id = 3
UNION ALL
    SELECT o.id, o.parent_id, o.name
    FROM   org o, parent_org po
    WHERE  po.parent_id = o.id)
SELECT id, parent_id, name
FROM parent_org;

它按预期方式工作。

3 1 "bottom3"
1 0 "middle1"
0   "top"

它也按照我期望的顺序返回数据，对我来说，这样做的原因是有意义的，因为它会发现结果。

It's also returning the data in the order that I expect, and it makes sense to me that it would do this because of the way that the results would be discovered.

问题是，我可以指望像这样的订单吗？

The question is, can I count on the order being like this?

推荐答案

是的，有一个已定义的顺序。在 Postgres 中 doc ，他们给出以下示例：

Yes, there is a defined order. In the Postgres WITH doc, they give the following example:

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS (
        SELECT g.id, g.link, g.data, 1,
          ARRAY[ROW(g.f1, g.f2)],
          false
        FROM graph g
      UNION ALL
        SELECT g.id, g.link, g.data, sg.depth + 1,
          path || ROW(g.f1, g.f2),
          ROW(g.f1, g.f2) = ANY(path)
        FROM graph g, search_graph sg
        WHERE g.id = sg.link AND NOT cycle
)
SELECT * FROM search_graph;

他们在提示框中说的（关于格式）：

About which they say in a Tip box (formatting mine):

递归查询评估算法以
breadth-first 搜索顺序生成输出。通过将外部查询 ORDER BY 设置为路径列$ b $，可以按深度优先
搜索顺序显示结果b用这种方式构造。

The recursive query evaluation algorithm produces its output in breadth-first search order. You can display the results in depth-first search order by making the outer query ORDER BY a "path" column constructed in this way.

在上述情况下，您似乎确实得到了 breadth-first 输出基于 INSERT 语句，因此我想您可以根据需要修改外部的 SELECT 以对其进行排序

You do appear to be getting breadth-first output in your case above based on the INSERT statements, so I would say you could, if you wanted, modify your outer SELECT to order it in another fashion.

我相信您的情况下 depth-first 的类比可能是：

I believe the analog for depth-first in your case would probably be this:

WITH RECURSIVE parent_org (id, parent_id, name) AS (
  SELECT id, parent_id, name
  FROM org
  WHERE id = 3
UNION ALL
    SELECT o.id, o.parent_id, o.name
    FROM   org o, parent_org po
    WHERE  po.parent_id = o.id)
SELECT id, parent_id, name
FROM parent_org
ORDER BY id;

正如我期望的那样（在我的脑海中不断运行），会产生以下结果：

As I would expect (running things through in my head) that to yield this:

0   "top"
1 0 "middle1"
3 1 "bottom3"

这篇关于PostgreSQL递归CTE结果排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！