From: | Perry Smith <pedz(at)easesoftware(dot)com> |
---|---|
To: | Christophe Pettus <xof(at)thebuild(dot)com> |
Cc: | pgsql-general list <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: ***SPAM*** Re: With Recursive / Recursive View question |
Date: | 2022-08-21 02:48:55 |
Message-ID: | 8B83D9FF-E0B0-4CC0-8159-70BE56524D93@easesoftware.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Peter J. Holzer | 2022-08-21 17:03:14 | Re: With Recursive / Recursive View question |
Previous Message | Perry Smith | 2022-08-21 02:02:41 | Re: ***SPAM*** Re: With Recursive / Recursive View question |