PostgreSQL - Insert data into multiple tables simultaneously

Question

I am having a data insertion problem in tables linked by foreign key. I have read in some places that there is a "with" command that helps in these situations, but I do not quite understand how it is used.

I would like to put together four tables that will be used to make a record, however, that all the data were inserted at once, in a single query, and that they were associated with the last table, to facilitate future consultations. Here is the code for creating the tables:

    CREATE TABLE participante
     (
       id serial NOT NULL,
       nome character varying(56) NOT NULL,
       CONSTRAINT participante_pkey PRIMARY KEY (id),
     );

    CREATE TABLE venda
    (
      id serial NOT NULL,
      inicio date NOT NULL,
      CONSTRAINT venda_pkey PRIMARY KEY (id)
    );

    CREATE TABLE item
    (
      id serial NOT NULL,
      nome character varying(256) NOT NULL,
      CONSTRAINT item_pkey PRIMARY KEY (id)
    );


    CREATE TABLE lances_vendas
    (
      id serial NOT NULL,
      venda_id integer NOT NULL,
      item_id integer NOT NULL,
      participante_id integer NOT NULL,
      valor numeric NOT NULL,
      CONSTRAINT lance_vendas_pkey PRIMARY KEY (id),
      CONSTRAINT lances_vendas_venda_id_fkey FOREIGN KEY (venda_id)
        REFERENCES venda (id),
      CONSTRAINT lances_vendas_item_id_fkey FOREIGN KEY (item_id)
        REFERENCES item (id),
      CONSTRAINT lances_vendas_participante_id_fkey FOREIGN KEY (participante_id)
        REFERENCES participante (id)
    );

Answer 1

The idea is to write WITH clauses that contain INSERT ... RETRUNING to return the generated keys. Then these “views for a single query” can be used to insert those keys into the referencing tables.

WITH par_key AS
        (INSERT INTO participante (nome) VALUES ('Laurenz') RETURNING id),
     ven_key AS
        (INSERT INTO venda (inicio) VALUES (current_date) RETURNING id),
     item_key AS
        (INSERT INTO item (nome) VALUES ('thing') RETURNING id)
INSERT INTO lances_vendas (venda_id, item_id, participante_id, valor)
   SELECT ven_key.id, item_key.id, par_key.id, numeric '3.1415'
   FROM par_key, ven_key, item_key;

Answer 2

I know that you requested a single query, but you may still want to consider using a transaction:

BEGIN;
INSERT INTO participante (nome) VALUES ('Laurenz');
INSERT INTO venda (inicio) VALUES (current_date);
INSERT INTO item (nome) VALUES ('thing');
INSERT INTO lances_vendas (venda_id, item_id, participante_id, valer)
VALUES (currval('venda_id_seq'), currval('item_id_seq'), currval('participante_id_seq'), 3.1415);
COMMIT;

The transaction ensures that any new row in participante, venda and item leave the value of currval('X') unchanged.