Re: Unable to use INSERT ... RETURNING with column from other table

On Tuesday, October 24, 2017, Andreas Joseph Krogh <andreas(at)visena(dot)com>
wrote:
>
> WITH upd_h(new_header_id, header_name, old_header_id) AS (
> INSERT INTO tbl_header(id, name)
> SELECT 3, h.name
> FROM tbl_header h WHERE h.id = 1 RETURNING id, name, 1 -- need h.id here)
> INSERT INTO tbl_value(header_id, name)SELECT f.new_header_id, hv.nameFROM tbl_value hv
> JOIN tbl_header h ON hv.header_id = h.id
> JOIN upd_h AS f ON hv.header_id = f.old_header_id
> ;
>
>
> This fails with:
> ERROR: missing FROM-clause entry for table "h"
> LINE 5: RETURNING id, name, h.id
>
>
> Is what I'm trying to do possible? I'd like to avoid having to use
> temp-tables and/or PLPgSQL for this as I need to insert many such values in
> large batches...
>
>

Not directly that I know of. Options:

1. Updatable view.

2. In this particular example you can place the desired h.id in its own
CTE. Move the insert into a CTE. Then join the insert-returning CTE with
the input CTE.

WITH input AS ( 1 as hid ),
ins1 as ( insert select where h.id = (select hid from input) returning *)
Select * from ins1 cross join input

David J.