Back to Papers and Articles
PostgreSQL - Recursive table returning functions

A lot of people have asked how you return a table of results in PostgreSQL. In this exercise, we will be using the same data as we did in Using PostgreSQL User-Defined functions to solve the Tree Problem except instead of returning a result that is a string, we will be returning a table. Note this version also assumes PostgreSQL 8 and above so will be using the new standard of \$ quoting.
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 full family tree
Itemnestlevel
Paper0
Recycled1
20 lb2

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_parentidsi_item 1 Paper 21Recycled 3220 lb 4240 lb 51Non-Recycled 6520 lb 7540 lb 85Scraps ```

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 throws 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 iare other Johns and Janes in the halls thinking the same thing.

The function that will return the parental tree of the paper as a table is listed below .

``````
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

Back to Papers and Articles