Re: Multiple inserts with two levels of foreign keys - Mailing list pgsql-general

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.