From: [email protected]
[mailto:[email protected]] On Behalf Of ChoonSoo Park
Sent: Monday, October 01, 2012 5:50 PM
To: [email protected]
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.
