Re: Postgresql multidimensional arrays cast fail

On Sat, Feb 1, 2014 at 6:45 PM, alexandros_e <alexandros(dot)ef(at)gmail(dot)com> wrote:
> I do:
>
> SELECT '{{1,2},{3,4}}'::INTEGER[][]
>
> But I get:
>
> {{1,2},{3,4}} INTEGER[]. Somehow the PostgreSQL server does not understand
> that is a multidimensional array. So, later if I want to get {1,2} or {3,4},
> the field[1] or field[2]. Evem when I try:
>
> field [1:1] I get {{1,2}} and not plain one dimensional integer array {1,2}
> which I want. How do I achieve that?
You could always use this function that Pavel wrote a couple of months
ago and referenced in the wiki:
https://wiki.postgresql.org/wiki/Unnest_multidimensional_array

Here is an example:
=# create table aa (data int[]);
CREATE TABLE
=# insert into aa values ('{{1,2},{3,4}}');
INSERT 0 1
=# 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;
CREATE FUNCTION
=# select reduce_dim(data[1:1]) from aa;
reduce_dim
------------
{1,2}
(1 row)
Regards,
--
Michael