I have a postgres database. In it is a table called structure.
In the structure table is an ltree field called path. It holds data like the following
f55219.p55468.f55635.f44939.p10900.f45675.f10902
f55219.f55468.p43355.f55340
f55219.f55468.f55635.f44939.p11153.f46590.f11222
f55219.p55468.f43355.f48299.f11620
I'm trying to work out how I query the structure table to find the first p* node from the right hand end, and ideally return that portion of the path. i.e. for the first row, I'd love to get back f55219.p55468.f55635.f44939.p10900.
I've tried things like
select
index ( ST.path, text2ltree('p*'), -1 )
,*
from
structure ST
which throws an error (and even I could make it work, would only return an index value).
In case it matters, there can be any number (0 - infinity) of f* records before the p* records in the data.
Thanks for the help!
If I have understood your question correctly then what you are looking for is if a path is like
f55219.p55468.f55635.f44939.p10900.f45675.f10902
you want
f55219.p55468.f55635.f44939.p10900
Please try:
SELECT
path,
subpath(
path,
0,
COALESCE((
SELECT max(idx)::int
FROM unnest(string_to_array(path::text, '.')) WITH ORDINALITY AS t(label, idx)
WHERE label LIKE 'p%'
), 0)
) AS truncated_path
FROM structure;
path::text
converts the ltree
to a string.
string_to_array(..., '.')
splits the path into labels.
Hope it helps.