0

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.

2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.