subject_type,

subject_id,

subject_relation,

resource_type,

resource_id,

relationship

)as values(

'subject_type1',

'subject_id1',

'subject_relation1',

'resource_type1',

'resource_id1',

'relationship1');

CREATE TABLE type_restrictions (

subject_type,

subject_relation,

resource_type,

relationship

)as values(

'subject_type1',

'subject_relation1',

'resource_type1',

'relationship1');

CREATE VIEW graph AS WITH RECURSIVE

relationship_graph(subject_type, subject_id, subject_relation, resource_type, resource_id, relationship, depth, is_cycle, path)

AS(SELECT r.*

, 0 AS depth

, false AS is_cycle

, ARRAY[r]::relationships[] AS path

FROM relationships AS r

JOIN type_restrictions AS tr

USING(subject_type,resource_type,relationship)

UNION

SELECT g.subject_type

, g.subject_id

, g.subject_relation

, r.resource_type

, r.resource_id

, r.relationship

, g.depth + 1 AS depth

, r = ANY(path) AS is_cycle

, path || r AS path

FROM relationship_graph AS g

JOIN relationships AS r

ON g.resource_type = r.subject_type

AND g.resource_id = r.subject_id

AND g.relationship = r.subject_relation

AND NOT is_cycle )

SELECT * FROM relationship_graph WHERE depth < 3;

select * from graph;