Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1e7T1v-0006Fx-L2 for pgsql-sql@arkaria.postgresql.org; Wed, 25 Oct 2017 21:16:04 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1e7T1u-0006PZ-MS for pgsql-sql@arkaria.postgresql.org; Wed, 25 Oct 2017 21:16:02 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1e7T1t-0006PP-Ih for pgsql-sql@postgresql.org; Wed, 25 Oct 2017 21:16:02 +0000 Received: from post.visena.com ([46.226.10.50]) by makus.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1e7T1l-00061H-5k for pgsql-sql@postgresql.org; Wed, 25 Oct 2017 21:16:00 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=visena.com; s=20141101.wh; h=Content-Type:MIME-Version:Subject:In-Reply-To:Message-ID:To:From:Date; bh=n9RcBN+WQgHvoPAm5ZHdH93uzDL8ePCJ0NSgJa4niJ0=; b=SYBrMeCfV7B2+xQ2s1vjmKNs0utfC2+MYIAjLeJvy7+JiQXsL5SkB36tynyZqBQFrAwf25kZSB8LKPvZTqvXtpB+3mMTjen6CwRtymwHholk/xqrpMykawCH6CeQQAVMdWySEfOB5pMRUDwKDKDYBUYbFDJ56rf7Z+feiTlemCA=; Received: from [10.0.1.10] (helo=tc7-visena.wh.internal.visena.com) by post.visena.com with esmtp (Exim 4.82) (envelope-from ) id 1e7T1f-0008Lw-Jb for pgsql-sql@postgresql.org; Wed, 25 Oct 2017 23:15:50 +0200 Received: from localhost ([127.0.0.1] helo=tc7-visena.wh.internal.visena.com) by tc7-visena.wh.internal.visena.com with esmtp (Exim 4.86_2) (envelope-from ) id 1e7T1P-000197-Vd for pgsql-sql@postgresql.org; Wed, 25 Oct 2017 23:15:32 +0200 Date: Wed, 25 Oct 2017 23:15:31 +0200 (CEST) From: Andreas Joseph Krogh To: pgsql-sql@postgresql.org Message-ID: In-Reply-To: Subject: Re: Unable to use INSERT ... RETURNING with column from other table MIME-Version: 1.0 X-Mailer: Visena Mail 2.1.0-SNAPSHOT X-Spam-Score: -1.0 X-Spam-Report: SpamAssasin (score=-1.0, required 5.0 ALL_TRUSTED=-1,HTML_MESSAGE=0.001) Content-Type: multipart/related; boundary="----=_Part_653_44266823.1508966131732" List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org ------=_Part_652_58093932.1508966131732 Content-Type: multipart/related; boundary="----=_Part_653_44266823.1508966131732" ------=_Part_653_44266823.1508966131732 Content-Type: multipart/alternative; boundary="----=_Part_654_1163308336.1508966131753" ------=_Part_654_1163308336.1508966131753 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable P=C3=A5 onsdag 25. oktober 2017 kl. 03:56:02, skrev David G. Johnston < david.g.johnston@gmail.com >: [snip] Not directly that I know of.=C2=A0 Options: =C2=A0 1.=C2=A0Updatable view. =C2=A0 2.=C2=A0In this particular=C2=A0example you can place the desired h.id in=20 its own CTE.=C2=A0 Move the insert into a CTE.=C2=A0 Then join the insert-r= eturning CTE=20 with the input=C2=A0CTE. =C2=A0 WITH input AS ( 1 as hid ), ins1 as ( insert select=C2=A0where h.id =3D (select hid from= =20 input)=C2=A0returning *) Select *=C2=A0from ins1 cross join input =C2=A0 This gives too many duplicate rows in my use-case (at first, I think I solv= ed=20 it at the end of this post). =C2=A0 Let me give a more detailed complete example: I want to copy the set of headers with corresponding values for an=20 email-message to a new email-message. In this example I have message 1000 a= nd=20 1001, and want to copy headers (with list of values) for message 1001=C2=A0= to=20 message 1002. =C2=A0 DROP TABLE IF EXISTS tbl_value; DROP TABLE IF EXISTS tbl_header; DROP TABLE= IF=20 EXISTStbl_message; CREATE TABLE tbl_message( id INTEGER PRIMARY KEY, name= =20 VARCHAR NOT NULL); CREATE TABLE tbl_header( id SERIAL PRIMARY KEY, message_= id=20 INTEGER NOT NULL REFERENCEStbl_message(id), name VARCHAR NOT NULL, index=20 INTEGER NOT NULL, UNIQUE(message_id, name), UNIQUE(message_id, index) ); CR= EATE=20 TABLEtbl_value( id SERIAL PRIMARY KEY, header_id INTEGER NOT NULL REFERENCE= S=20 tbl_header(id),name VARCHAR NOT NULL, index INTEGER NOT NULL, UNIQUE(header= _id,=20 name), UNIQUE(header_id, index) ); INSERT INTO tbl_message(id, name) VALUES= (1000 ,'Message'), (1001, 'Message 2'), (1002, 'Message 3'); INSERT INTO=20 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); IN= SERT=20 INTOtbl_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 val= ue 1' ,0),(5, 2, 'msg1 h2 value 2', 1),(6, 2, 'msg1 h2 value 3', 2) , (7, 3, 'msg= 2 h1=20 value 1', 0),(8, 3, 'msg2 h1 value 2', 1),(9, 3, 'msg2 h1 value 3', 2) , (1= 0, 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); =C2=A0 -- 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=20 tbl_value vON h.id =3D v.header_id WHERE h.message_id =3D 1000 ORDER BY h.i= ndex ASC , v.indexASC; =C2=A0 message_id header_id name index value_id name index 1000 1 header_one msg1 = 0 1=20 msg1 h1 value 1 0 1000 1 header_one msg1 0 2 msg1 h1 value 2 1 1000 1=20 header_one msg1 0 3 msg1 h1 value 3 2 1000 2 header_two msg1 1 4 msg1 h2 va= lue 1 0 1000 2 header_two msg1 1 5 msg1 h2 value 2 1 1000 2 header_two msg1 1 6 m= sg1=20 h2 value 3 2=20 =C2=A0 -- headers and values for message 1001 SELECT h.message_id, v.heade= r_id, h .name,h.index, v.id AS value_id, v.name, v.index FROM tbl_header h JOIN=20 tbl_value vON h.id =3D v.header_id WHERE h.message_id =3D 1001 ORDER BY h.i= ndex ASC , v.indexASC; =C2=A0 message_id header_id name index value_id name index 1001 3 header_one msg2 = 0 7=20 msg2 h1 value 1 0 1001 3 header_one msg2 0 8 msg2 h1 value 2 1 1001 3=20 header_one msg2 0 9 msg2 h1 value 3 2 1001 4 header_two msg2 1 10 msg2 h2 v= alue=20 1 0 1001 4 header_two msg2 1 11 msg2 h2 value 2 1 1001 4 header_two msg2 1 = 12=20 msg2 h2 value 3 2=20 =C2=A0 =C2=A0 =C2=A0 Now, try to copy all headers, with values, for message 1001 to message 1002= ; =C2=A0 WITH input AS ( SELECT h.id, h.name, h.index FROM tbl_header h WHERE h .message_id =3D1001 ) , ins1 AS ( INSERT INTO tbl_header(id, message_id, na= me,=20 index) SELECT nextval('tbl_header_id_seq'), 1002, h.name, h.index FROM inpu= t h =20 RETURNINGid ) INSERT INTO tbl_value(header_id, name, index) SELECT f.id AS= =20 new_header_id, hv.name, hv.indexFROM tbl_value hv JOIN input h ON hv.header= _id =3D h.id CROSS JOIN ins1 AS f; =C2=A0 This fails with: ERROR: =C2=A0duplicate key value violates unique constraint=20 "tbl_value_header_id_index_key" DETAIL: =C2=A0Key (header_id, index)=3D(5, 0) already exists. =C2=A0 The reason is that the CROSS JOIN with ins1 gives too many duplicate rows: =C2=A0 (has plain SELECT instead of INSERT from the CTEs) WITH input AS ( SELECT h.id, h.name, h.index FROM tbl_header h WHERE h .message_id =3D1001 ) , ins1 AS ( INSERT INTO tbl_header(id, message_id, na= me,=20 index) SELECT nextval('tbl_header_id_seq'), 1002, h.name, h.index FROM inpu= t h =20 RETURNINGid ) SELECT f.id AS new_header_id, hv.id, hv.name, hv.index FROM= =20 tbl_value hvJOIN input h ON hv.header_id =3D h.id CROSS JOIN ins1 AS f; =C2= =A0 new_header_id id name index 5 7 msg2 h1 value 1 0 5 8 msg2 h1 value 2 1 5 9= =20 msg2 h1 value 3 2 5 10 msg2 h2 value 1 0 5 11 msg2 h2 value 2 1 5 12 msg2 h= 2=20 value 3 2 6 7 msg2 h1 value 1 0 6 8 msg2 h1 value 2 1 6 9 msg2 h1 value 3 2= 6 10 msg2 h2 value 1 0 6 11 msg2 h2 value 2 1 6 12 msg2 h2 value 3 2=20 =C2=A0 =C2=A0 Instead, I want this: =C2=A0 new_header_id id name index 5 7 msg2 h1 value 1 0 5 8 msg2 h1 value 2 1 5 9= =20 msg2 h1 value 3 2 6 10 msg2 h2 value 1 0 6 11 msg2 h2 value 2 1 6 12 msg2 h= 2=20 value 3 2=20 =C2=A0 =C2=A0 I figured that if I also JOIN'ed on tbl_header.index then it works: =C2=A0 WITH input AS ( SELECT h.id, h.name, h.index FROM tbl_header h WHERE h .message_id =3D1001 ) , ins1 AS ( INSERT INTO tbl_header(id, message_id, na= me,=20 index) SELECT nextval('tbl_header_id_seq'), 1002, h.name, h.index FROM inpu= t h =20 RETURNINGid, index ) SELECT f.id AS new_header_id, hv.id, hv.name, hv.index= FROM tbl_value hvJOIN input h ON hv.header_id =3D h.id JOIN ins1 AS f ON f.index= =3D h .index;=20 =C2=A0 new_header_id id name index 5 7 msg2 h1 value 1 0 5 8 msg2 h1 value 2 1 5 9= =20 msg2 h1 value 3 2 6 10 msg2 h2 value 1 0 6 11 msg2 h2 value 2 1 6 12 msg2 h= 2=20 value 3 2=20 =C2=A0 =C2=A0 Which then results in this query for also producing the header-values: =C2=A0 WITH input AS ( SELECT h.id, h.name, h.index FROM tbl_header h WHERE h .message_id =3D1001 ) , ins1 AS ( INSERT INTO tbl_header(id, message_id, na= me,=20 index) SELECT nextval('tbl_header_id_seq'), 1002, h.name, h.index FROM inpu= t h =20 RETURNINGid, index ) INSERT INTO tbl_value(header_id, name, index) SELECT f= .id=20 ASnew_header_id, hv.name, hv.index FROM tbl_value hv JOIN input h ON=20 hv.header_id =3Dh.id JOIN ins1 AS f ON f.index =3D h.index;=20 =C2=A0 This seems to produce correct results: =C2=A0 -- headers and values for message 1002 SELECT h.message_id, v.header_id, h .name, v.idAS value_id, v.name FROM tbl_header h JOIN tbl_value v ON h.id = =3D=20 v.header_idWHERE h.message_id =3D 1002; =C2=A0 message_id header_id name value_id name 1002 5 header_one msg2 13 msg2 h1= =20 value 1 1002 5 header_one msg2 14 msg2 h1 value 2 1002 5 header_one msg2 15= =20 msg2 h1 value 3 1002 6 header_two msg2 16 msg2 h2 value 1 1002 6 header_two= msg2 17 msg2 h2 value 2 1002 6 header_two msg2 18 msg2 h2 value 3=20 =C2=A0 Do anybody see anything potentially wrong with this, or ways to simplify it= ? =C2=A0 Thanks. =C2=A0 -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andreas@visena.com www.visena.com =C2=A0 ------=_Part_654_1163308336.1508966131753 Content-Type: text/html;charset=UTF-8 Content-Transfer-Encoding: quoted-printable
P=C3=A5 onsdag 25. oktober 2017 kl. 03:56:02, skrev David G. Johnston = <david.g.johnston@gmail.co= m>:
[snip]
Not directly that I know of.=C2=A0 Options:
=C2=A0
1.=C2=A0Updatable view.
=C2=A0
2.=C2=A0In this particular=C2=A0example you can place the desired h.id in its own CTE.=C2=A0 Move the insert into a C= TE.=C2=A0 Then join the insert-returning CTE with the input=C2=A0CTE.
=C2=A0
WITH input AS ( 1 as hid ),
ins1 as ( insert select=C2=A0where h.id = =3D (select hid from input)=C2=A0returning *)
Select *=C2=A0from ins1 cross join input
=C2=A0
This gives too many duplicate rows in my use-case (at first, I think I= solved it at the end of this post).
=C2=A0
Let me give a more detailed complete example:
I want to copy the set of headers with corresponding values for an ema= il-message to a new email-message. In this example I have message 1000 and = 1001, and want to copy headers (with list of values) for message 1001=C2=A0= to message 1002.
=C2=A0
DROP TABLE IF EXISTS tbl_value;
DROP TABLE IF EXISTS tbl_header;
DROP TABLE IF EXISTS tbl_message;

CREATE TABLE tbl_mes=
sage(
    id INTEGER PRIMARY KEY<=
/span>,
    name VARCHAR NOT NULL
);

CREATE TABLE tbl_hea=
der(
    id SERIAL PRIMARY KEY,
    message_id INTEGER NOT =
NULL REFERENCES tbl_message(id),
    name VARCHAR NOT NULL,
    index INTEGER NOT NULL<=
/span>,
    UNIQUE(message_i=
d, name),
    UNIQUE(message_i=
d, index)
);

CREATE TABLE tbl_val=
ue(
    id SERIAL PRIMARY KEY,
    header_id INTEGER NOT N=
ULL REFERENCES tbl_header(id),
    name VARCHAR NOT NULL,
    index INTEGER NOT NULL<=
/span>,
    UNIQUE(header_id=
, name),
    UNIQUE(header_id=
, index)
);

INSERT INTO tbl_mess=
age(id, name) VALUES(1000, 'M=
essage'), (1001, 'Message 2'), (1002, 'Message 3');

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

INSERT INTO tbl_valu=
e(id, header_id, name, index)
VALUES(1, 1, 'msg1 h1 value 1', 0),(2, 1, 'msg1 h1 value 2', 1=
),(3, 1, 'msg1 h1 v=
alue 3', 2)
    , (4, 2, 'msg1 h2 valu=
e 1', 0),(5, 2, 'msg1 h2 value 2', 1),(6, 2, 'msg1 h2 value 3', 2)
    , (7, 3, 'msg2 h1 valu=
e 1', 0),(8, 3, 'msg2 h1 value 2', 1),(9, 3, 'msg2 h1 value 3', 2)
    , (10, 4, 'msg2 h2 val=
ue 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);
=C2=A0
-- headers and values for =
message 1000
SELECT h.mess=
age_id, v.header_id, h.name, h.index, v.=
id AS value_id, v.na=
me, v.index
FROM tbl_header h
    JOIN tbl_=
value v ON h.id =3D =
v.header_id
WHERE h.message_id =
=3D 1000
ORDER BY h.in=
dex ASC, v.index ASC;
=C2=A0
=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09
message_idheader_idnameindexvalue_idnameindex
10001header_one msg101msg1 h1 value 10
10001header_one msg102msg1 h1 value 21
10001header_one msg103msg1 h1 value 32
10002header_two msg114msg1 h2 value 10
10002header_two msg115msg1 h2 value 21
10002header_two msg116msg1 h2 value 32
 

=C2=A0
-- headers and values for =
message 1001
SELECT h.mess=
age_id, v.header_id, h.name, h.index, v.=
id AS value_id, v.na=
me, v.index
FROM tbl_header h
    JOIN tbl_=
value v ON h.id =3D =
v.header_id
WHERE h.message_id =
=3D 1001
ORDER BY h.in=
dex ASC, v.index ASC;
=C2=A0
=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09
message_idheader_idnameindexvalue_idnameindex
10013header_one msg207msg2 h1 value 10
10013header_one msg208msg2 h1 value 21
10013header_one msg209msg2 h1 value 32
10014header_two msg2110msg2 h2 value 10
10014header_two msg2111msg2 h2 value 21
10014header_two msg2112msg2 h2 value 32
=C2=A0
=C2=A0
=C2=A0
Now, try to copy all headers, with values, for message 1001 to message= 1002;
=C2=A0
WITH input AS (
    SELECT h.id, h.name, h.index
    FROM tbl_header =
h WHERE h.message_id=
 =3D 1001
)
    , ins1 AS (
    INSERT INTO tbl_=
header(id, message_id, name=
, index)
        SELECT nextval('tbl_header_id_seq'), 1002, h.na=
me, h.index
        FROM input h
    RETURNING id
)
INSERT INTO tbl_valu=
e(header_id, name, <=
span style=3D"color:#000080;font-weight:bold;">index)
SELECT f.id AS new_header_id, hv.name, h=
v.index
FROM tbl_value hv
    JOIN input h ON =
hv.header_id =3D h.i=
d
CROSS JOIN ins1 AS f;
=C2=A0
This fails with:
ERROR: =C2=A0duplicate key value violates unique constraint "tbl_= value_header_id_index_key"
DETAIL: =C2=A0Key (header_id, index)=3D(5, 0) already exists.

=C2=A0
The reason is that the CROSS JOIN with ins1 gives too many duplicate r= ows:
=C2=A0
(has plain SELECT instead of INSERT from the CTEs)
WITH input AS (
    SELECT h.id, h.name, h.index
    FROM tbl_header =
h WHERE h.message_id=
 =3D 1001
)
    , ins1 AS (
    INSERT INTO tbl_=
header(id, message_id, name=
, index)
        SELECT nextval('tbl_header_id_seq'), 1002, h.na=
me, 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 =3D h.i=
d
    CROSS JOIN ins1 =
AS f;
=C2=A0
=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09
new_header_ididnameindex
57msg2 h1 value 10
58msg2 h1 value 21
59msg2 h1 value 32
510msg2 h2 value 10
511msg2 h2 value 21
512msg2 h2 value 32
67msg2 h1 value 10
68msg2 h1 value 21
69msg2 h1 value 32
610msg2 h2 value 10
611msg2 h2 value 21
612msg2 h2 value 32
=C2=A0
=C2=A0
Instead, I want this:
=C2=A0
=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09
new_header_ididnameindex
57msg2 h1 value 10
58msg2 h1 value 21
59msg2 h1 value 32
610msg2 h2 value 10
611msg2 h2 value 21
612msg2 h2 value 32
=C2=A0
=C2=A0
I figured that if I also JOIN'ed on tbl_header.index then it works:
=C2=A0
WITH input AS (
    SELECT h.id, h.name, h.index
    FROM tbl_header =
h WHERE h.message_id=
 =3D 1001
)
    , ins1 AS (
    INSERT INTO tbl_=
header(id, message_id, name=
, index)
        SELECT nextval('tbl_header_id_seq'), 1002, h.na=
me, 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 =3D h.i=
d
    JOIN ins1 AS f ON f.index =3D h.index;
=C2=A0
=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09
new_header_ididnameindex
57msg2 h1 value 10
58msg2 h1 value 21
59msg2 h1 value 32
610msg2 h2 value 10
611msg2 h2 value 21
612msg2 h2 value 32
=C2=A0
=C2=A0
Which then results in this query for also producing the header-values:=
=C2=A0
WITH input AS (
    SELECT h.id, h.name, h.index
    FROM tbl_header =
h WHERE h.message_id=
 =3D 1001
)
    , ins1 AS (
    INSERT INTO tbl_=
header(id, message_id, name=
, index)
        SELECT nextval('tbl_header_id_seq'), 1002, h.na=
me, h.index
        FROM input h
    RETURNING id, index
)
INSERT INTO tbl_valu=
e(header_id, name, <=
span style=3D"color:#000080;font-weight:bold;">index)
SELECT f.id AS new_header_id, hv.name, h=
v.index
FROM tbl_value hv
    JOIN input h ON =
hv.header_id =3D h.i=
d
    JOIN ins1 AS f ON f.index =3D h.index;
=C2=A0
This seems to produce correct results:
=C2=A0
-- headers and values for =
message 1002
SELECT h.mess=
age_id, v.header_id, h.name, v.id AS val=
ue_id, v.name
FROM tbl_header h
    JOIN tbl_=
value v ON h.id =3D =
v.header_id
WHERE h.message_id =
=3D 1002;
=C2=A0
=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09
message_idheader_idnamevalue_idname
10025header_one msg213msg2 h1 value 1
10025header_one msg214msg2 h1 value 2
10025header_one msg215msg2 h1 value 3
10026header_two msg216msg2 h2 value 1
10026header_two msg217msg2 h2 value 2
10026header_two msg218msg2 h2 value 3
=C2=A0
Do anybody see anything potentially wrong with this, or ways to simpli= fy it?
=C2=A0
Thanks.
=C2=A0
--
Andrea= s Joseph Krogh
CTO / Partner<= /span> - Visena AS
Mobile: +47 90= 9 56 963
=3D""
=C2=A0
------=_Part_654_1163308336.1508966131753-- ------=_Part_653_44266823.1508966131732 Content-Type: image/png Content-Transfer-Encoding: base64 Content-Disposition: inline Content-ID: iVBORw0KGgoAAAANSUhEUgAAAIUAAAAYCAYAAADUIj6hAAAABHNCSVQICAgIfAhkiAAABzBJREFU aEPtmNFxHDcMhmVP3i1VECpvnjzkVIHWFfhcgVcVRKrAUgWRK/C6Al8H3lTgy0PGbzFdQc4VJP/H ADs43q6kROeJNbOYgQACIAgCWJKng4MZ5gxUGXh0U0b+SIuF9G+EWXj2Q15vbrE/NPtk9uub7Gfd t5mBx1NhqSEo8HshjbEUtlO2yIM9tsx5dZP9rPt2MzDZFAqZE4LGcFjdsg3saQaHt7fY70X949On aS+OZidDBkavD33157L4xaw2os+Mp/DAC10l2XhOCeStj0W5ajrJL8WfCi80Xgf9vVlrhg9yRONe /f7xI2vNsIcM7JwU9o6oGyJrLT8JOA1aX9sKP4wl94bAniukEbo/n7YPmuTETzIab4Y9ZWCrKexd 8M58lxPCvvD6aihfvexbEQoPYO8NQRO0JnddGO6FJYaVsBde7cXj7KRk4LsqDxQzCYeGsJNgaXZe +JXkyGgWINq3Gp+bHNILz+wEYs5qH1eJrouNrpDXtk4O6x1IvtD40GWyJYYdkF0jIbYZlN16xygI gl9smbMDsmFdfAKb23xGB8H/mv3tOJfArs1kukk79DEPdQ5u0g1vCvvqKXIW8mZYW+F3Tg4r8HvZ kQCCLydK8EFMQCe5N8RgL9mRG0SqQFuNvdE6beSs0qPDBuCdg0+gvClso8SbTO4ki7mQzQqBrcMH QPwReg2wW8umEe/+L8T/LEzBGF9nXjzZ46s+ITHvhegW8LL39xm6App7KYL/GE+vcYnFbJiP/0aY hdiCnRC7jaj7eiK2ETInC5PRF6LYkSN0+HabF75WuT6syCyI0YkVGGMvUC33AmfZTDUEj8u6IVhu EhRUJ2U2g6UlugyNb03Hl9obHwlxJbcRzcYjO4QPjVfGFTQav4vrmp7cpMp2qbHnBxWJbisbho2Q XI6C1sIHDUFhH4Hij4UbIev66eANeiwbkA+LBsO36zAHzoXU7AhbqLAXEiOYTXcSdO8VS9L4wN8U BIYhBd6oSUgYMijOkecRuTdQa/YiBXhbXFcnCnI2SrfeBG9NydrLYMhGHa5qB9pQIxlzAE6Okjzx JO5afGe6kmhBicWKQNKuTZ5EW+MjQY8v/9rQ0bhJSJyNGUe/JH1t8h1iMbdSPAvxHYin6YmN9YBX QmTYZZNh14vHhhhal5vtcIrJjmvszPRJdExH3MWHvykoBEc9CoCGWJisOLOGoCORs1FvIMbYA8z3 kwM59oemy6LlWrLxFLmWgiQAfEGd8S+NssbK+EhyGLxUkhhiy717waBqHOJYSEacwBejkFNhjJNj v/gANOdQxPfciP/JVBCK2cOIcg3RRJ+CPrLPNVhhN6F38VKMF3XLlIJrjU5C8gMFxvKDvOcPc4rV NjCn7KM0BV+161V8viSCuJZ8SITGJGEh7IUUlxOFMYUH1kJOCN4Wh+I5pqCuK01k40kSNtnKiKIl UfxAgW5sU5JlS05rtt5YFJF1SWoqHv6BRgQcA4/bdb9WRjmMk3jyUMAbIoyJq9e4cVmgzKt9j5gN J/aYDhk+hhjExwaPcz5PObA5Zd9bvz5UzCTZuZDidu5AchpiKSwPR+TV1bCWqBQ9nCjJ5neivC82 Nr4LeS2j1gw5LUqwBuhGQQU5UwHeSslXkwIynz2U2A2yKDgG7OffwLA3TpGRpk0Tzpj3ZEJXi/GR a6GN0e0NtppChePdcAz1FTS+FN8Kpxoiykk+J8fC5tenjbu9kSqpHLu9jBrhUuhNwVGbpyZrzrl0 HPVD8SXj5EOOD/eDC64VjvYBZLuUbIVAfBN1t/C/SU+cAGtdGo+fVnzycUX5wmn6eCKPmWYJG2E/ ppTsuRCbvUD9fwquksG5GqLVKhzDQ3GrEyLKSXhsiK3T5j9EyxffCFOYi2wUlPyFFDQAhehESDjQ GoX0ho0oj8RPou6TxHJdcT0NTcWkO0AnG7+uXsnHqcas/72wvWF+mSf7N/Watp9kTXoluzeS0fB9 9CcZ/hvhSZTfh99pispZOXL9KrGrARkNUMu9ITbScZWs7xOYNt9pwxSZtYDsX/GE3xTkrXgwAsXm fud08FiTeC+m2/o7ppo+PTS/NBK5ARpDG5YHr++DpmVfreYdiX8mnp+DzPEG9WbqJON0JBenZofs sxBA1gj5NXGvfJu/Qh7HwQh/VDUEyUxCit5hH94QfKkEdu+GwK8BX0hvCF+D67xhjmXQCXMwJCZ+ olK0A1EKRCE4snsh42w8Mv/Zhxw9iD7Cjo7CyQC/KyF6oBfShL4PYgG+CHsYKyZxvxZSZBAgjhIz YDz+AbfD37GtbaoSKzgGt+lKfI/GZtay6vG4VXTpPsh+IcQhOk9I7WYeP5AM3HZ9+DaSGIp9HIuu huC4pCGGx+YD2fcc5tfIAA0h/Et4/jX8zz4fWAasIf4UbR9Y6HO4d8jAnd4U0Y8ageuCB+c+H5R3 CHU2mTMwZ+B/y8DfSMBLLOYXVuEAAAAASUVORK5CYII= ------=_Part_653_44266823.1508966131732-- ------=_Part_652_58093932.1508966131732--