英文:
How to use results from first query in later queries within a DB Transaction
问题
DB事务的常见用例是在多个表上执行操作，这样如果其中一个操作失败，就可以轻松回滚所有操作。然而，我经常遇到的一个常见情况是希望将记录插入到多个表中，而后续的插入需要使用前面插入的序列ID。
由于ID直到事务实际提交后才生成/可用，那么如何实现这一点呢？如果必须在第一个插入之后提交以获取ID，然后执行第二个插入，那么似乎就失去了事务的目的，因为在提交之后（或者如果我根本不使用事务），如果第二个插入失败，我无法回滚第一个插入。
这似乎是DB事务的一个常见用例，我无法想象它不会以某种方式得到支持。如何实现这一点呢？
英文:
A common case for DB transactions is performing operations on multiple tables, as you can then easily rollback all operations if one fails. However, a common scenario I run into is wanting to insert records to multiple tables where the later inserts need the serial ID from the previous inserts.
Since the ID is not generated/available until the transaction is actually committed, how can one accomplish this? If you have to commit after the first insert in order to get the ID and then execute the second insert, it seems to defeat the purpose of the transaction in the first place because after committing (or if I don't use a transaction at all) I cannot rollback the first insert if the second insert fails.
This seems like such a common use case for DB transactions that I can't imagine it would not be supported in some way. How can this be accomplished?
答案1
得分: 1
cte (common table expression) with data modifying statements应该满足您的需求，请参考手册。
典型示例：
WITH cte AS (INSERT INTO table_A (id) VALUES ... RETURNING id)
INSERT INTO table_B (id) SELECT id FROM cte
在dbfiddle中查看演示。
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论