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

[Perry Smith <pedz@xxxxxxxxxxxxxxxx>]

On Aug 20, 2022, at 19:38, Christophe Pettus <xof@xxxxxxxxxxxx> wrote:

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

Attachment: signature.asc
Description: Message signed with OpenPGP