UPDATE: This artilce ws written for PostgreSQL 7.4. We have a newer version of this article for PostgreSQL 8.4. Using Recursive Common table expressions to represent Tree structures

In this example we will tackle a different recursive example instead of the who reports to who problem that we had in SQL Server article. We leave it up to the reader as an exercise to apply the same apporach to solve the who reports to who problem.

The Problem Suppose you are tracking supplies and have a field called si_item and another called si_parentid. The parent keeps track of what subclass a supply item belongs to. E.g. you have paper parent that has subclasses such as recycled, non-recycled. When someone takes supplies, you want to return the fully qualified name e.g. Paper->Recycled->20 Lb

Below is what the structure of your table looks like.

si_id int, si_parentid int, si_item. In your table are the following entries

si_id	si_parentid	si_item
1		Paper
2	1	Recycled
3	2	20 lb
4	2	40 lb
5	1	Non-Recycled
6	5	20 lb
7	5	40 lb
8	5	Scraps

Now everyone at work is asked to throw all their paper in the recycle bin for recycling. With recycling, we are told we will save trees and have plentiful amounts of paper. John loves his paper and doesn't want it to be recycled. He surely doesn't want his beautiful paper to get into someone else's hands. So what he does is wrap his paper in styrofoam and throw it in the recycle bin. He thinks (Hee hee - that'll teach them to ask me to give up my paper). Little does John know that there is are other Johns and Janes in the halls thinking the same thing.

The function that will display the fully qualified name is below . The function looks as follows

CREATE FUNCTION cp_getitemfullname(int8) RETURNS varchar AS 'DECLARE
itemid ALIAS FOR $1;
itemfullname varchar(255);
itemrecord RECORD;
BEGIN
    SELECT s.* INTO itemrecord FROM supplyitem s  where si_id=itemid;
     itemfullname := itemfullname + itemrecord.si_item;
     IF itemrecord.si_parentid IS NOT NULL  THEN
           itemfullname := cp_getitemfullname(itemrecord.si_parentid) + ''->'' + itemfullname ;
           RETURN itemfullname;
     ELSE
           RETURN itemfullname;
     END IF;
END'  LANGUAGE 'plpgsql'

To figure out what paper John will ask for SELECT cp_getitemfullname(8) As thename This returns Paper->Non-Recycled->Scraps

Articles of Interest
Solving Nearest Neighbor with a recursive PostgreSQL SQL function	Demonstrates how to return sets with an SQL function (rather than PGSQL) and how to write a recursive SQL function.