PostgreSQL how to return CTE from UPDATE statement?

I have the following query:

WITH inserted_rows AS (    INSERT INTO bet_slips_table (bet_slip_id, userid, pool_id, bet_amount, placement_time, multiplier_id, slip_status, bet_target)    VALUES (uuid_generate_v4(), 'ab643285-ec78-4ddd-a0b3-d12ede0273ca', 'match-UaVg5mdYOdcsAfNJaSxHY', 300, NOW(), NULL, 'placed', 'right'),           (uuid_generate_v4(), 'ab643285-ec78-4ddd-a0b3-d12ede0273ca', 'match-UaVg5mdYOdcsAfNJaSxHY', 100, NOW(), NULL, 'placed', 'left'),           (uuid_generate_v4(), 'ab643285-ec78-4ddd-a0b3-d12ede0273ca', 'match-UaVg5mdYOdcsAfNJaSxHY', 500, NOW(), NULL, 'placed', 'left'),           (uuid_generate_v4(), 'ab643285-ec78-4ddd-a0b3-d12ede0273ca', 'fefwefwe', 500, NOW(), NULL, 'placed', 'left'),           (uuid_generate_v4(), 'ab643285-ec78-4ddd-a0b3-d12ede0273ca', 'fefwefwe', 300, NOW(), NULL, 'placed', 'right')    ON CONFLICT DO NOTHING    RETURNING *  ),  agg_rows AS (    SELECT      SUM(        CASE WHEN bet_target = 'left' THEN bet_amount ELSE 0 END      ) AS left_total,      SUM(        CASE WHEN bet_target = 'right' THEN bet_amount ELSE 0 END      ) AS right_total,      pool_id    FROM inserted_rows    GROUP BY pool_id  )    UPDATE bet_pools_table AS b  SET pool_value = b.pool_value + ag.left_total + ag.right_total,    total_left_bets = b.total_left_bets + ag.left_total,    total_right_bets = b.total_right_bets + ag.right_total,    updated = NOW()  FROM agg_rows AS ag  WHERE closed = FALSE  AND b.pool_id = ag.pool_id  RETURNING inserted_rows;  

It currently fails with the error:

Query 1 ERROR: ERROR:  column "inserted_rows" does not exist  LINE 32: RETURNING inserted_rows;  

Which I totally understand, since inserted_rows is not used in the UPDATE statement I can't return it. How would I be able to return inserted_rows from this statement?

I had already tried joining the tables, but the problem is for the example query, agg_rows will only have 2 rows, so only 2 of the rows from inserted_rows will make it into the join.

Might I need a function to wrap this so I can have more control over variables? Or some other method ro return the CTE?

https://stackoverflow.com/questions/66512563/postgresql-how-to-return-cte-from-update-statement March 07, 2021 at 10:05AM