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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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