PostgreSQL WITH query, execution order

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.

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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

>Solution :

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;  --  repreat negated filter from above

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 …