如何编写复合类型的where语句？

问题描述

假设我创建了一个类型，例如

Lets say I create a type such as

CREATE TYPE testpair AS (key int, value int);

并放在表格中

create table test(id SERIAL PRIMARY KEY, a testpair[]);

具有这些测试值

insert into test(a) values (ARRAY[(1,2)::testpair]), (ARRAY[(5,9)::testpair]), (ARRAY[(4,16)::testpair]), (ARRAY[(9, 1)::testpair]);

如果我写这个，我将会得到结果

I will get a result if I write this

select * from test where (4,16)::testpair = ANY(a);

如果我想知道这些对中的任何一个是否以4开头或> = 9 的所有对？我知道我可以写 select（（5,8）:: testpair）.key; 来获取5，但以下错误

How do I write my query if I want to know if any of these pairs start with 4 or for all pairs that are >= 9? I know I can write select ((5,8)::testpair).key; to get 5 but the following is wrong

select * from test where 5  = ANY((a).key);

给出的错误是

ERROR:  column notation .key applied to type testpair[], which is not a composite type

很有意义，因为 a 是一个数组。但是我不知道如何编写查询以使用任何键

which makes sense since a is an array. But I have no idea how to write the query to use any 'key'

推荐答案

您可以使用 unnest 函数，该函数基本上将数组转换为表：

You can use the unnest function, which basically converts an array to a table:

select * from test where exists(select 1 from unnest(a) as t where (t).key=5);

这篇关于如何编写复合类型的where语句？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！