Re: Multiple inserts with two levels of foreign keys

But honestly, the amount of text duplication hurts my "inner programmer".  
And it would have to be generated dynamically, since you don't know how many 
crops were delivered.  #shudder

On 10/5/23 09:33, Dow Drake wrote:
> 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@alvh.no-ip.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
>

-- 
Born in Arizona, moved to Babylonia.