PostgreSQL ltde find all the ancestors of the given label (not the path)

Question

I have a table with a path column in a row. In my hierarchy, the label path is unique, which means that each label has exactly one parent label. In other words, the table should not have two liters that end with the same label.

I have an ltree label, say "C".

I can find all descendant lines of this shortcut with the following query:

select * from myTree where path ~ '*.C.*'; 

This works great and gives the correct subtree.

Now I need to implement a query to find all the ancestral strings of this label. I mean, if there are three rows in the table with the inscriptions "A", "AB", "ABC", I would like to get rows with the paths "A" and "AB" (possibly including "ABC", now it doesn’t have values.

If I know the full path "C" ("ABC" in the above example), the task is easy with the @> operator. However, now I only know "C", and yet I would like to complete the task with a single request. Is there any way to do this?

Answer 1

 SELECT * FROM myTree WHERE path @> ( SELECT path FROM myTree WHERE label = 'C' ); 

Answer 2

Something like that:

 WITH r AS ( SELECT *, row_number() OVER (ORDER BY path) rn FROM myTree ) SELECT * FROM r WHERE rn <= (SELECT rn FROM r WHERE path ~ '*.C'); 

SQL Fiddle

Maybe there is a better way to use embedded material.