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
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;
