Geek Answers Handbook

How to concatenate field values ​​using recursive query in postgresql?

I have a table in a PostgreSQL database that contains parts of addresses in the 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 request to return the address associated with all the names of the ancestors. I need a result table 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 suppose I should use the query WITH RECURSIVE, but all the examples I found use the where clause, so I have to put WHERE name='Office 5'in to get the result only for this particular string. But I need a concatenated address for every row of my initial table. How can I do that?

+4
source share
1

, . , root node, , .

, WHERE, , ID=1 Name=Bld 26. , ( , ), WHERE, .

, , , WHERE ID=1 , . Union'd SELECT , Derived Recursive .

- :

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;

PostgreSQL, , , RDBMS.

+4

More articles:


All Articles