Handling recursions
Recursions are an important aspect and are supported by the most advanced SQL database engines, including PostgreSQL. Using recursions, many types of operations can be done fairly easily. So, let us dissect the most simplistic recursion and try to understand how recursion works. Here is an example:
test=# WITH RECURSIVE x(n) AS ( SELECT 1 AS n, 'a'::text AS dummy UNION ALL SELECT n + 1, dummy || 'a' FROM x WHERE n < 5 ) SELECT FROM x; n | dummy ---+------- 1 | a 2 | aa 3 | aaa 4 | aaaa 5 | aaaaa (5 rows)
The goal of this query is to recursively return numbers and compile a string at the end. Basically, the query consists of two parts: the WITH RECURSIVE part and the SELECT statement at the end starting the recursion. While the...