函数将多列作为单列而不是多列返回 [英] function returns multiple columns as a single column instead of multiple columns

问题描述

我当前正在使用Postgresql 9.04编写一个函数，其中我试图使用将在select语句中使用的变量，然后返回结果。

I am currently writing a function in postgresql 9.04 where I am attempting to use a variable which will be used in a select statement then return the results.

该语句我想出的简单而有效；但是，所有列都输出到单个列而不是多个列。

The statement I have come up with is simple and works; however, all the columns are outputing to a single column instead of multiple columns.

这是我的功能：

create or replace function foo(IN pID integer, OUT project_id integer, OUT project_name    text, OUT project_type text, OUT project_description text, OUT project_status text)
returns setof record as

$$
select project_id, project_name, project_type, project_description, project_status from     t_projects
where project_id = $1;
$$

LANGUAGE SQL;


select foo(6) -- runs function

当前输出如下：

"(6,"test project","inbound","inbound test","processing")"

我该怎么做，以免将结果连接在一起并返回每个列项目

how can I make it so the results are not concatenated together and return each column item seperately?

预先感谢您。

推荐答案

您需要像这样调用函数：

you need to call the function like this:

select * from foo(6);

这将返回以下内容：

project_id | project_name | project_type | project_description | project_status
-----------|--------------|--------------|---------------------|----------------
         6 | test project |      inbound |        inbound test |     processing

这是一种Postgres怪癖，可以同时调用它并给您带来结果。您可能想查看设置返回函数的文档，还有其他方法也可以执行此操作。哦，上面有一个为plpgsql编写的Wiki页面，但大多数页面也适用于sql函数： http://wiki.postgresql.org/wiki/Return_more_than_one_row_of_data_from_PL/pgSQL_functions

it's a quirk of postgres that it can be called both ways and give you a result. you might want to check the docs on set returning functions some more, there are other ways to do this as well. Oh, there is a wiki page on it, written for plpgsql, but most applies to sql functions as well: http://wiki.postgresql.org/wiki/Return_more_than_one_row_of_data_from_PL/pgSQL_functions

这篇关于函数将多列作为单列而不是多列返回的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！