Postgres FOREACH array as composite type

Question

I'm trying to loop over an array with two types as a composite type, but it seems that I can not use the composite type as the variable array to loop over it. What I'm trying to accomplish:

create type my_item as (
    field_1        timestamp,
    field_2        numeric
);

CREATE OR REPLACE FUNCTION unnest_2d_1d_my(ANYARRAY) 
  RETURNS TABLE (ts timestamp, value numeric) AS
$func$
DECLARE
  a my_item[];
  timestamp timestamp[];
  value1 numeric[];
BEGIN
   FOREACH a SLICE 1 IN ARRAY $1 LOOP
      timestamp = array_append(timestamp, a.field_1);
      value1 = array_append(value1,a.field_2);

   END LOOP;
   RETURN QUERY select unnest(timestamp), unnest(value1);   
END
$func$  
LANGUAGE plpgsql IMMUTABLE;

Composite array types -> (timestamp, numeric):

CREATE TABLE test AS(SELECT * FROM unnest_2d_1d_my(array[['2018-01-01',2],['2018-01-02',5]]));

I'm expecting as an output:

ts           | value
'2018-01-01' | 2
'2018-01-02' | 5

but currently I only obtain the following error:

invalid input syntax for integer: "2018-01-01"

I wonder how can I work with a multi-type array to achieve the mentioned output.

Answer 1

An array always contains a single data type. ['2018-01-01',2] specifies an array with two different types: a varchar and an integer.

To initialize an my_type value you need a row() constructor:

row('2018-01-01',2)::my_item

Elements of that type can then be put into an array:

array[row('2018-01-01',2)::my_item,row('2018-01-02',5)::my_item

or you can simplify that by casting the whole array, which saves you from repeating the ::my_type cast for each element:

array[row('2018-01-01',2),row('2018-01-02',5)]::my_item[]

If you have such an array, there is no need for your own function. An unnest on it will return the result you want:

SELECT x.* 
FROM unnest(array[row('2018-01-01',2),row('2018-01-02',5)]::my_item[]) as x;

returns:

field_1             | field_2
--------------------+--------
2018-01-01 00:00:00 |       2
2018-01-02 00:00:00 |       5