Postgres递归CTE循环检测报错:path列含伪类型record[]
Postgres递归CTE循环检测视图创建报错:column "path" has pseudo-type record[]
我在Postgres中定义了一个包含循环检测的递归CTE视图graph,相关表定义如下:
CREATE TABLE relationships ( 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;
执行视图创建语句时,Postgres返回错误:
ERROR: column "path" has pseudo-type record[]
我完全按照Postgres官方文档的循环检测示例实现,本以为ARRAY[ROW]不会被识别为ARRAY[RECORD]类型,代码应该能正常运行,但找不到报错的原因,希望有人能帮忙解决。
内容的提问来源于stack exchange,提问作者Jonathan Whitaker
相关产品推荐
相关产品推荐

