如何从 PostgreSQL 9.0 函数(“存储过程")获取数据集 [英] How to obtain a data set from PostgreSQL 9.0 Function ("stored procedure")

问题描述

我正在尝试从 PostgreSQL 9.0 函数获取数据集，但无法使用存储过程.

这周我是 Postgres 的新手，所以让我解释一下我的条款:

在 pgAdmin III 中我可以输入命令:

SELECT * FROM 成员；

并接收以下数据输出:

memberid 会员名1 比尔·史密斯2乔·史密斯

我尝试创建许多函数(表/SETOF/等)，非常类似于:

创建或替换函数 get_all_members()返回成员 AS 的集合'从成员中选择*；'

当我在 pgAdmin 中运行它们(或从程序中调用它们)时，我得到以下信息:

SELECT get_all_members()

结果:

get_all_members成员(1，比尔·史密斯")(2，乔·史密斯")

有没有办法像直接输入 SQL 命令一样从 FUNCTION(存储过程)中获取它作为数据集.

非常感谢您的帮助！！！

解决方案

你可以通过稍微修改你的 select 语句来获得每列的输出，如下所示:

SELECT * FROM get_all_members()

这将为您提供逐列输出，就像正常进行查询一样.

请参阅文档中的设置返回函数.p>

I'm trying to obtain a data set from a PostgreSQL 9.0 function and I'm not able to with stored procedures.

I'm new to Postgres this week so let me explain my terms:

In pgAdmin III I can enter the command:

SELECT * FROM member;

And receive the following Data Output:

memberid   membername
1          Bill Smith
2          Joe Smith

I tried creating MANY functions (tables / SETOF / etc) pretty much like:

CREATE OR REPLACE FUNCTION get_all_members()
RETURNS SET OF member AS
'select * from member;'

When I run them in pgAdmin (or call them from a program) I get the following:

SELECT get_all_members()

Results:

get_all_members
member
(1, "Bill Smith")
(2, "Joe Smith")

Is there anyway to get this as a data set from a FUNCTION (Stored Procedure) as I can with directly entering SQL commands.

You help is GREATLY appreciated!!!

解决方案

You can get the per column output by modifying your select statement a bit so it looks like so:

SELECT * FROM get_all_members()

This will give you the column by column output just like doing the query normally.

See set returning functions in the documentation.

这篇关于如何从 PostgreSQL 9.0 函数(“存储过程")获取数据集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！