时间戳

首页 > 解决方案 > 通过 CTE 中的数组列查询

postgresql - 通过 CTE 中的数组列查询

问题描述

我有一个带有数组列的 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

我试过取消嵌套——<code>unnest(bubble_ids)——结果是ERROR: record type has not been registered

这样做的正确方法是什么？

标签： postgresqlsubquerycommon-table-expression

解决方案

(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子句而不是 aselect来定义常量值。

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);

推荐阅读