Re: connecting multiple INSERT CTEs to same record?

On 2021-10-19 1:13 p.m., David G. Johnston wrote:
> 
> Instead of assigning a unique identifier to student after inserting it 
> into the table, assign the identifier first.  Generally this is done by 
> using “nextval()” 

Aha! Such elegant solution!

Seems obvious in hindsight, but I just couldn't figure it out nor find
any mentions to it online.

For others who might stumble upon this thread in the future,
the solution becomes:

====

  with
    -- pre-assign unique IDs for each record,
    -- without inserting them to the table yet.
    new_data_with_ids as (
      select
        nextval(pg_get_serial_sequence('students','id'))
             as new_student_id,
        new_data.*
      from new_data )
    ,

    -- Now insert the new names, with their pre-assigned IDs
    new_students as (
        insert into students(id,name)
        select new_student_id, name
        from new_data_with_ids
        returning * -- optional
        )
    ,

    -- And use the IDs for other tables, too
    new_classes as (
       insert into classes(student_id, subject)
       select new_student_id, subject
       from new_data_with_ids
       returning * --optional
    )

  -- return the new IDs with the data
  select * from new_data_with_ids ;

===

Thank you!

Regards,
  - Assaf Gordon