创建带有WHERE子句的递归视图

huangapple
go评论90阅读模式
英文:

Create recursive view with WHERE clause

问题

我尝试创建一个用于递归查询的视图，并使用带有 WHERE 子句的视图来设置起始点。以下是您尝试创建视图的代码：

  1. CREATE OR REPLACE VIEW recursive_view AS
  2. WITH recursive Ancestor_Tree (dog_id, parent_id) AS (
  3.     SELECT 
  4.         mp.dog_id as dog_id,
  5.         mp.parent_id as parent_id
  6.     FROM
  7.         dog_parent mp
  9.     UNION ALL
  11.     SELECT
  12.         mp.dog_id,
  13.         mp.parent_id
  14.     FROM
  15.         dog_parent mp,
  16.         Ancestor_Tree ft
  17.     WHERE mp.dog_id = ft.parent_id
  18. )
  20. SELECT 
  21.     ft.dog_id,
  22.     mm.name AS Member,
  23.     ft.parent_id,
  24.     mp.name AS Parent
  25. FROM Ancestor_Tree ft
  26. INNER JOIN dog mm
  27.     ON mm.id = ft.dog_id
  28. INNER JOIN dog mp
  29.     ON mp.id = ft.parent_id

但是，当我使用 SELECT * FROM recursive_view WHERE dog_id = 26 调用它时，我只获得了狗 26 的父级，而没有递归部分。

结果：

  1. | dog_id | member     | parent_id | parent         |
  2. | ------ | ---------- | --------- | -------------- |
  3. | 26     | Rei        | 33        | Beniga         |
  4. | 26     | Rei        | 34        | Ginga          |

您想要创建一个接受 WHERE 子句的递归查询的视图。要实现这一点，您可以将 WHERE 子句放在最外层的 SELECT 语句中，如下所示：

  1. CREATE OR REPLACE VIEW recursive_view AS
  2. WITH recursive Ancestor_Tree (dog_id, parent_id) AS (
  3.     SELECT 
  4.         mp.dog_id as dog_id,
  5.         mp.parent_id as parent_id
  6.     FROM
  7.         dog_parent mp
  9.     UNION ALL
  11.     SELECT
  12.         mp.dog_id,
  13.         mp.parent_id
  14.     FROM
  15.         dog_parent mp,
  16.         Ancestor_Tree ft
  17.     WHERE mp.dog_id = ft.parent_id
  18. )
  20. SELECT 
  21.     ft.dog_id,
  22.     mm.name AS Member,
  23.     ft.parent_id,
  24.     mp.name AS Parent
  25. FROM Ancestor_Tree ft
  26. INNER JOIN dog mm
  27.     ON mm.id = ft.dog_id
  28. INNER JOIN dog mp
  29.     ON mp.id = ft.parent_id
  30. WHERE ft.dog_id = 26; -- 将 WHERE 子句移到这里

现在，您可以使用 SELECT * FROM recursive_view WHERE dog_id = 26 来获取递归查询的结果，其中 WHERE 子句将适用于整个视图。

英文:

I am trying to make a view for my recursive query, and use the view with a where clause to set a starting point.

  1. CREATE TABLE dog (
  2. 	id int,
  3. 	name varchar(50)
  4. )
  6. CREATE TABLE dog_parent (
  7. 	id int,
  8. 	dog_id int,
  9. 	parent_id int,
  10. )

This recursive query returns what I expect

  1. WITH recursive Ancestor_Tree AS (
  2.     SELECT 
  3.         mp.dog_id,
  4.         mp.parent_id
  5.     FROM
  6.         dog_parent mp
  7.     WHERE mp.dog_id = 26
  9.     UNION ALL
  11.     SELECT
  12.         mp.dog_id,
  13.         mp.parent_id
  14.     FROM
  15.         dog_parent mp,
  16.         Ancestor_Tree ft
  17.     WHERE mp.dog_id = ft.parent_id
  18. )
  20. SELECT 
  21.     ft.dog_id,
  22.     mm.name AS Member,
  23.     ft.parent_id,
  24.     mp.name AS Parent
  25. FROM Ancestor_Tree ft
  26. INNER JOIN dog mm
  27.     ON mm.id = ft.dog_id
  28. INNER JOIN dog mp
  29.     ON mp.id = ft.parent_id

Result:

  1. | dog_id | member     | parent_id | parent         |
  2. | ------ | ---------- | --------- | -------------- |
  3. | 33     | Beniga     | 35        | Bunta          |
  4. | 33     | Beniga     | 36        | Kaori          |
  5. | 26     | Rei        | 33        | Beniga         |
  6. | 34     | Ginga      | 37        | Gouzanhaou     |
  7. | 34     | Ginga      | 38        | Ukigumo        |
  8. | 26     | Rei        | 34        | Ginga          |
  9. | 38     | Ukigumo    | 39        | Kumotarou      |
  10. | 38     | Ukigumo    | 40        | Gintsurugihime |
  11. | 37     | Gouzanhaou | 41        | Gyokuhou       |
  12. | 35     | Bunta      | 42        | Koharu         |
  13. | 35     | Bunta      | 43        | Chouhou        |
  14. | 43     | Chouhou    | 44        | Kotofusa       |
  15. | 43     | Chouhou    | 45        | Tsubomi        |
  16. | 36     | Kaori      | 46        | Chacha         |
  17. | 46     | Chacha     | 47        | Teruhide       |
  18. | 46     | Chacha     | 48        | Sekihoume      |
  19. | 36     | Kaori      | 49        | Kokuga         |
  20. | 49     | Kokuga     | 50        | Kotokaze       |
  21. | 50     | Kotokaze   | 51        | Seizanhou      |
  22. | 50     | Kotokaze   | 52        | Houki          |

But I want to create a VIEW and replace the WHERE mp.dog_id = 26 with a WHERE for the VIEW like this:

  1. SELECT * FROM recursive_view WHERE dog_id = 26

This is how I tried to create the view:

  1. CREATE OR REPLACE VIEW recursive_view AS
  2. WITH recursive Ancestor_Tree (dog_id, parent_id) AS (
  3.     SELECT 
  4.         mp.dog_id as dog_id,
  5.         mp.parent_id as parent_id
  6.     FROM
  7.         dog_parent mp
  9.     UNION ALL
  11.     SELECT
  12.         mp.dog_id,
  13.         mp.parent_id
  14.     FROM
  15.         dog_parent mp,
  16.         Ancestor_Tree ft
  17.     WHERE mp.dog_id = ft.parent_id
  18. )
  20. SELECT 
  21.     ft.dog_id,
  22.     mm.name AS Member,
  23.     ft.parent_id,
  24.     mp.name AS Parent
  25. FROM Ancestor_Tree ft
  26. INNER JOIN dog mm
  27.     ON mm.id = ft.dog_id
  28. INNER JOIN dog mp
  29.     ON mp.id = ft.parent_id

But when I call it with SELECT * FROM recursive_view WHERE dog_id = 26 I only get the parents for dog 26, but not the recursive part.

Result:

  1. | dog_id | member     | parent_id | parent         |
  2. | ------ | ---------- | --------- | -------------- |
  3. | 26     | Rei        | 33        | Beniga         |
  4. | 26     | Rei        | 34        | Ginga          |

How can I make the VIEW for this recursive query that accepts a WHERE clause?

答案1

得分: 5

对于简单视图，其中输出列可以直接映射到输入列，查询规划器可以将谓词推送到输入表。（这包括所有可更新的视图。）但在这种情况下，输出列 dog_id 无法直接映射到输入列 dog_parent.dog_id，因此这是不可能的。

在你尝试的查询中：

> SELECT * FROM recursive_view WHERE dog_id = 26

... 过滤器 WHERE dog_id = 26 在结果行生成“递归”（实际上是迭代）之后应用于输出列。

VIEW 是你的目标的错误工具。你想要在运行递归项之前应用过滤器到输入列。使用一个返回集的 FUNCTION 或一个 prepared statement 来实现。比如：

  1. CREATE OR REPLACE FUNCTION f_ancestors_of(_dog_id int)  -- !!!
  2.   RETURNS TABLE (
  3.     dog_id int
  4.   , member text
  5.   , parent_id int
  6.   , parent int
  7.   )
  8.   LANGUAGE sql AS
  9. $func$
  10. WITH RECURSIVE ancestor_tree AS (
  11.     SELECT
  12.         mp.dog_id,
  13.         mp.parent_id
  14.     FROM
  15.         dog_parent mp
  16.     WHERE mp.dog_id = _dog_id  -- !!!
  18.     UNION ALL
  19.     SELECT
  20.         mp.dog_id,
  21.         mp.parent_id
  22.     FROM
  23.         dog_parent mp,
  24.         ancestor_tree ft
  25.     WHERE mp.dog_id = ft.parent_id
  26.    )
  27. SELECT
  28.     ft.dog_id,
  29.     mm.name AS member,
  30.     ft.parent_id,
  31.     mp.name AS parent
  32. FROM ancestor_tree ft
  33. INNER JOIN dog mm
  34.     ON mm.id = ft.dog_id
  35. INNER JOIN dog mp
  36.     ON mp.id = ft.parent_id
  37. $func$;

调用：

  1. SELECT * FROM f_ancestors_of(26);

相关链接：

英文:

For simple views, where an output column can be mapped to an input column directly, the query planner can push down predicates to input tables. (That includes all updateable views.) But in this case, the output column dog_id cannot be mapped directly to the input column dog_parent.dog_id, so that's not possible.

In the query you tried:

> SELECT * FROM recursive_view WHERE dog_id = 26

... the filter WHERE dog_id = 26 is applied after result rows have been generated "recursively" (iteratively, really) - to the output column.

A VIEW is the wrong tool for your objective. You want to apply the filter before running the recursive term - to the input column. Use a set-returning FUNCTION or a prepared statement for that. Like:

  1. CREATE OR REPLACE FUNCTION f_ancestors_of(_dog_id int)  -- !!!
  2.   RETURNS TABLE (
  3.     dog_id int
  4.   , member text
  5.   , parent_id int
  6.   , parent int
  7.   )
  8.   LANGUAGE sql AS
  9. $func$
  10. WITH RECURSIVE ancestor_tree AS (
  11.     SELECT
  12.         mp.dog_id,
  13.         mp.parent_id
  14.     FROM
  15.         dog_parent mp
  16.     WHERE mp.dog_id = _dog_id  -- !!!
  18.     UNION ALL
  19.     SELECT
  20.         mp.dog_id,
  21.         mp.parent_id
  22.     FROM
  23.         dog_parent mp,
  24.         ancestor_tree ft
  25.     WHERE mp.dog_id = ft.parent_id
  26.    )
  27. SELECT
  28.     ft.dog_id,
  29.     mm.name AS member,
  30.     ft.parent_id,
  31.     mp.name AS parent
  32. FROM ancestor_tree ft
  33. INNER JOIN dog mm
  34.     ON mm.id = ft.dog_id
  35. INNER JOIN dog mp
  36.     ON mp.id = ft.parent_id
  37. $func$;

Call:

  1. SELECT * FROM f_ancestors_of(26);

Related:

huangapple
  • 本文由 发表于 2023年6月6日 03:03:38
  • 转载请务必保留本文链接：https://go.coder-hub.com/76409294.html
  • family-tree
  • postgresql
  • recursion
  • sql-view
  • supabase
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定