Getting an ID inside a PostgreSQL transaction block

I'm trying to wrap all my transactions that should be all-or-nothing into BEGIN and COMMIT, but I'm not sure how to do this in cases like the following.

I have 3 tables, one for images, one for albums, and one for the relations between them, namely album_images. The way the system works is that a user can create an album and fill it with his images in one operation. The SQ开发者_JS百科L is as follows:

BEGIN;
  INSERT INTO albums [...];  -- Create a new album row
  SELECT id AS album_id FROM albums WHERE [...];  -- Get that rows ID
  -- Now use album_id in the next statement
  INSERT INTO album_images (album_id, image_id) [...];
COMMIT;

This is probably a common problem, I'm just not sure what to search for and I can't seem to find a solution in the documentation either.

---

As an alternative to the INSERT ... RETURNING clause mentioned by Cody, you can use the current value the sequence associated with the ID column:

BEGIN;
  INSERT INTO albums [...];
  INSERT INTO album_images (currval('albums_id_seq'), image_id) [...];
COMMIT;

This assumes the standard naming scheme from Postgres when creating the sequence automatically for columns defined as serial.

Another alternative - if you are only using a single insert - is to use the lastval() function. You would then not even need to put the sequence name into the INSERT statement:

BEGIN;
  INSERT INTO albums [...];
  INSERT INTO album_images (lastval(), image_id) [...];
COMMIT;

---

Postgres provides a "RETURNING" clause used in an INSERT to easily get back the newly-created primary key.

http://www.postgresql.org/docs/8.3/interactive/sql-insert.html

Some examples: http://6c62.net/blog/?p=48

---

Manually accessing sequences is not ideal

BEGIN;
  -- Create a new album row and hold onto the id
  WITH row as (INSERT INTO albums [...] RETURNING id AS album_id)
  -- Now use album_id in the next insert
  INSERT INTO album_images SELECT album_id, :image_id FROM row;
COMMIT;

returning was actually useful, however, the other 2 answers didn't know/explain how to apply it here

---

You can get the id of the inserted row as

INSERT INTO albums [...] returning album_id;

This will return the album_id after insertion.