在PostgreSQL事务块中获取ID [英] Getting an ID inside a PostgreSQL transaction block

问题描述

我正在尝试将所有应有的交易打包为 BEGIN 和 COMMIT ，但是在以下情况下我不确定如何做到这一点。

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.

我有3张桌子，一张用于图片，一张用于相册，以及它们之间的关系之一，即 album_images 。该系统的工作方式是用户可以创建一个相册，并通过一次操作将其图像填充到相册中。 SQL如下：

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 SQL 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.

推荐答案

作为Cody提到的INSERT ... RETURNING子句的替代方法，您可以使用与ID列关联的序列的当前值：

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;

假定在自动为定义为<$ c $的列创建序列时，采用Postgres的标准命名方案c>序列。

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

另一种替代方法（如果仅使用单个插入）是使用 lastval（）函数。这样，您甚至不需要将序列名称放入INSERT语句中：

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;

这篇关于在PostgreSQL事务块中获取ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！