“recursive” query in postgresql

this is my current query :

SELECT
    id, name,
    CASE 
        when geolocation='' then (select geolocation from location where id = q1.parent_id)
        else geolocation end
FROM location q1;

this is an example of the table on which i make my query and the current result i get:

id | name | geolocation | parent_id 
-- | ---- | ----------- | ---------
 1 | aaaa |   0.0,0.0   |    
 2 | bbbb |   1.1,1.1   |     1
 3 | cccc |             |     1
 4 | dddd |   2.2,2.2   |  
 5 | eeee |             |
 6 | ffff |             |     3 

id | name | geolocation 
-- | ---- | ----------- 
 1 | aaaa |   0.0,0.0   
 2 | bbbb |   1.1,1.1   
 3 | cccc |   0.0,0.0        
 4 | dddd |   2.2,2.2    
 5 | eeee |   
 6 | ffff | 

this is not the result i want to get. what i would like is the request to be “recursive” so that the 6th location gets the geolocation of the “root parent” (in this case location aaaa). so my expected result is :

id | name | geolocation 
-- | ---- | ----------- 
 1 | aaaa |   0.0,0.0   
 2 | bbbb |   1.1,1.1   
 3 | cccc |   0.0,0.0        
 4 | dddd |   2.2,2.2    
 5 | eeee |   
 6 | ffff |   0.0,0.0

i tried to use the “cte method” but i can’t get the result i want…

anyone has an idea?

best regards and have a great day

ps : careful, geolocation is a string

Answer

i tmp.sql

CREATE TABLE wtf
        ( id integer PRIMARY KEY
        , name text
        , geolocation text
        , parent_id integer REFERENCES wtf(id)
        );

INSERT INTO wtf
        ( id , name , geolocation , parent_id ) VALUES
  (1 ,'aaaa' ,'0.0,0.0',NULL)
 , (2 ,'bbbb' ,'1.1,1.1',1)
 , (3 ,'cccc' ,'',1)
 , (4 ,'dddd' ,'2.2,2.2', NULL )
 , (5 ,'eeee' ,'',NULL)
 , (6 ,'ffff' ,'',3 )
        ;

SELECT * FROM wtf;

        -- find the "root" (if any) for all entries
WITH RECURSIVE opa AS (
        SELECT id AS opa
        , id AS moi
        FROM wtf
        WHERE parent_id IS NULL -- The roots
    UNION
        SELECT o.opa AS opa -- inherit the root name from your parent
                ,m.id AS moi
        FROM wtf m -- the descendants
        JOIN opa o ON (m.parent_id = o.moi)
        )
SELECT w.*
        , x.name AS opa_name
        , x.geolocation AS opa_geolocation
FROM wtf w
JOIN opa o ON (w.id = o.moi)
LEFT JOIN wtf x ON (x.id = o.opa)
        ;

---

Results:

---

DROP SCHEMA
CREATE SCHEMA
SET
CREATE TABLE
INSERT 0 6
 id | name | geolocation | parent_id 
----+------+-------------+-----------
  1 | aaaa | 0.0,0.0     |          
  2 | bbbb | 1.1,1.1     |         1
  3 | cccc |             |         1
  4 | dddd | 2.2,2.2     |          
  5 | eeee |             |          
  6 | ffff |             |         3
(6 rows)

 id | name | geolocation | parent_id | opa_name | opa_geolocation 
----+------+-------------+-----------+----------+-----------------
  1 | aaaa | 0.0,0.0     |           | aaaa     | 0.0,0.0
  4 | dddd | 2.2,2.2     |           | dddd     | 2.2,2.2
  5 | eeee |             |           | eeee     | 
  2 | bbbb | 1.1,1.1     |         1 | aaaa     | 0.0,0.0
  3 | cccc |             |         1 | aaaa     | 0.0,0.0
  6 | ffff |             |         3 | aaaa     | 0.0,0.0
(6 rows)