How to create this postgres query INSERT?

Question

I need to return the id of the first table and fill it in the other 2 tables.

How can I do this?

                                     Table "public.Soggetto"
   Column   |           Type           |                        Modifiers
------------+--------------------------+---------------------------------------------------------
 codFisc    | character varying(20)    |
 partIVA    | character varying(20)    |
 regSociale | character varying(100)   |
 nome       | character varying(20)    |
 cognome    | character varying(20)    |
 gruppo_id  | integer                  |
 dataIns    | timestamp with time zone |
 dataElim   | timestamp with time zone |
 id         | integer                  | not null default nextval('"Soggetto_id_seq"'::regclass)

Indexes:
    "Soggetto_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "TabGruppo" FOREIGN KEY (gruppo_id) REFERENCES "Gruppo"(id)
Referenced by:
    TABLE ""JunctionONE"" CONSTRAINT "ToSoggetto" FOREIGN KEY (soggetto_id) REFERENCES "Soggetto"(id)
    TABLE ""Tipologia"" CONSTRAINT "toSoggetto" FOREIGN KEY (soggetto_id) REFERENCES "Soggetto"(id) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE ""Tipologia2"" CONSTRAINT "toSoggetto" FOREIGN KEY (soggetto_id) REFERENCES "Soggetto"(id) ON UPDATE CASCADE ON DELETE CASCADE





var query = client.query('INSERT INTO "Soggetto" (nome, cognome, "regSociale", "partIVA") VALUES($1, $2, $3, $4)',
    [sog.nome, sog.cognome, sog.ragioneSociale, sog.partitaIva],'INSERT INTO "Tipologia"(privato, azienda) VALUES ($5, $6)',
    [sog.privato, sog.azienda],'INSERT INTO "Tipologia2"(cliente, fornitore) VALUES($7, $8)',[sog.cliente, sog.fornitore]

Answer 1

using CTE:

var query = client.query(
'with i as (
   INSERT INTO "Soggetto" (nome, cognome, "regSociale", "partIVA") 
   VALUES($1, $2, $3, $4) RETURNING *
 )
 , e as (
   INSERT INTO "Tipologia"(soggetto_id,privato, azienda) select SELECT id, $5, $6
   FROM i
 )
 INSERT INTO "Tipologia2"(soggetto_id,cliente, fornitore) select SELECT id,$7, $8
   FROM i
',[sog.nome, sog.cognome, sog.ragioneSociale, sog.partitaIva, sog.privato, sog.azienda, sog.cliente, sog.fornitore]);

name5,6,7,8 are used for column name of table "Soggetto" that I dont know - please change to original names

working example with know names:

t=# create table s141 (i int);
CREATE TABLE
t=# create table s142 (i int);
CREATE TABLE
t=# create table s143 (i int);
CREATE TABLE
t=# with i as (insert into s141 select 1 returning *)
, e as (insert into s142 select i from i)
t-# insert  into s143 select i from i;
INSERT 0 1
t=# select * from s141;
 i
---
 1
(1 row)

t=# select * from s142;
 i
---
 1
(1 row)

t=# select * from s143;
 i
---
 1
(1 row)