You need to enable JavaScript to run this app.
优惠活动
大模型
产品
解决方案
定价
更多

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

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.06.14 04:50:06