PostgreSQL now supports recursive queries. This is handy for a few things, most notably recursing graphs.

For instance, assume we create a table accounts that looks something like this:

CREATE TABLE accounts (
  id SERIAL PRIMARY KEY NOT NULL,
  accounts_id INTEGER REFERENCES accounts,
  name TEXT
);

INSERT INTO accounts (accounts_id, name)
VALUES (NULL, 'Account 1');
INSERT INTO accounts (accounts_id, name)
VALUES (NULL, 'Account 2');
INSERT INTO accounts (accounts_id, name)
VALUES (
  (SELECT id FROM accounts WHERE name='Account 1'),
  'Account 3'
);
INSERT INTO accounts (accounts_id, name)
VALUES (
  (SELECT id FROM accounts WHERE name='Account 3'),
  'Account 4'
);


SELECT * FROM accounts;

 id | accounts_id |   name
----+-------------+-----------
  1 |             | Account 1
  2 |             | Account 2
  3 |           1 | Account 3
  4 |           3 | Account 4
(4 rows)

This lets us have accounts that in turn have subaccounts. This type of organization has many applications, such as resellers. The problem is, how to find out things like "what accounts are subaccounts of account x?"

Usually you'd resort to traversal in a procedural language, but with recursive queries you don't have to:

WITH RECURSIVE subaccounts (id) AS (
    SELECT * FROM accounts WHERE accounts_id = 1
  UNION ALL
    SELECT a.* FROM accounts a, subaccounts
    WHERE a.accounts_id = subaccounts.id
)
SELECT id, name, accounts_id
FROM subaccounts
ORDER BY accounts_id, name;

 id |   name    | accounts_id
----+-----------+-------------
  3 | Account 3 |           1
  4 | Account 4 |           3
(2 rows)