PostgreSQL 9.2: apply operation to every array member

Question

Let's say I'm trying to apply a division by 2 on an array

SELECT unnest(array[1.0, 2.0, 3.0])::REAL/2

This gives me three rows with the values 0.5, 1.0 and 1.5.

How do I then proceed to convert this back into an array? I tried

SELECT array_agg(unnest(array[1.0, 2.0, 3.0])::REAL/2)

But I get: ERROR: set-valued function called in context that cannot accept a set

Extra question: How would I go about the same problem, but on multi-dimensional arrays?

SELECT unnest(array[[1.0, 0.1], [2.0, 0.2]])::REAL/2

I would want an answer like "{{0.5,0.05},{1.0,0.1}}"

Closely related to stackoverflow.com/q/8584119/398670, though that doesn't seem to consider multi-dim arrays. — Craig Ringer
– Craig Ringer, Commented Jan 16, 2014 at 5:37

Raul Rene · Accepted Answer · 2014-01-15 21:47:24Z

3

Regarding your first question, you can convert a result set to an ARRAY by simply doing:

SELECT ARRAY (SELECT unnest(array[1.0, 2.0, 3.0])::REAL/20)

answered Jan 15, 2014 at 21:47

Raul Rene

10.3k9 gold badges56 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

yieldsfalsehood · Accepted Answer · 2014-01-15 21:45:24Z

The first part is easy enough - you just need to add an extra level to aggregate everything.

select array_agg(val/2)
from (select unnest(array[1.0, 2.0, 3.0]) val) t
;

As for the multi-dimensional case, maybe something awkward like this?

create aggregate array_array_agg(anyarray) (
  sfunc=array_cat,
  stype=anyarray
)
;

select array_array_agg(val)
from
(
  select (select array[array_agg(val/2)]
          from (select unnest((array[[1.0, 0.1], [2.0, 0.2]])[i:i]) val) t) val
  from generate_series(1, array_length(array[[1.0, 0.1], [2.0, 0.2]], 1)) as i
) t
;

That uses array indexing and generate_series to step over each array, then applies the logic in the first step. I'm not sure how much cleaner it could be.

Collectives™ on Stack Overflow

PostgreSQL 9.2: apply operation to every array member

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related