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