select with conditions on array of composite types (postgresql)

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)?

Answers


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.


Need Your Help

ASP.Net MVC Routing URL Generation

c# asp.net asp.net-mvc

Html.ActionLink appends "Index", the action name to the end of the urls, but i would like to not have the index action listed in the default controller url (hope that makes some sense...)

Why do they check WeakReference for null?

android multithreading image android-asynctask weak-references

Here is the blog post on android developers on how download images asynchronously: