Can I usemultiple return values of INSERT...RETURNING in another INSERT or multiples insert?

Question

I need take the id of anexo an then the result id list insert in another table

with new_id as(INSERT INTO 
    clienteproveedor.anexo
    (
        codigo,
        estado,
        facturar,
        observaciones,
        servicioproductocontratadoid,
        formaspagoid  
    )  
      select           
        ServiciosPorAnexo.CodAnexo,
        ServiciosPorAnexo.cancelado,
         serviciosporanexo.facturacion,
        ServiciosPorAnexo.MotivosElim,
        ServiciosPorAnexo.codigo_servicio,
        ServiciosPorAnexo.FormaPago
        from clienteproveedor.serviciosporanexo 
        returning id);

Does this answer your question? [Store and reuse value returned by INSERT ... RETURNING](https://stackoverflow.com/questions/15627781/store-and-reuse-value-returned-by-insert-returning) — user14063792468, Jun 04 '22 at 08:10
Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. — Community, Jun 04 '22 at 08:22

score 0 · Answer 1 · answered Jun 04 '22 at 19:19

Yes you can do that, and you are close. Your problem being your returning clause is in the wrong place. Try:

with new_id(id) as
     (INSERT INTO 
        clienteproveedor.anexo
       (
        codigo,
        estado,
        facturar,
        observaciones,
        servicioproductocontratadoid,
        formaspagoid
       ) 
      retuning id 
    )  
    select           
      ServiciosPorAnexo.CodAnexo,
      ServiciosPorAnexo.cancelado,
      serviciosporanexo.facturacion,
      ServiciosPorAnexo.MotivosElim,
      ServiciosPorAnexo.codigo_servicio,
      ServiciosPorAnexo.FormaPago
     from clienteproveedor.serviciosporanexo 
    where ServiciosPorAnexo.id in 
          (select id from new_id);

NOTE: Not tested as I do not have table definitions nor example data.

Can I usemultiple return values of INSERT...RETURNING in another INSERT or multiples insert?

1 Answers1