Postgresql. Create array inside select query

Question

How to translate following python expression to postgesql?

>>> ','.join([30 for x in range(3)])
30,30,30

I have table with colums:

id | entry | type | size
1 10 0 10 
2 20 0 10 
3 30 1 10
4 30 2 15

I want to query it like this:

SELECT id,
       CASE WHEN type = 1
          THEN 
              --For entry 30 and size 10 (300) - get 150,90,60
          WHEN type = 2
          THEN
              --For entry 10 and size 15 (150) - get 30,30,30,30,30 
       ELSE 
          entry*size
       END
 FROM table;

UPD Expected result:

id | prize_pool
1 | 100
2 | 200
3 | 150,90,60
4 | 30,30,30,30,30

UPD2 Equivalent function in python:

def prize_pool(entry,type,size):
    prize = entry*size
    if type == 0: 
        return [prize]
    if type == 1:
        return [prize * x for x in [0.5,0.3,0.2]]
    if type == 2:
        return [prize/int(size/3) for x in range(int(size/3))]

Answer 1

Assuming your starting table is named plop

SELECT
  plop.id,
  CASE
    WHEN plop.type = 1 THEN (SELECT array_agg(plop.entry * plop.size * val.x) FROM (VALUES (0.5), (0.3), (0.2)) val (x))::int4[]
    WHEN plop.type = 2 THEN (SELECT array_agg(3 * plop.entry * x/x ) FROM generate_series(1, plop.size / 3) x)::int4[]
    ELSE ARRAY[plop.entry * plop.size]::int4[]
  END AS prize_pool
FROM plop
;

That returns:

┌────┬──────────────────�?                                                                                                                                                                                       
│ id │    prize_pool    │                                                                                                                                                                                       
├────┼──────────────────┤                                                                                                                                                                                       
│  1 │ {100}            │                                                                                                                                                                                       
│  2 │ {200}            │                                                                                                                                                                                       
│  3 │ {150,90,60}      │                                                                                                                                                                                       
│  4 │ {90,90,90,90,90} │                                                                                                                                                                                       
└────┴──────────────────�?

Because entry x size / ( size / 3 ) = 3 x entry

Note the x/x is always equal to 1 and is needed to indicate to Postgres on which set it must aggregate the results as an array.

Hope it helps.

Answer 2

The two functions to look at are array_agg() which allows you to take an aggregate of elements as an array, and string_agg which allows you to append these in a string.

For example:

SELECT type, array_agg(size) from mytable group by type;  

would show

type | size
-----------------
   0 | {10,10}
   1 | {10}
   2 | {15}
(3 rows)

I don't know about Python's drivers but it might be one that will parse it into Python structures for you.