Create recursive view with WHERE clause

Question

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?

Erwin Brandstetter · Accepted Answer · 2023-06-06T16:35:12.703

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);

Create recursive view with WHERE clause

1 Answers1