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

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

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

CREATE TABLE tbl_header(   id SERIAL PRIMARY KEY,   message_id INTEGER NOT NULL REFERENCES tbl_message(id),   name VARCHAR NOT NULL,   index INTEGER NOT NULL,   UNIQUE(message_id, name),   UNIQUE(message_id, index)
);

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

INSERT INTO tbl_message(id, name) VALUES(1000, 'Message'), (1001, 'Message 2'), (1002, 'Message 3');

INSERT INTO tbl_header(id, message_id, name, index) VALUES(1, 1000, 'header_one msg1', 0), (2, 1000, 'header_two msg1', 1);
INSERT INTO tbl_header(id, message_id, name, index) VALUES(3, 1001, 'header_one msg2', 0), (4, 1001, 'header_two msg2', 1);

INSERT INTO tbl_value(id, header_id, name, index)
VALUES(1, 1, 'msg1 h1 value 1', 0),(2, 1, 'msg1 h1 value 2', 1),(3, 1, 'msg1 h1 value 3', 2)   , (4, 2, 'msg1 h2 value 1', 0),(5, 2, 'msg1 h2 value 2', 1),(6, 2, 'msg1 h2 value 3', 2)   , (7, 3, 'msg2 h1 value 1', 0),(8, 3, 'msg2 h1 value 2', 1),(9, 3, 'msg2 h1 value 3', 2)   , (10, 4, 'msg2 h2 value 1', 0),(11, 4, 'msg2 h2 value 2', 1),(12, 4, 'msg2 h2 value 3', 2)
;

SELECT setval('tbl_header_id_seq', 4);
SELECT setval('tbl_value_id_seq', 12);

-- headers and values for message 1000
SELECT h.message_id, v.header_id, h.name, h.index, v.id AS value_id, v.name, v.index
FROM tbl_header h
    JOIN tbl_value v ON h.id = v.header_id
WHERE h.message_id = 1000
ORDER BY h.index ASC, v.index ASC;

-- headers and values for message 1001
SELECT h.message_id, v.header_id, h.name, h.index, v.id AS value_id, v.name, v.index
FROM tbl_header h
    JOIN tbl_value v ON h.id = v.header_id
WHERE h.message_id = 1001
ORDER BY h.index ASC, v.index ASC;

WITH input AS (   SELECT h.id, h.name, h.index   FROM tbl_header h WHERE h.message_id = 1001
)   , ins1 AS (   INSERT INTO tbl_header(id, message_id, name, index)       SELECT nextval('tbl_header_id_seq'), 1002, h.name, h.index       FROM input h
    RETURNING id
)
INSERT INTO tbl_value(header_id, name, index)
SELECT f.id AS new_header_id, hv.name, hv.index
FROM tbl_value hv   JOIN input h ON hv.header_id = h.id
CROSS JOIN ins1 AS f;

WITH input AS (   SELECT h.id, h.name, h.index   FROM tbl_header h WHERE h.message_id = 1001
)   , ins1 AS (   INSERT INTO tbl_header(id, message_id, name, index)       SELECT nextval('tbl_header_id_seq'), 1002, h.name, h.index       FROM input h
    RETURNING id
)
SELECT f.id AS new_header_id, hv.id, hv.name, hv.index
FROM tbl_value hv   JOIN input h ON hv.header_id = h.id   CROSS JOIN ins1 AS f;

WITH input AS (   SELECT h.id, h.name, h.index   FROM tbl_header h WHERE h.message_id = 1001
)   , ins1 AS (   INSERT INTO tbl_header(id, message_id, name, index)       SELECT nextval('tbl_header_id_seq'), 1002, h.name, h.index       FROM input h
    RETURNING id, index
)
SELECT f.id AS new_header_id, hv.id, hv.name, hv.index
FROM tbl_value hv   JOIN input h ON hv.header_id = h.id   JOIN ins1 AS f ON f.index = h.index;

WITH input AS (   SELECT h.id, h.name, h.index   FROM tbl_header h WHERE h.message_id = 1001
)   , ins1 AS (   INSERT INTO tbl_header(id, message_id, name, index)       SELECT nextval('tbl_header_id_seq'), 1002, h.name, h.index       FROM input h
    RETURNING id, index
)
INSERT INTO tbl_value(header_id, name, index)
SELECT f.id AS new_header_id, hv.name, hv.index
FROM tbl_value hv   JOIN input h ON hv.header_id = h.id   JOIN ins1 AS f ON f.index = h.index;

-- headers and values for message 1002
SELECT h.message_id, v.header_id, h.name, v.id AS value_id, v.name
FROM tbl_header h
    JOIN tbl_value v ON h.id = v.header_id
WHERE h.message_id = 1002;