Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 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'));
share|improve this 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.

share|improve this answer
    
Drat. I wish I could use 9.1 or later. I'm kinda stuck with 8.4. –  John Aug 16 '13 at 14:50
1  
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. –  Magnus Hagander Aug 16 '13 at 14:52
    
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. –  John Aug 16 '13 at 15:14
    
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. –  Magnus Hagander Aug 16 '13 at 17:06
2  
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... –  MatheusOl Aug 20 '13 at 11:34

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.