英文:
Create recursive view with WHERE clause
问题
我尝试创建一个用于递归查询的视图，并使用带有 WHERE 子句的视图来设置起始点。以下是您尝试创建视图的代码：
CREATE OR REPLACE VIEW recursive_view AS
WITH recursive Ancestor_Tree (dog_id, parent_id) AS (
SELECT
mp.dog_id as dog_id,
mp.parent_id as parent_id
FROM
dog_parent mp
UNION ALL
SELECT
mp.dog_id,
mp.parent_id
FROM
dog_parent mp,
Ancestor_Tree ft
WHERE mp.dog_id = ft.parent_id
)
SELECT
ft.dog_id,
mm.name AS Member,
ft.parent_id,
mp.name AS Parent
FROM Ancestor_Tree ft
INNER JOIN dog mm
ON mm.id = ft.dog_id
INNER JOIN dog mp
ON mp.id = ft.parent_id
但是，当我使用 SELECT * FROM recursive_view WHERE dog_id = 26 调用它时，我只获得了狗 26 的父级，而没有递归部分。
结果：
| dog_id | member | parent_id | parent |
| ------ | ---------- | --------- | -------------- |
| 26 | Rei | 33 | Beniga |
| 26 | Rei | 34 | Ginga |
您想要创建一个接受 WHERE 子句的递归查询的视图。要实现这一点，您可以将 WHERE 子句放在最外层的 SELECT 语句中，如下所示：
CREATE OR REPLACE VIEW recursive_view AS
WITH recursive Ancestor_Tree (dog_id, parent_id) AS (
SELECT
mp.dog_id as dog_id,
mp.parent_id as parent_id
FROM
dog_parent mp
UNION ALL
SELECT
mp.dog_id,
mp.parent_id
FROM
dog_parent mp,
Ancestor_Tree ft
WHERE mp.dog_id = ft.parent_id
)
SELECT
ft.dog_id,
mm.name AS Member,
ft.parent_id,
mp.name AS Parent
FROM Ancestor_Tree ft
INNER JOIN dog mm
ON mm.id = ft.dog_id
INNER JOIN dog mp
ON mp.id = ft.parent_id
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.
CREATE TABLE dog (
id int,
name varchar(50)
)
CREATE TABLE dog_parent (
id int,
dog_id int,
parent_id int,
)
This recursive query returns what I expect
WITH recursive Ancestor_Tree AS (
SELECT
mp.dog_id,
mp.parent_id
FROM
dog_parent mp
WHERE mp.dog_id = 26
UNION ALL
SELECT
mp.dog_id,
mp.parent_id
FROM
dog_parent mp,
Ancestor_Tree ft
WHERE mp.dog_id = ft.parent_id
)
SELECT
ft.dog_id,
mm.name AS Member,
ft.parent_id,
mp.name AS Parent
FROM Ancestor_Tree ft
INNER JOIN dog mm
ON mm.id = ft.dog_id
INNER JOIN dog mp
ON mp.id = ft.parent_id
Result:
| dog_id | member | parent_id | parent |
| ------ | ---------- | --------- | -------------- |
| 33 | Beniga | 35 | Bunta |
| 33 | Beniga | 36 | Kaori |
| 26 | Rei | 33 | Beniga |
| 34 | Ginga | 37 | Gouzanhaou |
| 34 | Ginga | 38 | Ukigumo |
| 26 | Rei | 34 | Ginga |
| 38 | Ukigumo | 39 | Kumotarou |
| 38 | Ukigumo | 40 | Gintsurugihime |
| 37 | Gouzanhaou | 41 | Gyokuhou |
| 35 | Bunta | 42 | Koharu |
| 35 | Bunta | 43 | Chouhou |
| 43 | Chouhou | 44 | Kotofusa |
| 43 | Chouhou | 45 | Tsubomi |
| 36 | Kaori | 46 | Chacha |
| 46 | Chacha | 47 | Teruhide |
| 46 | Chacha | 48 | Sekihoume |
| 36 | Kaori | 49 | Kokuga |
| 49 | Kokuga | 50 | Kotokaze |
| 50 | Kotokaze | 51 | Seizanhou |
| 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:
SELECT * FROM recursive_view WHERE dog_id = 26
This is how I tried to create the view:
CREATE OR REPLACE VIEW recursive_view AS
WITH recursive Ancestor_Tree (dog_id, parent_id) AS (
SELECT
mp.dog_id as dog_id,
mp.parent_id as parent_id
FROM
dog_parent mp
UNION ALL
SELECT
mp.dog_id,
mp.parent_id
FROM
dog_parent mp,
Ancestor_Tree ft
WHERE mp.dog_id = ft.parent_id
)
SELECT
ft.dog_id,
mm.name AS Member,
ft.parent_id,
mp.name AS Parent
FROM Ancestor_Tree ft
INNER JOIN dog mm
ON mm.id = ft.dog_id
INNER JOIN dog mp
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:
| dog_id | member | parent_id | parent |
| ------ | ---------- | --------- | -------------- |
| 26 | Rei | 33 | Beniga |
| 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 来实现。比如：
CREATE OR REPLACE FUNCTION f_ancestors_of(_dog_id int) -- !!!
RETURNS TABLE (
dog_id int
, member text
, parent_id int
, parent int
)
LANGUAGE sql AS
$func$
WITH RECURSIVE ancestor_tree AS (
SELECT
mp.dog_id,
mp.parent_id
FROM
dog_parent mp
WHERE mp.dog_id = _dog_id -- !!!
UNION ALL
SELECT
mp.dog_id,
mp.parent_id
FROM
dog_parent mp,
ancestor_tree ft
WHERE mp.dog_id = ft.parent_id
)
SELECT
ft.dog_id,
mm.name AS member,
ft.parent_id,
mp.name AS parent
FROM ancestor_tree ft
INNER JOIN dog mm
ON mm.id = ft.dog_id
INNER JOIN dog mp
ON mp.id = ft.parent_id
$func$;
调用：
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:
CREATE OR REPLACE FUNCTION f_ancestors_of(_dog_id int) -- !!!
RETURNS TABLE (
dog_id int
, member text
, parent_id int
, parent int
)
LANGUAGE sql AS
$func$
WITH RECURSIVE ancestor_tree AS (
SELECT
mp.dog_id,
mp.parent_id
FROM
dog_parent mp
WHERE mp.dog_id = _dog_id -- !!!
UNION ALL
SELECT
mp.dog_id,
mp.parent_id
FROM
dog_parent mp,
ancestor_tree ft
WHERE mp.dog_id = ft.parent_id
)
SELECT
ft.dog_id,
mm.name AS member,
ft.parent_id,
mp.name AS parent
FROM ancestor_tree ft
INNER JOIN dog mm
ON mm.id = ft.dog_id
INNER JOIN dog mp
ON mp.id = ft.parent_id
$func$;
Call:
SELECT * FROM f_ancestors_of(26);
Related:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论