From PostgreSQL 8.4 you can write
WITH queries using the optional
RECURSIVE modifier to make a query refer to its own output.
Ok, but if I’m using a previous version of PostgreSQL? What can I do? I had this same question and, after some research, I found a solution based on functions.
Below I’ll explain how the functions works.
To run this example, first you need a table like this:
CREATE TABLE area ( id bigint NOT NULL, parent bigint, name character varying(50) NOT NULL, CONSTRAINT fk_area FOREIGN KEY (parent) REFERENCES area(id); );
As you can see the table has a column that references to itself, the parent column. For the areas in the top of hierarchy, the parent column should have
After you created the table you can insert some records in the area table:
INSERT INTO area VALUES (1, NULL, 'Area 1'); INSERT INTO area VALUES (2, 1, 'Area 1.1'); INSERT INTO area VALUES (3, 2, 'Area 1.1.1'); INSERT INTO area VALUES (4, 1, 'Area 1.2');
The first function I’ve written is here:
CREATE OR REPLACE FUNCTION area_children (area_id BIGINT, self BOOLEAN) RETURNS SETOF area AS $BODY$ DECLARE r area%ROWTYPE; BEGIN IF self THEN RETURN QUERY SELECT * FROM area WHERE id = area_id; END IF; FOR r IN SELECT * FROM area WHERE parent = area_id LOOP RETURN NEXT r; RETURN QUERY SELECT * FROM area_children(r.id, FALSE); END LOOP; RETURN; END $BODY$ LANGUAGE 'plpgsql';
area_id parameter contains the parent area id, all children of this area in any level of the hierarchy will be retrieved. The second parameter,
self, allows user to define if the first parameter’s area will be returned too. What this function does is:
If the current area should be returned, select its record:
IF self THEN RETURN QUERY SELECT * FROM area WHERE id = area_id; END IF;
For each child of the current area, add it (
NEXT r) to results and retrieves its children calling the function recursively.
FOR r IN SELECT * FROM area WHERE parent = area_id LOOP RETURN NEXT r; RETURN QUERY SELECT * FROM area_children(r.id, FALSE); END LOOP;
To call this function within a
SELECT statement, you can execute this query, where
1 should be a parent area id for which you want the children:
SELECT * FROM area_children(1, FALSE);
Finally I’ve written a second function that overloads the first function to make the second parameter default value to false:
CREATE OR REPLACE FUNCTION area_children (area_id BIGINT) RETURNS SETOF area AS $BODY$ BEGIN RETURN QUERY SELECT * FROM area_children(area_id, TRUE); RETURN; END $BODY$ LANGUAGE 'plpgsql';
You can insert an other main area in your table to check if the function is returning the expected results.
INSERT INTO area VALUES (5, NULL, 'Area 2');
If you run the following query, the Area 2 shouldn’t appear.
SELECT * FROM area_children(1);
If an error is raised when you try to create the functions, check if the
plpgsql function is in
pg_language table. If it’s not present, run the following query:
CREATE LANGUAGE plpgsql;
- PostgreSQL 8.3 Documentation: PL/pgSQL – SQL Procedural Language
- PostgreSQL 8.4 Documentation: WITH Queries
Thanks to VRoy for the SQL Server example.