Re: Multiple inserts with two levels of foreign keys

Yes! Thanks, Alvaro! This is exactly the pattern I was trying to work out! This community is awesome!

> On Oct 5, 2023, at 2:39 AM, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> wrote:
>
> 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