I'm trying to update/delete from a table, then return the updated data via an inner join like this:

WITH removed AS (
  DELETE FROM cart
  WHERE cart.id = 1
  RETURNING *
)

SELECT cart.id, product.name, product.descr, product.price, cart.quantity
FROM cart INNER JOIN product
ON cart.product_id = product.id
WHERE cart.user_id = (SELECT user_id FROM removed);

However, it seems that the main SELECT query is returning before delete, so the changes are not reflected. From what I've read in the PostgreSQL documentation:

The sub-statements in WITH are executed concurrently with each other and with the main query.

Is there an alternative method I could use to return the select statement after the update/deletion from the table?

CodePudding user response：

Like you quoted the manual yourself (though pointing to the outdated Postgres 9.1), changes made in CTEs of the same statement are not visible in the underlying tables.

I suggest this workaround:

WITH removed AS (
   DELETE FROM cart
   WHERE id = 1
   RETURNING user_id
   )
SELECT c.id, p.name, p.descr, p.price, c.quantity
FROM   cart c
JOIN   product p ON c.product_id = p.id
WHERE  c.user_id = (SELECT user_id FROM removed);
AND    c.cart.id <> 1;  --  repeat negated filter from above

<> is the "not equal" operator and the opposite of =. cart.id <> 1 excludes the row(s) to be deleted with WHERE id = 1.

Else, you need two separate statements (nested in the same transaction). The second (SELECT) would then see effects of the first. You just have to remember the affected user_id somehow ...