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) );
Advertisement
Answer
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;