Multiple attachments with PostgreSQL if the record does not exist

Question

I would like to insert data into multiple tables if the record does not exist.

In my case, I have a restaurant table, a location table, a table, foodtypeand some auxiliary tables, such as restaurant_locationand restaurant_foodtype. Now I would like to add a new entry to the restaurant with location and food information if the entry does not exist.

So something like:

IF NOT (select 1 from restaurant where name='restaurantname') THEN
 INSERT INTO restaurant(x,y) VALUES (valuex,valuey);
 INSERT INTO restaurant_location(rest_id,..) VALUES (rest_id,..);
 INSERT INTO restaurant_foodtype(rest_id,..) VALUES (rest_id,..);
 ...
END IF

How can I do this with simple SQL?

Answer 1

I just wrote this at the top of my head, but it should be an idea if you should do it with plain sql.

insert into 
    restaurant(x, y)
values
    select valuex, valuey 
    from dual
    where
      not exists(
        select 1 from restaurant where name = 'restaurantname')

---

EDIT: , , , , WITH:

with validation as(
  select 1 from restaurant where name = 'restaurantname'
)
insert into 
    restaurant(x, y)
values
    (
     select value1x, value1y 
     from dual
     where
       validation.v = 1),
    (
     select value2x, value2y 
     from dual
     where
       validation.v = 1)

Answer 2

Postgres 9.1 CTE, , , :

WITH x AS (
   INSERT INTO restaurant(name, x, y)
   SELECT 'restaurantname', valuex, valuey
   WHERE  NOT EXISTS (SELECT 1 FROM restaurant WHERE name = 'restaurantname')
   RETURNING rest_id     -- returns auto-generated id (from sequence)
   )
, y AS (
   INSERT INTO restaurant_location(rest_id, ...)
   SELECT rest_id, ...
   FROM   x              -- only produces rows after a successful INSERT
   )

   --- more chained INSERTs here?

INSERT INTO restaurant_foodtype(rest_id, ...)
SELECT rest_id, ...
FROM   x;

INSERT , " " . - , . UNIQUE restaurant.name ( ), , , , , ( ). , , .

RETURNING rest_id - , rest_id .

INSERT , .

INSERT.

PostgreSQL 8.1 plpgsql .
, , .