On Aug 20, 2022, at 15:42, Perry Smith <pedz@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
UNION ALL
SELECT parent.id, parent.parent_id, CONCAT(parent.basename, '/', child.basename) FROM dirents parent, pathname child
)
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