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 http://stackoverflow.com/q/8584119/398670, though that doesn't seem to consider multi-dim arrays. — Craig Ringer, Jan 16 '14 at 05:37

score 3 · Answer 1 · answered Jan 15 '14 at 21:47

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 '14 at 21:47

Raul Rene

10,014
9
53
75

score 1 · Accepted Answer · answered Jan 15 '14 at 21:45

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.

PostgreSQL 9.2: apply operation to every array member

2 Answers2