Postgresql multidimensional arrays cast fail

On Sat, Feb 1, 2014 at 6:45 PM, alexandros_e <alexandros.ef@gmail.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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

This is probably the only way to do it. Still, it seems to me an overkill if
basically you need to run a function at each multidimensional array to get
access to each i-array element. Thanks for your answer.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Postgresql-multidimensional-arrays-cast-fail-tp5790077p5790095.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general