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
invoices refers to
customers etc). The trick here is that
invoices is set automatically on row insert. The following query returns an empty result, unless I remove
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;