2011/9/16 David Johnston <polobo@xxxxxxxxx>: > -----Original Message----- > From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Ondrej Ivanic > Sent: Friday, September 16, 2011 12:54 AM > To: pgsql-general@xxxxxxxxxxxxxx general > Subject: different unnest function > > Hi, > > I need function which unnest array in a different way. Input table has ineger[][] column: > col1 > ---------- > {{1,2,3,4}, {5,6,7,8}, {9, 10, 11, 12}} > {{11,12,13,14}, {15,16,17,18}, {19, 110, 111, 112}} ... > > and output should be: > > select unnest2(col1) from T > unnest2 > ----------------- > {1,2,3,4} > {5,6,7,8} > {9, 10, 11, 12} > {11,12,13,14} > {15,16,17,18} > {19, 110, 111, 112} > > My function is: > create or replace function unnest2(anyarray) returns setof anyarray AS $BODY$ select $1[i:i] from generate_series(array_lower($1,1), array_upper($1,1)) i; $BODY$ language 'sql'; > > and the result is: > {{1,2,3,4}} > {{5,6,7,8}} > {{9, 10, 11, 12}} > {{11,12,13,14}} > {{15,16,17,18}} > {{19, 110, 111, 112}} > > which is almost what I need...(or I'm at the beginning :)) Any ideas? > > ---------------------------------------------------------------------------- > > Untested by try something like: > > SELECT ($1[i:i])[1] FROM generate_series(...) i; > > Basically you want to take the first (and only) array element from your slice. You might need to use sub-queries to get the syntax to work but the idea should be possible. nope -- that doesn't work. there is no way to manipulate array dimensionality with the [] operator except in the special case from D1->D0 (datum). this is a fundamental awkwardness that can be weird to most people. to get the output OP wants, you need to expand and rewrap: create or replace function unnest2(anyarray) returns setof anyarray AS $BODY$ select array(select unnest($1[i:i])) from generate_series(array_lower($1,1), array_upper($1,1)) i; $BODY$ language 'sql'; -- ugh. 9.1 has a vastly improved (plpgsql only) way to do this -- FOREACH SLICE... http://www.postgresql.org/docs/9.1/interactive/plpgsql-control-structures.html#PLPGSQL-FOREACH-ARRAY merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
