Re: How to search for composite type array

Thank you David,

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(at)yahoo(dot)com> wrote:

> *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.****
>
> ** **
>