Search for element in array of composite types

Using PostgreSQL 9.0

I have the following table setup

CREATE TABLE person (age integer, last_name text, first_name text, address text);

CREATE TABLE my_people (mperson person[]);

INSERT INTO my_people VALUES(array[ROW(44, 'Jo开发者_高级运维hn', 'Smith', '1234 Test Blvd.')::person]);

Now, i want to be able to write a select statement that can search and compare values of my composite types inside my mperson array column.

Example:

SELECT * FROM my_people WHERE 20 > ANY( (mperson) .age);

However when trying to execute this query i get the following error:

ERROR:  column notation .age applied to type person[], which is not a composite type
LINE 1: SELECT mperson FROM my_people WHERE 20 > ANY((mperson).age);

So, you can see i'm trying to test the values of the composite type inside my array.

I know, i'm not supposed to use arrays and composites in my tables, but this best suites our applications requirements.

Also, we have several nested composite arrays, so a generic solution that would allow me to search many levels would be appreciated.

The construction ANY in your case looks redundant. You can write the query that way:

SELECT * FROM my_people WHERE (mperson[1]).age < 20;

Of course, if you have multiple values in this array, that won't work, but you can't get the exact array element the other way neither.

Why do you need arrays at all? You can just write one element of type person per row.

Check also the excellent HStore module, which might better suit your generic needs.

Temporary test setup:

CREATE TEMP TABLE person (age integer, last_name text, first_name text
                                                     , address text);
CREATE TEMP TABLE my_people (mperson person[]);

-- test-data, demonstrating 3 different syntax styles:
INSERT INTO my_better_people (mperson)
VALUES
   (array[(43, 'Stack', 'Over', '1234 Test Blvd.')::person])

  ,(array['(44,John,Smith,1234 Test Blvd.)'::person,
          '(21,Maria,Smith,1234 Test Blvd.)'::person])

  ,('{"(33,John,Miller,12 Test Blvd.)",
      "(22,Frank,Miller,12 Test Blvd.)",
      "(11,Bodi,Miller,12 Test Blvd.)"}');

Call (almost the solution):

SELECT (p).*
FROM   (
   SELECT unnest(mperson) AS p
   FROM   my_people) x
WHERE  (p).age > 33;

Returns:

 age | last_name | first_name |     address
-----+-----------+------------+-----------------
  43 | Stack     | Over       | 1234 Test Blvd.
  44 | John      | Smith      | 1234 Test Blvd.

• key is the unnest() function, that's available in 9.0.
• Your mistake in the example is that you forget about the ARRAY layer in between. unnest() returns one row per base element, then you can access the columns in the complex type as demonstrated.

Brave new world

IF you actually want a whole people instead of an individual that fits the criteria, I propose you add a primary key to the table and proceed as follows:

CREATE TEMP TABLE my_better_people (id serial, mperson person[]);

-- shortcut to populate the new world by emigration from the old world ;)
INSERT INTO my_better_people (mperson)
SELECT mperson FROM my_people;

Find individuals:

SELECT id, (p).*
FROM  (
   SELECT id, unnest(mperson) AS p
   FROM   my_better_people) x
WHERE  (p).age > 20;

Find whole people (solution):

SELECT *
FROM   my_better_people p
WHERE  EXISTS (
   SELECT 1 
   FROM (
      SELECT id, unnest(mperson) AS p
      FROM   my_better_people
      ) x
   WHERE  (p).age > 20
   AND    x.id = p.id
   );

• You can do it without a primary key, but that would be foolish.