In many relational database models, we have recursive data structures; it’s true that the relational data model is not oriented to support recursive data structures, and is also true that SQL, (as a query language) doesn’t have a healthy way to manage this recursive structures. However, every database engine has a particular clause to maintain the recursive data structures. In this article, we will see how to create recursive queries in PostgreSQL using TeamSQL.
A Recursive Data Model Example
The simplest data model to show recursive queries is the employee-boss relation. Suppose we have an employee table, and each record has a foreign key pointing to his/her manager’s record. Let’s see the SQL code to create and populate the table in TeamSQL.
The records of Mary Smith and all the people managed directly or indirectly by her.
Well the previous title describe a recursive query, there is not other way to resolve the last question data than a recursive query. By fortune PostgreSQL has a clause “WITH RECURSIVE” which allows to solve this kind of queries. This clause is based on the UNION of two SELECTs. The first one (called Select1 in the below image) is to obtain the first element in the recursive chain. I our example will be the SELECT of the record belonging to Peter Smith.
SELECT * FROM employee WHERE lname = ‘Smith’ AND fname = ‘Peter’;
The second (Select2 in below image) query needs to be connected with the first one using the foreign key. We will ask for records where the manager_id is equal to the emp_id in the first SELECT. Let’s see how we will connect
How many times is the second query executed?
This is an interesting point, and is key to understand how the recursive queries work. The second query is executed many times. How many ? We don’t know “a priori”, but we can say that while the second query return at least one row, will be executed again, and the result will be UNIONed with the previous result (here is the recursion concept). When the second query doesn’t return any record, the whole query will be considered complete, and finished.
From Employee to CEO chain
In this example, we are going to use a recursive query but in opposite order. We will start with one specific employee, and go up in the hierarchy until reaching the CEO. Due to in this particular data model, the CEO is marked as manager of the CEO, and this fact can have a negative impact on our query let’s see
The previous query never ends, because as the CEO is his own manager, the query fall in a forever loop. So, we need to stop one level before the CEO level, because if we reach CEO level, the recursive query will never stops. In order to stop one level before, we add a condition asking for employee that are not his own managers. (condition e.emp_id <> e.manager_id) in the following image. However, we will need to add with an extra UNION the record of the manager. Let’s see the final query:
We show several recursive queries using the PostgreSQL clause “WITH RECURSIVE”. We cover the sub parts of this clause, and explained how all these parts work together. We even show a failing recursive query (a non ending query) and we fixed it. SQL has a lot of particular and not very used clauses, learning these very specific clauses will give you a deeper experience with the language. Go deeper, Be a TeamSQL HERO!