[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: ***SPAM*** Re: With Recursive / Recursive View question
From: Perry Smith <pedz () easesoftware ! com>
Date: 2022-08-21 2:48:55
Message-ID: 8B83D9FF-E0B0-4CC0-8159-70BE56524D93 () easesoftware ! com
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
> On Aug 20, 2022, at 19:38, Christophe Pettus <xof@thebuild.com> wrote:
>
>
> > 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
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 #5 (unknown)]
<html><head><meta http-equiv="Content-Type" content="text/html; \
charset=utf-8"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; \
line-break: after-white-space;" class=""><br class=""><div><blockquote type="cite" \
class=""><div class="">On Aug 20, 2022, at 19:38, Christophe Pettus <<a \
href="mailto:xof@thebuild.com" class="">xof@thebuild.com</a>> wrote:</div><br \
class="Apple-interchange-newline"><div class=""><div class=""><br \
class=""><blockquote type="cite" class="">On Aug 20, 2022, at 15:42, Perry Smith \
<<a href="mailto:pedz@easesoftware.com" class="">pedz@easesoftware.com</a>> \
wrote:<br class=""><br class="">To rephrase, is it possible to write a view that \
would work from the child terms out towards the ancestors?<br \
class=""></blockquote><br class="">Assuming that the concern is that you want to \
parameterize this predicate:<br class=""><br class=""><span class="Apple-tab-span" \
style="white-space:pre"> </span> WHERE basename = '10732.emlx'<br \
class=""><br class="">... you might consider an SQL function taking basename as a \
parameter.</div></div></blockquote></div><br class=""><div class="">That wasn't so \
bad…</div><div class=""><br class=""></div><blockquote style="margin: 0 0 0 40px; \
border: none; padding: 0px;" class=""><div class=""><font face="Courier New" \
class=""><span style="font-style: normal;" class="">CREATE OR REPLACE FUNCTION \
pathname(in_id bigint)</span></font></div><div class=""><font face="Courier New" \
class=""><span style="font-style: normal;" class="">RETURNS character varying \
AS</span></font></div><div class=""><font face="Courier New" class=""><span \
style="font-style: normal;" class="">$$</span></font></div><div class=""><font \
face="Courier New" class=""><span style="font-style: normal;" \
class="">DECLARE</span></font></div><div class=""><font face="Courier New" \
class=""><span style="font-style: normal;" class=""> fullpath character \
varying;</span></font></div><div class=""><font face="Courier New" class=""><span \
style="font-style: normal;" class=""> </span></font></div><div \
class=""><font face="Courier New" class=""><span style="font-style: normal;" \
class="">BEGIN</span></font></div><div class=""><font face="Courier New" \
class=""><span style="font-style: normal;" class=""> WITH RECURSIVE \
pathname(id, parent_id, basename) AS (</span></font></div><div class=""><font \
face="Courier New" class=""><span style="font-style: normal;" class=""> \
SELECT <a href="http://child.id" class="">child.id</a>, child.parent_id, \
child.basename</span></font></div><div class=""><font face="Courier New" \
class=""><span style="font-style: normal;" class=""> FROM dirents \
child</span></font></div><div class=""><font face="Courier New" class=""><span \
style="font-style: normal;" class=""> WHERE <a \
href="http://child.id" class="">child.id</a> = in_id</span></font></div><div \
class=""><font face="Courier New" class=""><span style="font-style: normal;" \
class=""> UNION ALL</span></font></div><div class=""><font face="Courier \
New" class=""><span style="font-style: normal;" class=""> SELECT \
<a href="http://parent.id" class="">parent.id</a>, parent.parent_id, \
CONCAT(parent.basename, '/', child.basename)</span></font></div><div class=""><font \
face="Courier New" class=""><span style="font-style: normal;" class=""> \
FROM dirents parent, pathname child</span></font></div><div class=""><font \
face="Courier New" class=""><span style="font-style: normal;" class=""> \
WHERE <a href="http://parent.id" class="">parent.id</a> = \
child.parent_id</span></font></div><div class=""><font face="Courier New" \
class=""><span style="font-style: normal;" class=""> )</span></font></div><div \
class=""><font face="Courier New" class=""><span style="font-style: normal;" \
class=""> SELECT basename INTO fullpath FROM pathname where parent_id IS \
NULL;</span></font></div><div class=""><font face="Courier New" class=""><span \
style="font-style: normal;" class=""> RETURN fullpath;</span></font></div><div \
class=""><font face="Courier New" class=""><span style="font-style: normal;" \
class="">END;</span></font></div><div class=""><font face="Courier New" \
class=""><span style="font-style: normal;" class="">$$ LANGUAGE \
plpgsql;</span></font></div><div class=""><font face="Courier New" class=""><span \
style="font-style: normal;" class=""><br class=""></span></font></div><div \
class=""><font face="Courier New" class=""><span style="font-style: normal;" \
class="">SELECT pathname(id) FROM dirents WHERE basename = \
'OSX';</span></font></div></blockquote><div class=""><br class=""></div><div \
class="">Thank you … again! :-)</div><div class="">Perry</div><div class=""><br \
class=""></div></body></html>
["signature.asc" (signature.asc)]
-----BEGIN PGP SIGNATURE-----
iQIzBAEBCAAdFiEE5yOa/gCtQpb3oCpljxzk9yzE+MAFAmMBnRcACgkQjxzk9yzE
+MC6yw//femBr9zggmfgtlKZAyfeQ3BfR+OGg48/ZctGygeFY8YcuJDP1sqrlpgK
dp5YMS8spJCEXbDQFYWKltygG8ZedXNgzJzndz/hOEovhtgRi5ZY9vSgvyCfiJEs
+Hw3aUbkIYTKGZlJ4yMibLm1GOziKN35+g4/Hb/K7QNqcciYmrI5qOdMud+AdYiU
SiHAJPeq7807G88RFTf9F+w1scd+xpBywvc/hiNyyvicwA/HF0armaeFYR7NpbMh
mKQiXFNZLuFsdY3RdA8pi4xe2pNv3MYSknANvg9q9GIBbxPAMSIRDrk3r3nG3Ozd
BgNfhSt38tmmDkld75Prb16gw+AjFvn9YtrOKoBMTxevpKtaGqurBiEdqRbnlpnO
oc9/kSc68C84Pi9uVtRNJp+63znHBo4+SYQDUfjz2hxHHPDAdU7spavrEAXeyx1U
ttavx8YNV8hvyOPyD2ARXAeXdvCt23G2quNJC+bISlq1T6kBRvTr50LHXwSh16yb
52sdXipEcrR78CTa6oplnKHdsA8nymrL/P/ZB3O/t9XxUPlXa//zjvd0HzwNubzd
62rhXdd2fsvYK5xy3KG5bNDQ5aZQ+FEgACbPDOjMY+MUUBb0nYXbLVRLvZOsHDdx
bROR2+lwklpvBeNx5FPNeuBkDYQof8uV2ggPpJqqCzFvmWnlUaw=
=tPO7
-----END PGP SIGNATURE-----
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic