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?
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: