Is there an equivalent of connectby in PostrgreSQL to go up by tree?

Question

I am learning how to work with trees in postgresql12 and found a good function connectby.

For example:

SELECT * FROM connectby('descriptor_value', 'descriptor_value_id', 
    'parent_value_id', '1', 0, '->') 

Gives the following ouput:

However, I don't want to build all tree starting from root, I want to get branch to root starting from node (performance). For example, I want to pass as argument 87 and get 1->86->87. Is there such a function?

Answer 1

This is typically done using a recursive common table expression.

with recursive cte as (
  select descriptor_value_id, parent_value_id, 1 as level
  from descriptor_value
  where descriptor_value_id = 87
  union all
  select p.descriptor_value_id, p.parent_value_id, c.level + 1
  from descriptor_value p 
    join cte c on c.parent_value_id = p.descriptor_value_id
)
select * 
from cte;

---

The connectby() function is pretty much obsolete since the introduction of recursive CTEs in Postgres 8.4