CODEWITHSUNDEEP
postgresqlmultidimensional-array
Jose
Jose

Reputation: 1067

Postgresql transform multidimensional array to relational model

I have seeing this example multiple times but only on a single column

INSERT INTO user_subservices(user_id, subservice_id) 
SELECT 1 id, x
FROM    unnest(ARRAY[1,2,3,4,5,6,7,8,22,33]) x

I need to insert multiple columns with multidimensional array like:

INSERT INTO items(order_id,lot_serial,pallets,oum) VALUES from Array

Array example:

[[3,'Fff767',89,'Boxes'],[3,'FDH6784',45,'Boxes'],[3,'FDH6788',97,'Boxes']...]

What would be the approach to do this with Postgresql 9.4.5

Upvotes: 1

Views: 148

Answers (1)

Yevhen Surovskyi
Yevhen Surovskyi

Reputation: 961

You can create reduce_dim function to unnest ultidimensional array. See https://wiki.postgresql.org/wiki/Unnest_multidimensional_array

CREATE OR REPLACE FUNCTION public.reduce_dim(anyarray)
RETURNS SETOF anyarray AS
$function$
DECLARE
    s $1%TYPE;
BEGIN
    FOREACH s SLICE 1  IN ARRAY $1 LOOP
        RETURN NEXT s;
    END LOOP;
    RETURN;
END;
$function$
LANGUAGE plpgsql IMMUTABLE;

insert into items select t[1],t[2],t[3],t[4] from (select reduce_dim(reduce_dim(ARRAY[['3','Fff767','89','Boxes'],['3','FDH6784','45','Boxes'],['3','FDH6788','97','Boxes']])) t) as y;

Upvotes: 1

Related Questions