Multiple nested update/insert statement in postgres

Question

I have the problem, that I have to do an insert into on table of my database and a update another table with the id, the insert provided me with. I tried this:

UPDATE events SET mail_key = (
INSERT INTO mail_keys 
    SELECT (name) FROM events)
RETURNING id);

But it doesn't work. Error is:

ERROR:  Syntax Error at »INTO«
LINE 2:  INSERT INTO mail_keys 

It is a bit simplified, since I do not select the name, but a normalized version of the name, which does not create the problem here.

Has anyone an idea? This statement will be duplicated over more tables (clubs, persons,...), so I have a unique key of several tables. The next question would be, how to ensure, that there are no double mail_keys created, if the event.name is not unique in events. I have a unique constraint in mail_keys.

EDIT (for more information): The mail_key table looks like this:

CREATE TABLE sailbook.mail_keys (
id serial,
mail_key character varying(127),
CONSTRAINT mail_key_pk PRIMARY KEY (id),
CONSTRAINT unique_mail_key UNIQUE(mail_key)
);

What I want to do is: I have a table with names of an event, like:

123 -  Hurricane
242 - Street Art
363 - The Party 2012

And Clubs like this:

896 - Town Hall Club
874 - Halo 12
846 - Francist

And I want to create a unique mail key out of it and store it in a different table (since the mail key should not only be unique for an event, but for the club or the person too). The mail key table would then look like this:

1 - hurricane
2 - street_art
3 - the_party_2012
4 - town_hall_club
5 - halo_12
6 - francist 

And the event table would look like this:

123 - Hurricane - 1
242 - Street Art - 2
363 - The Party 2012 - 3

And the club table would look like this:

896 - Town Hall Club - 4
874 - Halo 12 - 5 
846 - Francist - 6

Answer 1

A common table expression can be used to perform multiple changes in a single statement, as documented here: http://www.postgresql.org/docs/9.1/static/queries-with.html

You would insert values in the CTE, returning the columns required for the update which would be performed in the main query.

Performance for bulk data would be better than a row-by-row approach, and the statement is guaranteed to either succeed or fail, with no need to rollback partial success.

Answer 2

The right approach for this problem would be an anonymous function/method, which does two statements or more and keeps the values like this:

-- EVENTS
DO $$ 
DECLARE 
  ds record;
  nid integer;
  c integer;
  nwert character varying(4000);
BEGIN
  FOR ds IN (SELECT id, name AS wert FROM events) LOOP
    SELECT count(*) INTO c FROM mail_keys WHERE mail_key = ds.wert; 
    nwert := ds.wert;
    IF (c > 0) THEN   
      nwert := nwert || '_' || ds.id;
    END IF;

    INSERT INTO mail_keys (mail_key) VALUES(nwert) returning id INTO nid;
    UPDATE events SET mail_key = nid WHERE id = ds.id;
  END LOOP;
END$$;

That solves the problem!