With Recursive / Recursive View question

[Perry Smith]

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.

This select doesn’t complete before I get impatient:

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

In this case, the non-recursive term starts at the top of the directory trees 
and the recursion works “in” towards the children.

I’m not surprised that the first is fast and the second is very slow.  My 
problem is I currently have a file called recurse.sql which is the top query.  
I go in and edit that file and then execute it via psql -f recurse.sql.  What 
I’m attempting to do in the second example is to create a view and then use 
select on the view to select the rows that I’m looking for.

To rephrase, is it possible to write a view that would work from the child 
terms out towards the ancestors?

Thank you for your time,
Perry



signature.asc
Description: Message signed with OpenPGP

Re: With Recursive / Recursive View question

[Christophe Pettus]

> On Aug 20, 2022, at 15:42, Perry Smith  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.

Re: ***SPAM*** Re: With Recursive / Recursive View question

[Perry Smith]

> On Aug 20, 2022, at 19:38, Christophe Pettus  wrote:
> 
> 
> 
>> On Aug 20, 2022, at 15:42, Perry Smith  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 always been very skittish around them.  
But perhaps I need to grow up.

Thank you again,
Perry



signature.asc
Description: Message signed with OpenPGP

Re: ***SPAM*** Re: With Recursive / Recursive View question

[Perry Smith]

> On Aug 20, 2022, at 19:38, Christophe Pettus  wrote:
> 
> 
>> On Aug 20, 2022, at 15:42, Perry Smith  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.

That wasn’t so bad…

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

Thank you … again! :-)
Perry



signature.asc
Description: Message signed with OpenPGP