将postgres函数与查询结合 [英] Combine postgres function with query

问题描述

我目前正在处理我在结果集中需要的sql函数的输出：

I currently struggle with the output of a sql-function which I require in my result-set:

SELECT getAdditionalInfoAboutDate(date) from sampleCalendar

问题是，我通过以下方式获取结果：

The problem is, that I get the result in the following way:

1. Attribute1，Attribute2，Attribute3


2. Attribute2，Attribute3，Attribute4


3. ...

如您所见，我得到了我想要的结果，但是即使我的函数只有一列返回3列。

As you can see I get my desired result but it only has one column even though my function returns 3 columns.

当我尝试创建如下语句时：

When I try to create a statement like:

SELECT (Select * from getAdditionalInfoAboutDate(date)) from sampleCalendar

中选择（从getAdditionalInfoAboutDate（date *中选择*））仅返回一列。

I get the exception "subquery must return only one column".

我是否有机会以某种方式解决此问题？

Do I have the chance to solve this somehow?

编辑：找到了回答 HERE ：

SELECT getAdditionalInfoAboutDate(date).* from sampleCalendar

推荐答案

即使您找到的答案是我的，根据您的函数实际返回的内容，可能会有更好的答案：一行还是多行？假设个行。

Even though the answer you found is from me, there may be a better answer depending on what your function actually returns: one or multiple rows? Assuming one row.

无论哪种方式，问题中的内容都是不正确 。 该行类型必须用括号括起来才能分解，否则语法可能会模棱两可。它必须是：

Either way, what you have in your question is incorrect. The row type must be enclosed in parentheses to decompose it, else the syntax could be ambiguous. It must be:

SELECT (getAdditionalInfoAboutDate(date)).* FROM sampleCalendar;

不过，Postgres在这里的优势很弱。它将多次评估函数-结果中的每一列一次。要优化性能，请将函数调用放在子查询中：

However, Postgres has a weak spot here. It would evaluate the function multiple times - once for every column in the result. To optimize performance put the function call in a subquery:

SELECT (f_row).*
FROM  (SELECT getAdditionalInfoAboutDate(date) AS f_row FROM sampleCalendar) sub;

或使用 左加入横向 在9.3版中

Or use a LEFT JOIN LATERAL in pg 9.3+

SELECT f_row.*  -- no parentheses here, it's a table alias
FROM   sampleCalendar s
LEFT   JOIN LATERAL getAdditionalInfoAboutDate(s.date) f_row ON TRUE

更多详细信息：

• 从函数返回的记录具有串联的列

• Record returned from function has columns concatenated

这篇关于将postgres函数与查询结合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！