From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of ChoonSoo Park 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. |
