在 PostgreSQL 中返回插入的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2276574/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Returning inserted rows in PostgreSQL
提问by Dusty
I'm currently working on a report generation servlet that agglomerates information from several tables and generates a report. In addition to returning the resulting rows, I'm also storing them into a reports table so they won't need to be regenerated later, and will persist if the tables they're drawn from are wiped. To do the latter I have a statement of the form (NB: x is externally generated and actually a constant in this statement):
我目前正在开发一个报告生成 servlet，该 servlet 聚合来自多个表的信息并生成报告。除了返回结果行之外，我还将它们存储到报告表中，以便以后不需要重新生成它们，并且如果它们从中提取的表被擦除，它们将持续存在。要做到后者，我有一个形式的声明（注意：x 是外部生成的，实际上是该声明中的一个常量）：
INSERT INTO reports
(report_id, col_a, col_b, col_c)
SELECT x as report_id, foo.a, bar.b, bar.c
FROM foo, bar
This works fine, but then I need a second query to actually return the resulting rows back, e.g.
这工作正常，但是我需要第二个查询来实际返回结果行，例如
SELECT col_a, col_b, col_c
FROM reports
WHERE report_id = x
This works fine and since it only involves the single table, shouldn't be expensive, but seems like I should be able to directly return the results of the insertion avoiding the second query. Is there some syntax for doing this I've not been able to find? (I should note, I'm fairly new at DB work, so if the right answer is to just run the second query, as it's only slightly slower, so be it)
这工作正常，因为它只涉及单个表，不应该很昂贵，但似乎我应该能够直接返回插入的结果，避免第二次查询。是否有一些我找不到的语法来执行此操作？（我应该注意，我对数据库工作还很陌生，所以如果正确的答案是只运行第二个查询，因为它只是稍微慢了一点，就这样吧）
回答by Matthew Wood
In PostgreSQL with version >= 8.2, you can use this construct:
在版本 >= 8.2 的 PostgreSQL 中，您可以使用以下结构：
INSERT INTO reports (report_id, col_a, col_b, col_c)
SELECT x as report_id, foo.a, bar.b, bar.c
FROM foo, bar
RETURNING col_a, col_b, col_c
回答by dman
Or without select:
或者不选择：
INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets')
RETURNING did;
回答by Joshua D. Drake
You could also use an SRF although that may be overkill. It depends on what you are trying to do. For example, if you are only returning the information to perform a piece of logic that will go directly back to the database to perform more queries, it may make sense to use an SRF.
您也可以使用 SRF，尽管这可能有点矫枉过正。这取决于您要尝试做什么。例如，如果您只返回信息以执行将直接返回数据库以执行更多查询的一段逻辑，则使用 SRF 可能是有意义的。
http://wiki.postgresql.org/wiki/Return_more_than_one_row_of_data_from_PL/pgSQL_functions
http://wiki.postgresql.org/wiki/Return_more_than_one_row_of_data_from_PL/pgSQL_functions
