简单的PostgreSQL函数返回行 [英] Simple PostgreSQL function to return rows

问题描述

如何将简单的select查询(例如 select * from客户)转换为pg中的存储过程/函数?

How do I convert a simple select query like select * from customers into a stored procedure / function in pg?

我是Postgres的新手，并且创建函数customers()作为返回表/setof 只是感觉不对，因此这里出现了问题.

I'm new to Postgres and create function customers() as returns table/setof just didn't feel right and thus the question here.

我了解proc在pg land中被称为功能".因此， 创建过程 不存在，我唯一的选择是创建视图或函数.问题是 create function x()返回setof y 返回一个用逗号分隔的值的行，未经进一步处理就无法使用(至少这是我在pgAdmin和Ruby中看到的内容)/续集).

I understand procs are called "functions" in pg land. Thus create procedure does not exist and my only options are to either create a view or a function. The issue is create function x() returns setof y returns a paren'd comma separated row of values which can't be used without further processing (at least that's what I'm seeing in pgAdmin and Ruby/Sequel).

创建函数x()返回表(...)要求我嵌入不需要的行定义.

create function x() returns table(...) requires I embed the row definition which I don't want to.

我敢肯定所有这些原因背后都有原因，但令我感到惊讶的是，最常见的用例是这个棘手的问题.

I'm sure there's a reason behind all this but I'm surprised that the most common use case is this tricky.

推荐答案

问题是创建函数x()返回y的集合"返回一个原封不动的逗号分隔的行值，未经进一步处理就无法使用

The issue is "create function x() returns setof y" returns a paren'd comma separated row values which can't be used without further processing

该函数返回一行.要分解成单独的列，请使用:

The function returns a row. To decompose into individual columns, call it with:

SELECT * FROM getcustomers();

假设函数定义了正确的返回类型.参见:

That's assuming the function defines a proper return type. See:

• 如何从PL返回多行/pgSQL函数?

CREATE FUNCTION 上的手册>应该是一个很好的起点.示例部分涵盖了该主题.

The manual on CREATE FUNCTION should be a good starting point. The example section covers this topic.

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