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!

Frank Heikens · Accepted Answer · 2012-12-05 08:57:52Z

3

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;

edited Dec 5, 2012 at 8:57

answered Dec 5, 2012 at 8:17

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Miljen Mikic · Accepted Answer · 2012-12-05 07:46:22Z

1

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

edited Dec 5, 2012 at 7:46

answered Dec 5, 2012 at 7:41

Miljen Mikic

15.3k8 gold badges64 silver badges67 bronze badges

2 Comments

Frank Heikens Over a year ago

This is a workaround for older versions, as of 8.4 (released in 2009), PostgreSQL has recursive queries. Much easier and faster.

Miljen Mikic Over a year ago

@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.

Collectives™ on Stack Overflow

postgres SQL, function

2 Answers 2

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Related