postgresql - PL/pgSQL：查询结构与函数结果类型不匹配

Question

我有一张桌子

create table abac_object (
    inbound abac_attribute,
    outbound abac_attribute,
    created_at timestamptz not null default now(),

    primary key (inbound, outbound)
);

abac_attribute是我定义的自定义类型

create type abac_attribute as (
    value text,
    key text,
    namespace_id uuid
);

我尝试创建一些在表格上工作的函数

create function abac_object_list_1(_attr abac_attribute)
returns setof abac_object as $$
    select *
    from abac_object
    where outbound = _attr
$$ language sql stable;

create function abac_object_list_2(_attr1 abac_attribute, _attr2 abac_attribute)
returns setof abac_object as $$
    select t1.*
    from abac_object as t1
    inner join abac_object as t2 on t1.inbound = t2.inbound
    where
        t1.outbound = _attr1
        and t2.outbound = _attr2
$$ language sql stable;

create function abac_object_list_3(_attr1 abac_attribute, _attr2 abac_attribute, _attr3 abac_attribute)
returns setof abac_object as $$
    select t1.*
    from abac_object as t1
    inner join abac_object as t2 on t1.inbound = t2.inbound
    inner join abac_object as t3 on t1.inbound = t3.inbound
    where
        t1.outbound = _attr1
        and t2.outbound = _attr2
        and t3.outbound = _attr3
$$ language sql stable;

create function abac_object_list(_attrs abac_attribute[], _offset integer, _limit integer)
returns setof abac_attribute as $$
begin
    case array_length(_attrs, 1)
        when 1 then return query
            select t.inbound
            from abac_object_list_1(_attrs[1]) as t
            order by t.created_at
            offset _offset
            limit _limit;
        when 2 then return query
            select t.inbound
            from abac_object_list_2(_attrs[1], _attrs[2]) as t
            order by t.created_at
            offset _offset
            limit _limit;
        when 3 then return query
            select t.inbound
            from abac_object_list_3(_attrs[1], _attrs[2], _attrs[3]) as t
            order by t.created_at
            offset _offset
            limit _limit;
        else raise exception 'bad argument' using detail = 'array length should be less or equal to 3';
    end case;
end
$$ language plpgsql stable;

abac_object_list_1工作正常

select *
from abac_object_list_1(('apple', 'type', '00000000-0000-1000-a000-000000000010') ::abac_attribute);

但是，abac_object_list我收到以下错误

select *
from abac_object_list(array [('apple', 'type', '00000000-0000-1000-a000-000000000010')] :: abac_attribute[], 0, 100);

[42804] 错误：查询结构与函数结果类型不匹配详细信息：返回类型 abac_attribute 与第 1 列中的预期类型文本不匹配。其中：PL/pgSQL 函数 abac_object_list(abac_attribute[],integer,integer) 第 4 行，位于 RETURN QUERY

我不明白texttype 是从哪里来的。

这些函数以前有效，但后来我添加了created_at列以便能够添加显式ORDER BY子句。现在我无法正确重写函数。

也可能有更好（或更惯用）的方式来定义这些功能。

你能帮我弄清楚吗？谢谢你。

附言

好吧，如果我写它似乎工作

return query select (t.inbound).*

但我不确定这是否是正确的方法。另外我想知道什么更好用return tableor return setof。

标签： postgresqlplpgsql