psql how do you select from a composite array

Question

CREATE TYPE complex AS (
start_time timestamp,
amount int,
);

Given a variable

my_complexes complex[]

how do you query it like a table?

SELECT amount FROM my_complexes;

Gives "relation does not exist".

SELECT mc.amount FROM (SELECT my_complexes) mc;

Gives "amount does not exist".

In case it is relevant, my_complexes is passed in as a parameter of a function. I want to then select on that array.

Answer 1

You should use:

SELECT (your_column_from_table_ my_complexes).amount FROM my_complexes;

Full example:

CREATE TYPE complex AS (
   start_time timestamp,
   amount int
);

CREATE TABLE my_complexes (
    clm complex
);

INSERT INTO my_complexes VALUES ((LOCALTIMESTAMP, 112));
SELECT (clm).amount FROM my_complexes;

Answer 2

It seems what I was missing was UNNEST(my_complexes)

SELECT mc.amount FROM UNNEST(my_complexes) mc;