Re: Multiple inserts with two levels of foreign keys

On 2023-Oct-04, Dow Drake wrote:

> I want to insert a farm record, then insert two crops associated with that
> farm, then insert two deliveries for each of the the two crops so that in
> the end, my tables look like this:

If I understand you correctly, for each table you want one CTE with the
data you want to insert, and another CTE with the data actually
inserted, that can be matched later. Something like this should work:

with newfarms (name) as (values ('Happy Valley Farm')),
insertedfarms (id, name) as (insert into farms (name)
select newfarms.name
from newfarms
returning id, name),
newcrops (farm, name) as (values ('Happy Valley Farm', 'corn'),
('Happy Valley Farm', 'wheat')),
insertedcrops as (insert into crops (farm_id, name)
select (select insertedfarms.id
from insertedfarms
where insertedfarms.name = newcrops.farm),
newcrops.name
from newcrops
returning id, farm_id, name),
newdeliveries (farm, name, ticket) as (values ('Happy Valley Farm', 'corn', '3124'),
('Happy Valley Farm', 'wheat', '3127'),
('Happy Valley Farm', 'corn', '3133'),
('Happy Valley Farm', 'wheat', '3140')),
inserteddeliveries as (insert into deliveries (crop_id, ticket)
select (select ics.id
from insertedfarms ifs join insertedcrops ics on (ifs.id = ics.farm_id)
where ifs.name = newdeliveries.farm and
ics.name = newdeliveries.name),
ticket
from newdeliveries
returning *)
select * from inserteddeliveries;

--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
Are you not unsure you want to delete Firefox?
[Not unsure] [Not not unsure] [Cancel]
http://smylers.hates-software.com/2008/01/03/566e45b2.html