Re: looking for an array-extract-item-as-it operator

I need to access an array-item from an array of arrays. Suppose

WITH t AS (SELECT '{{1,2,3},{33,44,55}}'::int[][] as a)SELECT
a[2], -- returns null (!), why not works?
a[2:2], -- returns array-into-array, not a simple arrayFROM t;

There are a simple function or operator to acess it as it?

Summarizing: I am looking for a f(a,2) that returns {33,44,55}, not
{{33,44,55}}.

Peter Krauss <ppkrauss(at)gmail(dot)com> writes:
> I need to access an array-item from an array of arrays.

Multi-dimensional arrays in Postgres are not "arrays of arrays".
If you persist in thinking they are, it's mostly going to lead you
astray about what will work or not.

Having said that, you might find that plpgsql's
FOREACH ... SLICE ... IN ARRAY ... LOOP
construct would help you, if you need to iterate through the
rows sequentially.

http://www.postgresql.org/docs/9.4/static/plpgsql-control-structures.html#PLPGSQL-FOREACH-ARRAY

regards, tom lane

(ops, sending to the pgsql-hackers, see the complete thread below)

Adding a formal suggestion after discussion: to include a fast
array_getarray() function!

CREATE FUNCTION array_getarray( m anyarray, idx int ) RETURNS anyarray AS
$f$
-- this is a slow workaround for an (need for) internal operation
WITH item AS (SELECT unnest($1[$2:$2]) as x)
SELECT array_agg(x) FROM item;
$f$ LANGUAGE sql IMMUTABLE;

-- EXAMPLE:
SELECT array_getarray(zz,2) as x, zz[2:2] as y -- x is not same as y!
FROM ( SELECT '{{1,2},{33,44}}'::int[][] as zz ) as tt

2016-01-07 7:26 GMT-02:00 Peter Krauss <ppkrauss(at)gmail(dot)com>:

>
>
> 2016-01-06 20:50 GMT-02:00 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>
>> Peter Krauss <ppkrauss(at)gmail(dot)com> writes:
>> > I need to access an array-item from an array of arrays.
>>
>> Multi-dimensional arrays in Postgres are *not* "arrays of arrays".
>>
>
> Thanks, you expressed in a little phrase something fundamental to think
> about pg-arrays (!), the pg-Guide need some notices like yours, to remember
> people like me ;-) Well... The good answer closes the question.
>
> - - - -
>
> We can imagine that the "multidimensional array world" is like a data
> type, that is distinct from the "usual array" data type...
> I am starting other discussion...
>
> Let me explain how the question arrives for me: was after working with
> JSONB, where arrays are of "usual array" type.
> Now that PostgreSQL 9.4+ incorporated definitely JSONB, the SQL array data
> type is an important "intermediate" between JSONB and usual SQL structures
> (and type operation algebras).
>
> So, perhaps, PostgreSQL 9.4+ will need a kind of "usual array type", a new
> internal type, and a *cast* function: with this new type will be possible
> to simplify the work with JSONB, and do other things like
> array_agg(array[x,y]).
> ... It is not for final user, perhaps only for developers, or library
> plugins: an "intermediate" type that not broken compatibility... Not very
> useful, a type only to formally express things like to eficient cast, etc.
>
>
>
>
>
>