From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of ChoonSoo Park
Sent: Monday, October 01, 2012 5:50 PM
To: pgsql-general@xxxxxxxxxxxxxx
Subject: 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.
unnest is the secret to this problem.
I appreciate your help.
-Choon Park
On Mon, Oct 1, 2012 at 6:56 PM, David Johnston <polobo@xxxxxxxxx> wrote:
