从PostgreSQL存储过程返回记录集的最简单方法是什么? [英] What's the easiest way to return a recordset from a PostgreSQL stored procedure?

问题描述

我只是有一个表格，其中包含国家/地区及其ISO国家/地区代码的列表.我将查询包装在存储过程(又称函数)中，例如:

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:

CREATE OR REPLACE FUNCTION get_countries(
                    ) RETURNS setof record AS $$
        SELECT country_code, country_name FROM country_codes
$$ LANGUAGE sql;

我得到的错误是:

ERROR:  a column definition list is required for functions returning "record"

我知道我可以定义一个TYPE，然后像游标一样遍历记录集，但是IIRC有一种更好的方法可以在较新版本的PostgreSQL(我使用的是8.4.3)下执行此操作，但是我正在拉试图记住的头发.

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.

这有效:

CREATE OR REPLACE FUNCTION get_countries(
                    ) RETURNS setof country_codes AS $$
        SELECT country_code, country_name FROM country_codes
$$ LANGUAGE sql;

注意"RETURNS setof [表名]".但这似乎不是最灵活的.如果我尝试返回多个表的联接，则会崩溃.

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.

推荐答案

您应该能够使用输出参数，如下所示:

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;

这篇关于从PostgreSQL存储过程返回记录集的最简单方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！