How to "fold" array values in Postgres?

Question

I am a Postgresql newcomer and I'd like to know is it possible to calculate the following:

select T.result + 
    -- here I want to do the following:
    -- iterate through T.arr1 and T.arr2 items and add their values
    -- to the T.result using the rules:
    -- if arr1 item is 1 then add 10, if arr1 item is 2 or 3 then add 20,
    -- if arr2 item is 3 then add 15, if arr2 item is 4 then add 20, else add 30

from (
    select 5 as result, array[1,2,3] as arr1, array[3,4,5] as arr2
) as T

So that for these arrays the query will produce: 5+10+20+20+15+20+30 = 120.

Thanks for any help.

it is kind of 'artifical' example just to demonstrate my problem. No, let's assume their lengths are not always 3. — varnie
– varnie, Commented Mar 6, 2013 at 13:27

Ihor Romanchenko · Accepted Answer · 2013-03-06 14:53:26Z

3

Try something like:

SELECT SUM(CASE val 
           WHEN 1 THEN 10
           WHEN 2 THEN 20
           WHEN 3 THEN 20
           END)
FROM unnest(array[1,2,3]) as val

To get the sum of array.

The full query will look like:

select T.result + 
       (SELECT SUM(CASE val 
               WHEN 1 THEN 10
               WHEN 2 THEN 20
               WHEN 3 THEN 20
               END)
        FROM  unnest(arr1) as val) +
       (SELECT SUM(CASE val 
               WHEN 3 THEN 15
               WHEN 4 THEN 20
               ELSE 30
               END)
        FROM  unnest(arr2) as val)   
from (
    select 5 as result, array[1,2,3] as arr1, array[3,4,5] as arr2
) as T

SQLFiddle

answered Mar 6, 2013 at 14:53

Ihor Romanchenko

29k9 gold badges56 silver badges45 bronze badges

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

Collectives™ on Stack Overflow

How to "fold" array values in Postgres?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related