多次调用带有数组参数的集合返回函数 [英] Call a set-returning function with an array argument multiple times

问题描述

这是的变体返回多列的 plpgsql 函数被多次调用.但是，我希望找到针对我的特定情况的解决方案.

我有一个函数，它处理带有给定参数的行数组，并返回一组行 + 一个新列.

CREATE OR REPLACE foo(data data[], parameter int) RETURNS SETOF Enhanceddata AS...

该函数适用于只有一组数据的测试用例

SELECT * FROM foo( (SELECT ARRAY_AGG(data) FROM datatable GROUP BY dataid WHERE dataid = something), 1)

但我想让它处理多组数据，而不将 dataid 传递给函数.我尝试了多种变体:

SELECT dataid, (foo(ARRAY_AGG(data)),1).*来自数据集WHERE dataid = 某物——仅在 1 上进行测试GROUP BY 数据ID

但是该函数会为每一列调用一次.

解决方案

在 Postgres 9.3 或更高版本中，通常最好使用 LEFT JOIN LATERAL ... ON true:

SELECT sub.dataid, f.*从  (SELECT dataid, array_agg(data) AS arr来自数据集WHERE dataid = 某物按 1 分组) 子LEFT JOIN LATERAL foo(sub.arr) f ON true;

如果函数 foo() 可以返回无行，这是安全的形式，因为它保留连接左侧的所有行，即使没有返回行向右.

否则，或者如果您想要从横向连接中排除没有结果的行，请使用:

横向交叉连接 foo(sub.arr)

或简写:

, foo(sub.arr)

手册中有明确提及.>

Craig 的相关答案(由 Daniel 引用)相应更新:

• 如何避免在 SQL 查询中使用 (func()).* 语法进行多个函数评估?

This is a variation on plpgsql function that returns multiple columns gets called multiple times. However, I was hoping to find a solution to my particular set of circumstances.

I have a function that processes an array of rows with a given parameter, and returns a set of rows + a new column.

CREATE OR REPLACE foo(data data[], parameter int) RETURNS SETOF enhanceddata AS
...

The function works on a test case with only 1 set of data

SELECT * FROM foo( (SELECT ARRAY_AGG(data) FROM datatable GROUP BY dataid WHERE dataid = something), 1) 

But I would like to make it work with multiple groups of data, without passing a dataid to the function. I tried a number of variations of:

SELECT dataid, (foo(ARRAY_AGG(data)),1).*
FROM dataset
WHERE dataid = something -- only testing on 1
GROUP BY dataid

But the function gets called once for every column.

解决方案

In Postgres 9.3 or later, it's typically best to use LEFT JOIN LATERAL ... ON true:

SELECT sub.dataid, f.*
FROM  (
   SELECT dataid, array_agg(data) AS arr
   FROM   dataset
   WHERE  dataid = something
   GROUP  BY 1
   ) sub
LEFT   JOIN LATERAL foo(sub.arr) f ON true;

If the function foo() can return no rows, that's the safe form as it preserves all rows to the left of the join, even when no row is returned to the right.

Else, or if you want to exclude rows without result from the lateral join, use:

CROSS JOIN LATERAL foo(sub.arr)

or the shorthand:

, foo(sub.arr)

There is an explicit mention in the manual.

Craig's related answer (referenced by Daniel) is updated accordingly:

• How to avoid multiple function evals with the (func()).* syntax in an SQL query?

这篇关于多次调用带有数组参数的集合返回函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！