Postgresql query for getting n-level parent-child relation stored in a single table

Question

I have a table denoting parent-child relations. The relations can go n-level deep.

I have created a sample table using the following query:

CREATE SEQUENCE relations_rel_id_seq     INCREMENT BY 1     NO MAXVALUE     NO MINVALUE     CACHE 1; CREATE TABLE relations(     rel_id bigint DEFAULT nextval('relations_rel_id_seq'::regclass) NOT NULL PRIMARY KEY,     rel_name text,     rel_display text,     rel_parent bigint ); 

SQLFiddle

I need to query the table and display the parent-child relations hierarchically. I'm still not getting an idea regarding how to query n-level deep using sql query.

For the sqlfiddle eg, the expected hierarchy of output:

rel1     rel11         rel111         rel112             rel1121 rel2     rel21         rel211         rel212 

N.B: The value n, in n-level is unknown.

DB Design:

Is there any better way such a relation can be expressed in the database for easy querying.?

Answer 1

With Postgres you can use a recursive common table expression:

with recursive rel_tree as (    select rel_id, rel_name, rel_parent, 1 as level, array[rel_id] as path_info    from relations     where rel_parent is null    union all    select c.rel_id, rpad(' ', p.level * 2) || c.rel_name, c.rel_parent, p.level + 1, p.path_info||c.rel_id    from relations c      join rel_tree p on c.rel_parent = p.rel_id ) select rel_id, rel_name from rel_tree order by path_info; 

SQLFiddle based on your example: http://sqlfiddle.com/#!11/59319/19

(I replaced the spaces for indention with underscores as SQLFiddle doesn't display the spaces correctly)