Re: With Recursive / Recursive View question

On 2022-08-20 17:42:27 -0500, Perry Smith wrote:
> This select is almost instant:
>
>
>     WITH RECURSIVE pathname(id, parent_id, basename) AS (
>         SELECT child.id, child.parent_id, child.basename
>         FROM dirents child
>         WHERE basename = '10732.emlx'
>       UNION ALL
>         SELECT parent.id, parent.parent_id, CONCAT(parent.basename, '/',
>     child.basename)
>         FROM dirents parent, pathname child
>         WHERE parent.id = child.parent_id
>     )
>     SELECT basename FROM pathname where parent_id IS NULL;
>
>
> Note that the non-recursive term selects the children and the recursion is
> “out” towards the ancestors.
[...]
> To rephrase, is it possible to write a view that would work from the child
> terms out towards the ancestors?

I see that you also have a solution using a function but I thought I
should give it a shot using a view:

create view tree as
    WITH RECURSIVE pathname(id, parent_id, fullpath, leafname) AS (
        SELECT child.id, child.parent_id, child.basename, child.basename
        FROM dirents child
      UNION ALL
        SELECT parent.id, parent.parent_id, CONCAT(parent.basename, '/', child.fullpath), leafname
        FROM dirents parent, pathname child
        WHERE parent.id = child.parent_id
    )
    SELECT * FROM pathname where parent_id is null;

This does functionally do what you want (IIUC):

hjp=> select * from tree where leafname = '10732.emlx';
╔════╤═══════════╤════════════════════════════╤════════════╗
║ id │ parent_id │          fullpath          │  leafname  ║
╟────┼───────────┼────────────────────────────┼────────────╢
║  5 │       (∅) │ home/alice/Mail/10732.emlx │ 10732.emlx ║
╚════╧═══════════╧════════════════════════════╧════════════╝
(1 row)

hjp=> select * from tree where leafname = 'bin';
╔════╤═══════════╤══════════════╤══════════╗
║ id │ parent_id │   fullpath   │ leafname ║
╟────┼───────────┼──────────────┼──────────╢
║  1 │       (∅) │ bin          │ bin      ║
║ 23 │       (∅) │ usr/bin      │ bin      ║
║  5 │       (∅) │ home/bob/bin │ bin      ║
╚════╧═══════════╧══════════════╧══════════╝
(3 rows)

However, to be performant the optimizer would have to recognize that it
can push «leafname = ...» all the way down into the initial subquery of
the recursive query. That's theoretically possible but I would be
surprised if it actually did this. (It didn't in my tests, but my test
data set was too small to get it to even use indexes with normal
queries).

        hp


--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"