INSERT result for SELECT FROM join'ed tables just modified

Question

I have the task to insert data into couple tables and return data from their intersection in one query. The tables are linked through specific fields (solditems refers to products and invoices, invoices refers to customers etc). The trick here is that id in invoices is set automatically on row insert. The following query returns an empty result, unless I remove JOIN on invoices. If the SELECT is done in a separate query everything is OK. What's wrong?

WITH newid AS (
INSERT INTO invoices (customer,idate)
    VALUES (777,(SELECT now()::date))
    RETURNING id
),
sold AS (
INSERT INTO solditems (invoiceid,prod,qty)
    VALUES ((SELECT id FROM newid), 888, 1),
           ((SELECT id FROM newid), 999, 2)
    RETURNING *
)
SELECT * FROM sold AS s
   JOIN products AS p ON p.id=s.prod
   JOIN invoices AS i ON i.id=s.invoiceid;

Answer 1

Like Akina explained, all CTEs and the outer SELECT of the same command see the same snapshot of the table, so the new row is not visible to the SELECT, yet. See:

• Why can't rows inserted in a CTE be updated in the same statement?
• Can I select data inserted in the same uncommited transaction?

Return all you need in the RETURNING clause:

WITH ins_invo AS (
   INSERT INTO invoices (customer, idate)
   SELECT 777, CURRENT_DATE  -- !
   RETURNING *  --!
   )
, ins_sold AS (
   INSERT INTO solditems (invoiceid, prod, qty)
   SELECT i.id, v.p, v.q  -- !
   FROM   ins_invo i
   CROSS  JOIN (
      VALUES (888, 1)
           , (999, 2)
      ) v(p, q)
   RETURNING *
   )
SELECT *
FROM   ins_sold      s
JOIN   ins_invo      i ON i.id = s.invoiceid
LEFT   JOIN products p ON p.id = s.prod  --!
;

You also join to products to retrieve (assumingly one) pre-existing row from table products. Use a LEFT JOIN there, or you get nothing back if there is no matching row in products. Unless referential integrity is enforced with an FK constraints from sold.prod → products.id, then you'd get an exception for the missing products.id, and this point is moot. (Doesn't hurt in any case, though.)

Also simplified repeated SELECT, where you only need one.