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.
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
Elements of that type can then be put into an array:
or you can simplify that by casting the whole array, which saves you from repeating the
::my_type cast for each element:
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;
field_1 | field_2 --------------------+-------- 2018-01-01 00:00:00 | 2 2018-01-02 00:00:00 | 5
External links referenced by this document: