postgres SQL, function

Question

I want to create a function which returns how many levels the boss is above the person(calling the function).

Here is the way i would like to do it, but i don't quite know how SQL syntax works http://pastebin.com/dyDaGwf9

the table looks like this:

workerid name chefid
1 Bob 
2 Alice 1
3 Joe 1
4 John 2
5 Dirk 4
6 Ralf 2
7 Lisa 1
8 Lyn 3

the final result upon calling the function should look like this

function call:

Select workerid, name, rankFunction(workerid) from workers;
workerid name rank
1 Bob 0
2 Alice 1
3 Joe 1
4 John 2
5 Dirk 3
6 Ralf 2
7 Lisa 1
8 Lyn 2

Would be great if somebody could shed some light,

Thanks!

Answer 1

You don't need a function for this, just a recursive query (available as of version 8.4):

WITH RECURSIVE chef as (
 SELECT workerid, name, chefid, 0 AS rank FROM workers WHERE chefid is null
 UNION ALL
 SELECT workers.workerid, workers.name, workers.chefid, rank + 1 
 FROM workers JOIN chef ON workers .chefid = chef.workerid 
)
SELECT workerid, name, rank FROM chef ORDER BY workerid;

Answer 2

Here you are, a simple recursion:

CREATE OR REPLACE FUNCTION rankFunction(worker_id int)
 RETURNS int AS
$BODY$
DECLARE
 temp_chefid int;
BEGIN
 temp_chefid := (SELECT chefid from workers where workerid = worker_id);
 IF(temp_chefid IS NULL) THEN
 RETURN 0;
 ELSE RETURN 1 + rankFunction(temp_chefid);
 END IF;
END;
$BODY$
 LANGUAGE plpgsql VOLATILE

• Frank Heikens: This is a workaround for older versions, as of 8.4 (released in 2009), PostgreSQL has recursive queries. Much easier and faster.
• Miljen Mikic: @FrankHeikens The existence of more elegant solution doesn't imply that current answer is wrong, useless etc. which usually should be reason for down-voting. If I were Frank Heikens, I would now down-vote your answer because it doesn't work - there is no column "id". Enjoy.