1

Given the table: 

CREATE TABLE self-ref (
  name VARCHAR NOT NULL,
  parent VARCHAR REFERENCES self-ref(name),

  PRIMARY KEY(name)
);

If I get a name from the table (and thus the Pkey), how can I get the name of it's "root"-level parent: the name where parent is NULL which is referenced to (indirectly) by the given name.

Say I have these values (I don't know which values I will get):

+-------------+-----------+
| Name        | Parent    |
+-------------+-----------+
| Root 1      | NULL      |
| Root 2      | NULL      |
| Child 1     | Root 1    |
| Child 2     | Root 2    |
| Sub-child 1 | Child 1   |
+-------------+-----------+

The function/whatever solution should return Root 1 for Root 1, Child 1 and Sub-child 1; and should return Root 2 for Root 2 and Child 2.

How can I achieve this? Note: It really has to be Postgres....

TobiSH
  • 2,833
  • 3
  • 23
  • 33
Jonas
  • 35
  • 1
  • 6
  • You need a recursive hierarchical query to handle this requirement. – Tim Biegeleisen Nov 16 '19 at 10:04
  • @TimBiegeleisen could explain a bit more, please? (Edit: I'm not quite used to this kind of puzzles to solve ...) – Jonas Nov 16 '19 at 10:10
  • See [Recursive CTE concatenate fields with parents from arbitrary point.](https://stackoverflow.com/a/53530450/1995738) – klin Nov 16 '19 at 10:12
  • Thank you @klin. How to compare the result of this query (a table-like structure) to a single VARCHAR value? As in, I need to if the value is 'inherited' from a certain root. – Jonas Nov 16 '19 at 10:16
  • Perhaps you can use a closure table - like I did (https://stackoverflow.com/a/38701519/5962802) Closure table allows a lot of freedom in your queries - but of course takes additional space. – IVO GELOV Nov 16 '19 at 12:14

1 Answers1

3

You need a recursive CTE. The syntax looks like:

with recursive cte as (
      select name, name as parent
      from selfref
      where parent is null
      union all
      select s.name, cte.parent
      from cte join
           selfref s
           on s.parent = cte.name
     )
select *
from cte;

Here is a db<>fiddle.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786