在PostgreSQL事务块中获取ID

Question

在PostgreSQL事务块中获取ID

7

我正试图将所有应该具备全或无效的交易包装在BEGIN和COMMIT中，但我不确定在以下情况下该如何实现。

我有3个表，一个用于图像，一个用于相册，一个用于它们之间的关系，即album_images。系统的运作方式是用户可以一次操作创建一个相册并填充它的图片。SQL如下：

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;

这可能是一个常见的问题，我不确定该搜索什么，也没有在文档中找到解决方案。

- Steffen

4个回答

1

手动访问序列并不理想。

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实际上很有用，但是其他两个答案并不知道/解释如何在这里应用它。

- Tobi Akinyemi

“手动访问序列不是一个健壮的解决方案” - 是的，它是。 - user330315

@a_horse_with_no_name 改变了我的措辞。 - Tobi Akinyemi

你需要至少删除CTE后面的;才能使语句正确。 - user330315

1

Postgres提供了一个“RETURNING”子句，用于在INSERT语句中轻松获取新创建的主键。

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

一些例子：http://6c62.net/blog/?p=48

- Cody Caughlan

3

谢谢，但我的问题不是获取那个ID，而是在下一个语句中使用它。在我的示例中，那个SELECT是实际可用的工作代码，可以返回正确的ID。或者我误解了什么？那些示例对我来说有点难懂。 - Steffen

当我尝试在事务的另一个语句中使用命名的 RETURNING 时，它会抛出一个“未知列”错误... 我不认为这些变量在事务中是共享的。 - Anthony Bobenrieth

-2

您可以获取插入行的ID：

INSERT INTO albums [...] returning album_id;

插入后，这将返回 album_id。

- HARI KRISHNAN

具体而言，原帖作者想要在事务中使用下一个语句中的id。仅使用RETURNING子句是无法独立解决此问题的。 - Jordan Arsenault

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- user330315 · Accepted Answer

作为 Cody 提到的 INSERT ... RETURNING 子句的另一种选择，您可以使用与 ID 列关联的序列的当前值：

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

这假设在创建序列时，使用了Postgres的标准命名方案，用于自动为定义为serial的列创建序列。

另一种选择是，如果您只使用单个插入，则可以使用lastval()函数。这样，您甚至不需要将序列名称放入INSERT语句中。

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