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.subject_type, r.subject_id, r.subject_relation,

r.resource_type, r.resource_id,

r.relationship,

0,

false,

ARRAY[ROW(r.resource_type, r.resource_id, r.relationship, r.subject_type, r.subject_id, r.subject_relation)]

FROM relationships r

INNER JOIN type_restrictions tr

ON

r.subject_type = tr.subject_type AND

r.resource_type = tr.resource_type AND

r.relationship = tr.relationship

UNION

SELECT

g.subject_type,

g.subject_id,

g.subject_relation,

r.resource_type,

r.resource_id,

r.relationship,

g.depth + 1,

ROW(r.resource_type, r.resource_id, r.relationship, r.subject_type, r.subject_id, r.subject_relation) = ANY(path),

path || ROW(r.resource_type, r.resource_id, r.relationship, r.subject_type, r.subject_id, r.subject_relation)

FROM relationship_graph g, relationships r

WHERE

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;