SQL 从 PostgreSQL 存储过程返回记录集的最简单方法是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2036093/
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
What's the easiest way to return a recordset from a PostgreSQL stored procedure?
提问by jamieb
I simply have a table that contains a list of countries and their ISO country codes. I'm wrapping the query in a stored procedure (aka function) such as:
我只是有一个包含国家列表及其 ISO 国家代码的表格。我将查询包装在一个存储过程（又名函数）中，例如：
CREATE OR REPLACE FUNCTION get_countries(
) RETURNS setof record AS $$
SELECT country_code, country_name FROM country_codes
$$ LANGUAGE sql;
The error I am getting is:
我得到的错误是：
ERROR: a column definition list is required for functions returning "record"
I know that I can define a TYPE and then loop through the recordset like a cursor, but IIRC there's a better way to do this under newer versions of PostgreSQL (I'm using 8.4.3) but I'm pulling my hair out trying to remember.
我知道我可以定义一个 TYPE 然后像游标一样循环遍历记录集，但是 IIRC 在较新版本的 PostgreSQL（我使用的是 8.4.3）下有更好的方法来做到这一点，但我正在尝试记住。
Edit:
编辑：
This works:
这有效：
CREATE OR REPLACE FUNCTION get_countries(
) RETURNS setof country_codes AS $$
SELECT country_code, country_name FROM country_codes
$$ LANGUAGE sql;
Note the "RETURNS setof [table name]". But it doesn't seem to be the most flexible. It falls apart if I attempt to return a join of several tables.
请注意“返回 setof [表名]”。但它似乎不是最灵活的。如果我尝试返回多个表的连接，它就会崩溃。
回答by codermonkeyfuel
There is also the option of using RETURNS TABLE(...)(as described in the PostgreSQL Manual), which I personally prefer:
还可以选择使用RETURNS TABLE(...)（如PostgreSQL 手册中所述），我个人更喜欢：
CREATE OR REPLACE FUNCTION get_countries()
RETURNS TABLE(
country_code text,
country_name text
)
AS $$
SELECT country_code, country_name FROM country_codes
$$ LANGUAGE sql;
This is effectively the same as using SETOF tablename, but declares the table structure inline instead of referencing an existing object, so joins and such will still work.
这实际上与 using 相同SETOF tablename，但声明表结构内联而不是引用现有对象，因此连接等仍然有效。
回答by Magnus Hagander
You should be able to use output parameters, like this:
您应该能够使用输出参数，如下所示：
CREATE OR REPLACE FUNCTION get_countries(country_code OUT text, country_name OUT text)
RETURNS setof record
AS $$ SELECT country_code, country_name FROM country_codes $$
LANGUAGE sql;
