My goal is to write an PostgreSQL view that is able to recursively find all the foreign key relationships so that I can have 1 table to see all the uuid’s and their worksite.
To achieve this, our naming convention to connect foreign key relationships is table name _uuid but I’m happy to change it if we need to.
If I had the following tables:
worksites
uuid
-------
worksite1
tasks
uuid | worksites_uuid
---------------------
1 | worksite1
2 | worksite2
work_permits
uuid | tasks_uuid
---------------------
3 | 1
4 | 2
work_permit_attachments
uuid | work_permits_uuid
---------------------
5 | 4
Then I could create the following view:
uuid | table_name | worksites_uuid
-----------------------------------------------
1 | tasks | worksite1
2 | tasks | worksite1
3 | work_permits | worksite1
4 | work_permits | worksite2
5 | work_permit_attachments | worksite2
Is something like this possible in SQL? Any help or guidance would be greatly appreciated.
