Update array with new values from table

Question

I have table with prices for some items:

|----------------------------|
| ItemID (INT) | Price (INT) |
|--------------|-------------|
|       1      |      50     |
|--------------|-------------|
|       2      |      36     |
|--------------|-------------|
|       3      |      11     |
|--------------|-------------|
|       4      |      22     |
|--------------|-------------|
|       5      |      54     |
|--------------|-------------|
|       6      |      38     |
|--------------|-------------|
|       7      |       2     |
|--------------|-------------|
|       8      |       1     |
|--------------|-------------|
|       9      |      39     |
|----------------------------|

This table contains most actual prices, updating every hour (for example).

I have something like "carts" with items and prices that was actual at cart creation time:

|-------------------------------------------------------|
| CartID (INT) | Items (INTEGER[]) | Prices (INTEGER[]) |
|--------------|-------------------|--------------------|
|       1      |      {1,2,3}      |     {50,25,310}    |
|--------------|-------------------|--------------------|
|       2      |      {4,5,6}      |    {1337,20,32}    |
|--------------|-------------------|--------------------|
|       3      |     {1,9,6,7}     |   {258,356,711,2}  |
|-------------------------------------------------------|

At some event I have to update prices in this carts to actual ones.

My function (not finished):

CREATE OR REPLACE FUNCTION public.prices_update(startindex integer)
 RETURNS void
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $function$
DECLARE
    cart RECORD;
    prices INTEGER[];
    t RECORD;
BEGIN
    FOR bet IN
        SELECT * FROM Carts WHERE CartID > startindex
    LOOP
        prices := ARRAY[]::INTEGER[];
        FOR t IN
            SELECT ItemID, Price FROM Prices WHERE ItemID = ANY(cart.Items)
        LOOP
            prices := prices || t.Price;
        END LOOP;
        RAISE NOTICE '%', prices;
    END LOOP;
END;
$function$

So, I stuck on new prices array creation. Position of prices should be linked with positions of items in array. How to make it? Or maybe more clever solutions for saving prices?

Answer 1

This query select carts with prices from table items:

select cartid, array_agg(c.itemid) items, array_agg(i.price) prices
from (
    select cartid, unnest(items) itemid
    from carts
    where cartid > 0        -- startindex
    ) c
    join items i using(itemid)
group by 1
order by 1;

 cartid |   items   |    prices    
--------+-----------+--------------
      1 | {1,2,3}   | {50,36,11}
      2 | {4,5,6}   | {22,54,38}
      3 | {1,6,7,9} | {50,38,2,39}
(3 rows)

Use the above query to update carts:

update carts c
set items = n.items, prices = n.prices
from (
    select cartid, array_agg(c.itemid) items, array_agg(i.price) prices
    from (
        select cartid, unnest(items) itemid
        from carts
        where cartid > 0        -- startindex
        ) c
        join items i using(itemid)
    group by 1
    order by 1
    ) n
where n.cartid = c.cartid;

The query has to be a little bit more complicated if the order of array elements must be preserved. Use additionally order by row_number() in the stage when elements are aggregated.

select cartid, array_agg(itemid) items, array_agg(price) prices
from (
    select cartid, n, c.itemid, i.price
    from (
        select cartid, itemid, row_number() over (partition by cartid) n
        from (
            select cartid, unnest(items) itemid
            from carts
            where cartid > 0        -- startindex
            ) c
        ) c
        join items i using(itemid)
    order by 1, 2
    ) c
group by 1
order by 1;

 cartid |   items   |    prices    
--------+-----------+--------------
      1 | {1,2,3}   | {50,36,11}
      2 | {4,5,6}   | {22,54,38}
      3 | {1,9,6,7} | {50,39,38,2}
(3 rows)