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

> On Aug 20, 2022, at 19:38, Christophe Pettus <xof(at)thebuild(dot)com> wrote:
>
>
>> On Aug 20, 2022, at 15:42, Perry Smith <pedz(at)easesoftware(dot)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.

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