Link to home
Start Free TrialLog in
Avatar of itaakashcom
itaakashcom

asked on

How to write PL/SQL function for finding circular references

I have two tables
ID      Task
 1      1
2      2
3      3
4      4
Col1      depend
2      3
2      4
3      1
4      2
ID and Col1 are related through F.K constraint, I want to find out all circular references.
Here ID and Col1 is just for combining rows from 2 tables.
For e.g
Task 1 can start anytime.
Task 2 can start only after completion of 3, 4 etc
1-
2- 3, 4, 1, 2   --- Here for 2 there is circular dependency
3- 1
4-2, 3, 4     --here also circular dependency

Case 2:
Col1      depend
2      3
2      4
3      1
4      5
5      2
ID      Task
 1      1
2      2
3      3
4      4
5      5

1-
2 - 3, 4, 1, 5,2      -- circular reference
3 – 1
4 – 5, 2, 3, 4        -- circular reference
5 - 2, 3 , 4, 5        -- circular reference
Circular references may be available at any recursion level.
How to find such circular references, We have tried recursive  query but we went in infinite loop ..how to write recursive query for this.
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
CONNECT BY is Oracle syntax
PostgreSQL 9.3 uses WITH RECURSIVE syntax with LIMIT clause to catch infinite loops.

Have you studied http://www.postgresql.org/docs/9.3/static/queries-with.html