Обсуждение: 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? Thanks, -- Ondrej Ivanic (ondrej.ivanic@gmail.com)
-----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ondrej Ivanic Sent: Friday, September 16, 2011 12:54 AM To: pgsql-general@postgresql.org general Subject: [GENERAL] 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 getthe syntax to work but the idea should be possible. David J.
2011/9/16 David Johnston <polobo@yahoo.com>: > -----Original Message----- > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ondrej Ivanic > Sent: Friday, September 16, 2011 12:54 AM > To: pgsql-general@postgresql.org general > Subject: [GENERAL] 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 getthe 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