Is there a way to set value in specific position inside array based on information from other columns (PostgreSQL-9.3 and later)?
For example I would like to select an item and its stock information from following tables:
Table items
:
CREATE TABLE items (
id integer NOT NULL
);
Table item_stock
(containing shop-specific information like stock and prices)
CREATE TABLE item_stock (
item_id integer NOT NULL,
shop_id integer NOT NULL,
stock integer,
cost numeric(19,3),
);
Now with having following data inside my tables (SQLFiddle):
items containing 4 products:
(1), (2), (3), (4)
items_stock:
(1, 1, 2, 10),
(1, 2, 0, 9),
(2, 2, 0, 9),
(3, 1, 3, 22);
What I would like to end up with is query producing following results, where stock column contains stock info from specific shops like this (shop_id=1, shop_id=2)
:
id, stock
1, {2, 0}
2, {0, 0}
3, {3, 0}
4, {0, 0}