Postgresql – Possible to have nested inserts in Postgres 8.4

insertpostgresql

I'm trying to import data into an inventory type database. Here's what I have for the tables (truncated for brevity):

create type hostrole as enum ('Physical', 'Virtual', 'Hypervisor', 'Other');

create table host (
  id serial primary key,
  hostname varchar(40),
  role hostrole
);

create table interface (
  id serial primary key,
  name varchar(20),
  host int references host(id) on delete cascade,
  mac macaddr
);

I know I can nest a select into an insert statement, my question is whether I can nest an insert into an insert (and if so, how to do so). Here's the statement I'm trying to run:

insert into interface 
  (name, 
   mac, 
   host) 
     values 
  ('eth0', 
   '00:50:56:9d:34:d4', 
   (insert into host (hostname, hostrole) values ('foobar', 'Virtual') returning id)
);

The error comes up at the "into" of the nested insert. Can I do this in one shot, or do I have to make separate queries to do this insert?

Best Answer

You should be able to do something like this with a writable CTE:

WITH i AS (
   INSERT INTO host (hostname, hostrole) VALUES ('foobar', 'Virtual') RETURNING id
)
INSERT INTO interface (name, mac, host)
SELECT 'eth0', '00:50:56:9d:34:d4', id
FROM i

(untested, but it should be something like that)

Writable CTE is in PostgreSQL 9.1 and up.

Related Question