通过CTE中的数组列进行查询

Question

我有一个CTE，它有一个数组列，并希望通过该id数组过滤另一列。

with vars as (
select
(1, 7, 10000) bubble_ids,
'Frank' name
)
select * from walruses
inner join tusks on walruses.id = tusks.walrus_id
where (
name = (select name from vars)
and tusks.bubble_id IN (select bubble_ids from vars)
);

这会产生以下内容：ERROR: operator does not exist: integer = record

我尝试过取消测试——unnest(bubble_ids)——这会导致ERROR: record type has not been registered

做这件事的正确方法是什么？

Answer 1

(1, 7, 10000)不定义数组。这是一个行构造函数，它创建一个匿名记录(有三个字段(。

数组文字是用方括号写的，并且必须以关键字array为前缀，例如array[1, 7, 10000]。或者，您可以将其写为字符串值：'{1,7,1000}'

当使用CTE作为变量/参数的"容器"时，我通常使用交叉连接使其可用于查询。这是较少键入

with vars as (
select
array[1, 7, 10000] bubble_ids,
'Frank' as name --<< you need the AS, because name is a keywod
)
select * 
from walruses
inner join tusks on walruses.id = tusks.walrus_id
cross join vars v
where name = v.vars
and tusks.bubble_id = any(v.bubble_ids);

---

我更喜欢values子句而不是select来定义常数值。

with vars(bubble_ids, name) as (
values (array[1, 7, 10000], 'Frank')
)
select * 
from walruses
inner join tusks on walruses.id = tusks.walrus_id
cross join vars v
where name = v.vars
and tusks.bubble_id = any(v.bubble_ids);