Thread: 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 array
FROM 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@gmail.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@gmail.com>:
2016-01-06 20:50 GMT-02:00 Tom Lane <tgl@sss.pgh.pa.us>:Peter Krauss <ppkrauss@gmail.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.