Re: [SQL] Unable to use INSERT ... RETURNING with column fromother table

DROP TABLE IF EXISTS tbl_value;
DROP TABLE IF EXISTS tbl_header;

CREATE TABLE tbl_header(   id INTEGER PRIMARY KEY,   name VARCHAR NOT NULL
);

CREATE TABLE tbl_value(   id SERIAL PRIMARY KEY,   header_id INTEGER NOT NULL REFERENCES tbl_header(id),   name VARCHAR NOT NULL
);

INSERT INTO tbl_header(id, name) VALUES(1, 'header_one'), (2, 'header_two');

INSERT INTO tbl_value(id, header_id, name)
VALUES(1, 1, 'value 1'),(2, 1, 'value 2'),(3, 1, 'value 3')   , (4, 2, 'value 1'),(5, 2, 'value 2'),(6, 2, 'value 3');

SELECT setval('tbl_value_id_seq', 6);

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
;

select h.*, v.* from tbl_header h JOIN tbl_value v ON v.header_id = h.id ORDER BY h.id, v.id;

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, h.id
)   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
;