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
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of ChoonSoo Park
Sent: Monday, October 01, 2012 5:50 PM
To: pgsql-general@postgresql.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.
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@yahoo.com> wrote:
Show quoted text
*From:* pgsql-general-owner@postgresql.org [mailto:
pgsql-general-owner@postgresql.org] *On Behalf Of *ChoonSoo Park
*Sent:* Monday, October 01, 2012 5:50 PM
*To:* pgsql-general@postgresql.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.****
** **