Thought I'd follow up on a previous post regarding recursive queries in PostgreSQL. I had opportunity to use this particular query again, and I thought I'd share a couple small enhancements:

CREATE FUNCTION subaccounts(INTEGER) RETURNS SETOF accounts AS $$

DECLARE
  row accounts%rowtype;

BEGIN
  FOR row IN (
    WITH RECURSIVE subs(id) AS (
      SELECT * FROM accounts WHERE id = $1
    UNION ALL
      SELECT a.* FROM accounts a, subs
      WHERE a.accounts_id = subs.id
    )
    SELECT *
    FROM subs
  ) LOOP RETURN NEXT row;
  END LOOP;
END;

$$ LANGUAGE 'plpgsql';

The key features here are

1. Refactored into a function, so you can pass it an id. This allows you to select any branch from the tree.
2. Included the parent record in the result set. This turns out to be more useful to me.

Here's an example usage scenario:

SELECT * FROM accounts;

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

SELECT * FROM subaccounts(1);

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