In this post, we are going to explain in brief about what a hierarchical query is in Oracle and then how to write such queries in PostgreSQL.
Hierarchical query
Hierarchy is based on relationship with a parent-child in the table or view. It mandates that each child can have only one parent, whereas a parent can have multiple children. This is very useful when trying to build reporting queries. A hierarchical query displays organized rows in a tree structure, so in order to retrieve the data it has to be traversed starting from the root.
Hierarchical query in Oracle
Hierarchical queries make use of the following syntax, keywords, and clauses:
CONNECT BY: Defines the relationship between parent and child. This is a mandatory clause.
PRIOR: Indicate the parent.
START WITH: Defines the records where we want the query to start.
LEVEL: A pseudocolumn that indicates the level of hierarchy or row position.
connect_by_root: Basically returns the rows using data from the root row.
ORDER SIBLINGS BY: Applies the ORDER BY clause to the siblings rows.
Example
To understand how a hierarchical query works, let’s go through an example in Oracle.
To start, create a dummy table:
create table dummy_table(
emp_no int,
ename char(5),
job char(9),
manager_no int
);
Insert data into “dummy_table”:
insert into dummy_table values(10,'A1','CEO',null);
insert into dummy_table values(11, 'B1', 'VP', 10);
insert into dummy_table values(12, 'B2', 'VP', 10);
insert into dummy_table values(13, 'B3', 'VP', 10);
insert into dummy_table values(14, 'C1', 'DIRECTOR', 13);
insert into dummy_table values(15, ‘C2’, ‘DIRECTOR’, 13);
insert into dummy_table values(16, 'D1', 'MANAGER', 15);
insert into dummy_table values(17 ,'E1', 'ENGINEER', 11);
insert into dummy_table values(18, 'E2', 'ENGINEER', 11);
If we look at the tree structure of this table, we can easily identify who reports to whom:
A1 is the boss (root). This position doesn’t report to any one, but B1, B2, B3 (parents and children) report to it.
E1 and E2 report to B1. D1 reports to C2, and C2 and C1 report to B3 .
Displaying the levels of the records
We can make a simple hierarchical query to see the levels of the records in “dummy_table”:
SQL> SELECT emp_no,ename,job,level
FROM dummy_table
CONNECT BY PRIOR emp_no = manager_no
START WITH manager_no IS NULL
order by level ;
EMP_NO ENAME JOB LEVEL
---------- ----- --------- -----------------
10 A1 CEO 1
11 B1 VP 2
12 B2 VP 2
13 B3 VP 2
15 C2 DIRECTOR 3
14 C1 DIRECTOR 3
18 E2 ENGINEER 3
17 E1 ENGINEER 3
16 D1 MANAGER 4
9 rows selected.
The LEVEL pseudocolumn returns the current depth in the tree, starting with 1. A1 is the root and has level=1.
Hierarchical query in PostgreSQL
We can create the same hierarchical query in PostgreSQL. Using best practices, there are two possible ways to do this:
1. With the help of common table expressions (CTE):
postgres=# WITH RECURSIVE cte AS (
SELECT emp_no, ename, manager_no, 1 AS level FROM dummy_table
where manager_no is null
UNION ALL
SELECT e.emp_no, e.ename, e.manager_no, c.level + 1 FROM cte c
JOIN dummy_table e ON e.manager_no = c.emp_no
)
SELECT *
FROM cte;
emp_no | ename | manager_no | level
--------+-------+------------+-------
10 | A1 | | 1
11 | B1 | 10 | 2
12 | B2 | 10 | 2
13 | B3 | 10 | 2
14 | C1 | 13 | 3
17 | E1 | 11 | 3
18 | E2 | 11 | 3
15 | C2 | 13 | 3
16 | D1 | 15 | 4
(9 rows)
postgres=#
Please note the PostgreSQL terms in this query:
“WHERE manager_no …” is equivalent to the Oracle command “START WITH”
“JOIN dummy_table ON …” is equivalent to the Oracle command “CONNECT BY.”
2. Installing the extension tablefunc:
The tablefunc extension is a contrib module that resides under the contrib/ folder it PostgreSQL sources.This extension supports equivalent functions to CONNECT BY and STARTS WITH, as well as a LEVEL keyword, but the syntax is different from Oracle.
Create extension tablefunc:
postgres=# CREATE EXTENSION tablefunc;
CREATE EXTENSION
postgres=#
The connectby function, from which a hierarchical tree structure can be created, has the following structure:
connectby(text relname, text keyid_fld, text parent_keyid_fld [, text orderby_fld ], text start_with, int max_depth [, text branch_delim ])
These are the parameters of the connectby function:
relname: Defines the name of the source relation (table)
keyid_fld: Name of the key field
parent_keyid_fld: Name of the parent-key field
orderby_fld: Name of the field to order siblings by (optional)
start_with: Value of the row where the query should start
max_depth: Max depth, or zero for unlimited depth
branch_delim: String to separate keys with in branch output (optional)
Example
We can use the connectby function to display data in a hierarchical way:
postgres=# SELECT * FROM connectby('dummy_table', 'emp_no', 'manager_no', '10', 0, '->') AS t(emp_no int, manager_no int, level int, ord text) order by emp_no;
emp_no | manager_no | level | ord
--------+------------+-------+----------------
10 | | 0 | 10
11 | 10 | 1 | 10->11
12 | 10 | 1 | 10->12
13 | 10 | 1 | 10->13
14 | 13 | 2 | 10->13->14
15 | 13 | 2 | 10->13->15
16 | 15 | 3 | 10->13->15->16
17 | 11 | 2 | 10->11->17
18 | 11 | 2 | 10->11->18
(9 rows)
postgres=#
Ordering by siblings
Let’s say we have the following query using ORDER BY in Oracle:
SQL>SELECT emp_no,ename,job,level
FROM dummy_table
CONNECT BY PRIOR emp_no = manager_no
START WITH manager_no IS NULL
order siblings by emp_no ;
In PostgreSQL, the query can be rewritten like this:
postgres=# SELECT * FROM connectby('dummy_table', 'emp_no', 'manager_no', 'emp_no', '10', 0,'->') AS t(emp_no int, manager_no int, level int, branch text, ord int);
emp_no | manager_no | level | branch | ord
--------+------------+-------+-------------+------------------------
10 | | 0 | 10 | 1
11 | 10 | 1 | 10->11 | 2
17 | 11 | 2 | 10->11->17 | 3
18 | 11 | 2 | 10->11->18 | 4
12 | 10 | 1 | 10->12 | 5
13 | 10 | 1 | 10->13 | 6
14 | 13 | 2 | 10->13->14 | 7
15 | 13 | 2 | 10->13->15 | 8
16 | 15 | 3 | 10->13->15->16 | 9
(9 rows)
Hope it helps!