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.