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(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"