This is the second part in a series of blog posts describing PostgreSQL analogs of common Oracle queries
One of the most intricate Oracle specific constructions is "START WITH ... CONNECT BY". According to Oracle's documentation, the syntax is: SELECT [query] [START WITH initial_condition] CONNECT BY [nocycle] condition. This statement is commonly used to traverse hierarchical data in the parent-child order. It's easier to illustrate how it works with an example.
Consider a table that stores opponents moves in a game of chess. Each table row contain coordinates (in algebraic notation) of a single move by whites and the move in response by blacks, as well as a column that references a preceding move, making it possible to keep multiple continuations of a specific move for the post-game analysis.
CREATE TABLE moves(id integer, parent integer, white varchar(10), black varchar(10));
The following statements describe 2 variants of a very short game, the first one leading to the early checkmate (known as a scholar's mate), and the second one to the position where black successfully avoids being checkmated.
INSERT INTO moves VALUES(1, 0, 'e4', 'e5'); INSERT INTO moves VALUES(2, 1, 'Qh5', 'Nc6'); INSERT INTO moves VALUES(3, 2, 'Bc4', 'g6'); INSERT INTO moves VALUES(4, 3, 'Qf3', 'Nf6'); -- checkmate is avoided INSERT INTO moves VALUES(5, 2, 'Bc4', 'Nf6'); INSERT INTO moves VALUES(6, 5, 'Qxf7#', NULL); -- blacks being checkmated
Let's build an Oracle query showing a sequence of moves that leads to the checkmate:
SELECT DISTINCT id AS final_move_id, LTRIM(SYS_CONNECT_BY_PATH(NVL(white,'')||':'||NVL(black,''),';'),';')||';' AS moves, LEVEL AS mate_in FROM moves WHERE white LIKE '%#' OR black LIKE '%#' START WITH id = 1 CONNECT BY PRIOR id = parent;
The query instructs Oracle to look for a checkmate:
As a result, Oracle goes from one row to another only if the parent column of the new row contains the id of the current row, accumulating all visited rows in a result set. The SYS_CONNECT_BY_PATH clause produces a string out of the specified columns of the visited rows, connecting each (parent, child) pair by the designated character (';' in our case).
Being Oracle SQL extension, CONNECT BY is not available in PostgreSQL. Recent versions of PostgreSQL implement Common Table Expressions (CTE), SQL-standard way of dealing with hierarchical data. Here's one possible rewrite of the query above for PostgreSQL using recursive CTEs: