Recursive Queries in PostgreSQL redux
Filed under: postgresqlThought 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
- Refactored into a function, so you can pass it an id. This allows you to select any branch from the tree.
- 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)






