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.