从PostgreSQL存储过程返回记录集的最简单方法是什么？

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,然后像光标一样循环遍历记录集,但是在更新版本的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.

请注意“RETURNS setof [table name]”。但它似乎并不是最灵活的。如果我尝试返回多个表的连接,它就会崩溃。

2 个解决方案

#1

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;

#2

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.

这实际上与使用SETOF tablename相同,但是声明表结构内联而不是引用现有对象,因此连接等仍然有效。

#1