Обсуждение: With Recursive / Recursive View question

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;

CREATE RECURSIVE VIEW pathname(id, basename, parent_id, ino, ext, fullpath) AS
     SELECT id, basename, parent_id, ino, ext, basename
     FROM dirents
     WHERE parent_id IS NULL
   UNION ALL
     SELECT child.id, child.basename, child.parent_id, child.ino, child.ext, CONCAT(parent.fullpath, '/', child.basename)
     FROM dirents child, pathname parent
     WHERE parent.id = child.parent_id;

SELECT * FROM pathname WHERE basename = '10732.emlx’;

> On Aug 20, 2022, at 15:42, Perry Smith <pedz@easesoftware.com> wrote:
>
> To rephrase, is it possible to write a view that would work from the child terms out towards the ancestors?

Assuming that the concern is that you want to parameterize this predicate:

        WHERE basename = '10732.emlx'

... you might consider an SQL function taking basename as a parameter.

> On Aug 20, 2022, at 19:38, Christophe Pettus <xof@thebuild.com> wrote:
>
>
>
>> On Aug 20, 2022, at 15:42, Perry Smith <pedz@easesoftware.com> wrote:
>>
>> To rephrase, is it possible to write a view that would work from the child terms out towards the ancestors?
>
> Assuming that the concern is that you want to parameterize this predicate:
>
>         WHERE basename = '10732.emlx'
>
> ... you might consider an SQL function taking basename as a parameter.

Yea.  If I did a function, I would just pass in the id.  I’ve used functions only rarely.  For whatever reason, I’ve
alwaysbeen very skittish around them.  But perhaps I need to grow up. 

Thank you again,
Perry

CREATE OR REPLACE FUNCTION pathname(in_id bigint)

RETURNS character varying AS

$$

DECLARE

  fullpath character varying;

  

BEGIN

  WITH RECURSIVE pathname(id, parent_id, basename) AS (

      SELECT child.id, child.parent_id, child.basename

      FROM dirents child

      WHERE child.id = in_id

    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 INTO fullpath FROM pathname where parent_id IS NULL;

  RETURN fullpath;

END;

$$ LANGUAGE plpgsql;

SELECT pathname(id) FROM dirents WHERE basename = 'OSX';

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!"