On Tuesday, October 24, 2017, Andreas Joseph Krogh <
andreas@visena.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.name
FROM 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.