select with conditions on array of composite types (postgresql)

Question

For example I have type:

CREATE TYPE record AS ( name text, description text, tags text[])

And table:

CREATE TABLE items ( id serial, records record[] )

How can I select all items with records with tags 'test' (without using PL/pgSQL)?

Answer 1

Why does everyone want to shoot themselves in the foot with arrays, hstores, etc? Normalize your data into standard SQL tables. And use advanced features like arrays, hstores when you are programming. But here's a bullet...

Postgres isn't going to like your use of a reserved word as a type.

CREATE TYPE rec AS (name text, description text, tags text[]);
CREATE TABLE items (id int, wreck rec);
INSERT INTO items(1, row('foo','foo description', '{test,testing,tested}')::rec);

SELECT * 
FROM items
WHERE 'test' = ANY ((wreck).tags)

And searching for text in an array, in a composite in another array... well that just boggles the mind. And even if you did figure it out, anyone who came after you trying to maintain your code would be left scratching their heads.