Migrating hierarchical queries from Oracle to PostgreSQL
Posted Thursday Apr 26th, 2012 10:55am
by Alexey Klyukin | Permalink

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;

FINAL_MOVE_ID	MOVES	MATE_IN
6	e4:e5;Qh5:Nc6;Bc4:Nf6;Qxf7#:;	4

The query instructs Oracle to look for a checkmate:

• The search starts at the move with id = 1, as indicated in the START WITH clause, and considers all possible continuations that lead to a checkmate, denoted by the final '#' in the move's description.
• Each move and its direct continuation, for instance, moves 2 and 5 represent the parent-child relationship, described by the PRIOR condition.
• The search depth is stored in the LEVEL pseudo-column.

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: Read more...

---

blog comments powered by Disqus