Use returned value of INSERT ... RETURNING in multiple following inserts

Question

I'm trying to use a value returned by an INSERT ... RETURNING statement in multiple following INSERTs.

Say we have the following tables:

CREATE TABLE hosts (host_id SERIAL, name CHARACTER VARYING(20));
CREATE TABLE interfaces (interface_id SERIAL, host_id INTEGER, name CHARACTER VARYING(10), iface_ip INET);
INSERT INTO hosts (name) VALUES ('Host A'),('Host B');

What I want, is to insert a row in the first table (hosts), get the created host_id and then insert multiple rows into the second table (interfaces) with given values and the host_id from the first statement.

I found the following way, using a CTE and a SELECT with static values which works for me, but I'm pretty sure, that this is not the way to accomplish it...

WITH temp_table AS (
INSERT INTO hosts (name) VALUES ('Host C') RETURNING host_id AS last_hostid
), i1 AS (
INSERT INTO interfaces (host_id, name, iface_ip) SELECT last_hostid, 'eth0', '192.168.1.1' FROM temp_table
), i2 AS (
INSERT INTO interfaces (host_id, name, iface_ip) SELECT last_hostid, 'eth1', '192.168.2.1' FROM temp_table
), i3 AS (
INSERT INTO interfaces (host_id, name, iface_ip) SELECT last_hostid, 'eth2', '192.168.3.1' FROM temp_table
) SELECT 1;

I know that I can easily do this, by talking back to a webserver with say PHP, and then fill in the variable in the next statement. But I wanted to accomplish it without all the back and forth, solely in PostgreSQL. So, if there is a better way than mine (and I'm pretty sure of it) - any hints?

score 2 · Accepted Answer · 2019-01-29T06:38:20.240

You can create one CTE with the rows you want to insert and then use that as the source for the actual insert:

WITH temp_table AS (
  INSERT INTO hosts (name) VALUES ('Host C') 
  RETURNING host_id AS last_hostid
), new_data (name, iface_ip) AS (
  values 
    ('eth0', '192.168.1.1'::inet),
    ('eth1', '192.168.2.1'::inet),
    ('eth2', '192.168.3.1'::inet)
)
INSERT INTO interfaces (host_id, name, iface_ip) 
SELECT last_hostid, nd.name, nd.iface_ip
FROM new_data as nd, temp_table;

The (implicit) cross join in the SELECT doesn't matter as temp_table only return a single row.

Use returned value of INSERT ... RETURNING in multiple following inserts

1 Answers1