How to concatenate field values with recursive query in postgresql?

Question

I have a table in PostgreSQL database that contains parts of addresses in a form of a tree and looks like this:

Id | Name         | ParentId
1  | London       | 0
2  | Hallam Street| 1
3  | Bld 26       | 2
4  | Office 5     | 3

I would like to make a query to return an address, concatenated from all ancestor names. I need the result table to be like this:

Id | Address
1  | London
2  | London, Hallam Street
3  | London, Hallam Street, Bld 26
4  | London, Hallam Street, Bld 26, Office 5

I guess I have to use WITH RECURSIVE query, but all the examples I've found use the where clause, so I have to put WHERE name='Office 5' to get the result only for that particular row. But I need a concatenated address for each row of my initial table. How can this be done?

Answer 1

The trick with recursive queries is that you need to specify a seed query. This is the query that determines your root node, or the starting point to descend or ascend the tree that you are building.

The reason the WHERE clause is there is to establish the seed ID=1 or Name=Bld 26. If you want every record to have the tree ascended or descended (depending on what you specify in the unioned select), then you should just scrap the WHERE statement so all records are seeded.

Although, the example you give... you might want to start with WHERE ID=1 in the seed, write out the child ID and parent ID. Then in the Union'd SELECT join your derived Recursive table with your table from which you are selecting and join on the Derived Recursive table's Child to your table's parent.

Something like:

WITH RECURSIVE my_tree AS (
  -- Seed

  SELECT 
      ID as Child, 
      ParentID as Parent, 
      Name,
      Name as Address
  FROM <table>
  WHERE <table>.ID = 1


  UNION

  -- Recursive Term
  SELECT 
    table.id as Child, 
    table.parent_id as Parent, 
    table.name,
    t.address || ', ' || table.name as Address

  FROM my_tree as t 
    INNER JOIN <table> table ON
        t.Child = table.Parent_Id  
)
SELECT Child, Address from my_tree;

I've not used PostgreSQL before, so you might have to fuss a bit with the syntax, but I think this is pretty accurate for that RDBMS.