PostgreSQL Recursive Function Not working

560 Views Asked by At

I have a requirement to get the hierarchical structure for the employees.

Below is the current recursive function I am using:

WITH  RECURSIVE resource_tbl AS (
 select pers_id,pers_full_nm,mgr_id,mgr_full_nm, 1 as level   from resource  

   UNION ALL
   select t.pers_id,t.pers_full_nm,t.mgr_id,t.mgr_full_nm, c.level+1   from resource_tbl c  
   INNER JOIN   resource t ON t.mgr_id = c.pers_id
   )
SELECT *
FROM   resource_tbl
ORDER  BY level;

When I run this query I get the below error:

ERROR: RECURSIVE option in WITH clause is not supported SQL state: 0AM00

Does Anyone had this Problem before? The Postgres SQL version is 8.2.

If Version is a Problem then how can I implement in the Current PostgreSQL environment we had?

0

There are 0 best solutions below