PostgreSQL select for arrays of composite elements

Question

I've got a table defined over a composite type:

create type footype as (  
a double precision,
b double precision
);  

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

How can I use a select statement on the single field of the composite elements contained in the table?

Thanks in advance,
Antonio

Answer 1

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)