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@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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
(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>:
Show quoted text
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.
Import Notes
Reply to msg id not found: CAHEREtujdwks-V7hvHetccSbsaemPP0+sxXnjY4mx8RmYpsAw@mail.gmail.com