database - 为什么递归联合不适用于 PostgreSQL 中的复合类型
问题描述
我有一个包含复合类型字段的表。当我尝试对这些字段执行递归联合时,我得到了一个错误。
drop type example_t cascade;
create type example_t as (
value text,
key text
);
drop table if exists example cascade;
create table example (
inbound example_t,
outbound example_t,
primary key (inbound, outbound)
);
create or replace function example_fn(_attrs example_t[])
returns table (attr example_t) as $$
with recursive target as (
select outbound
from example
where array[inbound] <@ _attrs
union
select r.outbound
from target as t
inner join example as r on r.inbound = t.outbound
)
select unnest(_attrs)
union
select * from target;
$$ language sql immutable;
select example_fn(array[('foo', 'bar') ::example_t]);
ERROR: could not implement recursive UNION DETAIL: All column datatypes must be hashable. CONTEXT: SQL function "example_fn" during startup SQL state: 0A000
非递归联合才有效
create or replace function example_fn(_attrs example_t[])
returns table (attr example_t) as $$
select unnest(_attrs)
union
select * from example;
$$ language sql immutable;
select example_fn(array[('foo', 'bar') ::example_t]);
我可以通过这种方式重构我的函数以使其正常工作。但它看起来很奇怪。我的意思是它的可读性较差。有什么办法可以做得更好吗?
create or replace function example_fn(_attrs example_t[])
returns table (attr example_t) as $$
with recursive target as (
select (outbound).value, (outbound).key
from example
where array[inbound] <@ _attrs
union
select (r.outbound).value, (r.outbound).key
from target as t
inner join example as r on r.inbound = (t.value, t.key) ::example_t
)
select (unnest(_attrs)).*
union
select * from target;
$$ language sql immutable;
解决方案
PostgreSQL 黑客邮件列表上有一个线程和 Tom Lane 的简短解释:
一般来说,我们认为数据类型的相等概念可以通过其默认的 btree opclass(支持基于排序的查询算法)或默认的哈希 opclass(支持基于哈希的查询算法)来定义。
普通的 UNION 代码支持排序或散列,但我们还没有支持基于排序的递归 UNION 方法。我不相信这是值得做的......
作为一种解决方法使用union all
:
with recursive target as (
select outbound
from example
where inbound = ('a', 'a')::example_t
union all
select r.outbound
from target as t
inner join example as r on r.inbound = t.outbound
)
select *
-- or, if necessary
-- select distinct *
from target
推荐阅读
- java - 将列表视图滚动到新添加项目的位置
- scala - Apache Flink按键转储到多个文件(组)
- java - 关于滚动条的 AdjustmentListener 行为的问题
- ios14 - Catalyst 上的 SidebarListStyle
- java - 使用 PrintStream 进行日志记录
- php - 在 Woocommerce 的单个产品页面上显示和呈现特定的自定义产品属性到图像
- next.js - SSR getServerSideProps 上的 Next.js graphql 上下文为空 {}
- java - 在 jpa hibernate 表上保存数据在连接列上给出 null
- c - 如何在 C 中播放音效或音乐?
- vuejs2 - 在 REST API 上使用 FilePond 恢复或删除文件,并带有上传图像的动态 url