Re: How to search for composite type array

From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of ChoonSoo Park
Sent: Monday, October 01, 2012 5:50 PM
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] How to search for composite type array

Hello postgresql gurus,

I want to have an array of composite type in a table. Retrieving/saving
value work fine.

I just wonder how I can search against composite type array.

CREATE TYPE CompXYZ AS (

attr1 integer,

attr2 text,

attr3 inet

);

CREATE TABLE sample (

id integer not null primary key,

list CompXYZ[]

);

insert into sample values (1, '{"(1,abc,127.0.0.1)", "(5,def,10.0.1.2)"}');

insert into sample values (2, '{"(10,hello,127.0.0.1)",
"(20,def,10.0.1.2)"}');

insert into sample values (3, '{"(20,hello,10.1.1.1)",
"(30,there,10.1.1.2)"}');

How I can search a row containing "hello" for attr2?

I know if I have a separate table for saving 3 attributes along with foreign
key to sample table, then I can achieve my goal.

I just want to know if there is a way to do the same thing using composite
array.

Thank you,

Choon Park

============================================================================
=

SELECT * FROM sample WHERE id IN (

SELECT id FROM (

SELECT id, unnest(list) AS list_item FROM sample

) explode --need to unnest the array so you can address individual parts of
the composite type in the where clause

WHERE (explode.list_item).text = 'hello' -note the () are required around
(table.column), even if table is omitted; i.e., "(list_item).text"

) --/IN

In may be worth it to define a

text = CompXYZ

custom equality function+operator then you could do this (in theory.):

. WHERE 'hello' = ANY(list)

Your main issue is that the ANY/ALL array operators operating on whole
elements.

Tweak the above to output whatever specific data you need as written it
outputs a single record from sample if any of the contained array elements
matches.

David J.