多次调用带有数组参数的set-returning函数 [英] 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).*
FROM dataset
WHERE dataid = something -- only testing on 1
GROUP BY dataid

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

解决方案

在Postgres 9.3或更高版本中，通常最好使用 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;

如果函数foo()不能返回没有行，那是安全的形式，因为它保留了连接左侧的所有行，即使没有行返回到右侧.

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

CROSS JOIN LATERAL 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?

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