PostgreSQL select for arrays of composite elements

Just the usual array access syntax followed by the usual composite type access syntax. So a bit of setup for demonstration purposes:

=> insert into footable (x, y) values(1, ARRAY[(1.0,2.0)::footype]);=> select * from footable; x |     y     ---+----------- 1 | {"(1,2)"}(1 row)

And then:

=> select y[1].a, y[1].b from footable where x = 1; a | b ---+--- 1 | 2(1 row)

You can also access the composite type inside a WHERE clause:

=> select * from footable where y[1].b < 3; x |         y         ---+------------------- 1 | {"(1,2)"}(1 row)

First of all, there is one mistake, the word 'as' is wrong in the create table query. You should rather write this:

create table footable (    x integer,      y footype []);

Insert data into the table:

insert into footable(x, y) values(10, ARRAY[ ROW(1.0,2.0), ROW(3,4)]::footype[] );# select * from footable; x  |         y         ----+------------------- 10 | {"(1,2)","(3,4)"}(1 row)

Get the data by the fields:

# select x, y[1], y[1].a, y[1].b from footable; x  |   y   | a | b ----+-------+---+--- 10 | (1,2) | 1 | 2(1 row)

I have corrected the solution that didn't work because it had a mistake in the generate_subscripts() function. I have changed the column in this function because it must be an array type column (y), not an atomic one (x):

=> select * from (select x, y, generate_subscripts(y,1) as s from footable) as coord where y[s].a = 2.5;