Possible to have nested inserts in Postgres 8.4?

Question

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?

Answer 1

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.

• John: Drat. I wish I could use 9.1 or later. I'm kinda stuck with 8.4.
• Magnus Hagander: On 8.4 there is no way to do it, sorry. But if you're on 8.4, there are other reasons to start looking at an upgrade - postgresql.org/support/versioning - you've got less than a years "lifetime" left in that version.
• John: Yeah, I'm already looking at upgrading the OS entirely. I'm stuck at 8.4 because I'm stuck at RHEL 5, trying to start moving to RHEL 7 in early 2014.
• Magnus Hagander: While I realize there can be policies, RHEL5 does i no way limit you to 8.4. See postgresql.org/download/linux/redhat in the section about yum.
• MatheusOl: No reason to use 9.1 also, as 9.2 is newer, better and stable... BTW, if you can't use repositories, install it by source code, it is really easy...

Answer 2

In 8.4 you could do this by wrapping the inner inserts in a function. Something like:

CREATE OR REPLACE FUNCTION insert_host(in_hostname text, in_hostrole text)
RETURNS INT LANGUAGE SQL AS
$$
 INSERT INTO host (hostname, hostrole) VALUES (1,ドル 2ドル);
 SELECT id FROM host WHERE hostname = 1ドル;
$$;

Then you can still do something like:

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