plpgsql函数，将多维数组作为参数

Question

我正试图在选择查询中的多维数组列(int[][])上运行plpgsql函数。

功能如下：

CREATE OR REPLACE FUNCTION public.reduce_to_percentages(int[][])
RETURNS float[] AS
$function$
DECLARE
s int[];
a float[];
BEGIN
FOREACH s SLICE 1 IN ARRAY $1 LOOP
a := a || s[2]::float / s[1]::float;
END LOOP;
RETURN a;
END;
$function$
LANGUAGE plpgsql VOLATILE;

以下查询有效：

SELECT reduce_to_percentages(ARRAY[ARRAY[100, 20], ARRAY[300, 50]]);

以下查询也是如此：

SELECT reduce_to_percentages((SELECT counts FROM objects LIMIT 1));

但是以下查询为函数提供了null值，并在尝试通过$1:进行FOREACH时导致异常

SELECT reduce_to_percentages(counts) FROM objects;

Answer 1

您可以通过检查提供的NULL值(如@klin)来修复错误
但那是在猪身上涂口红。重写函数以使用基于集合的解决方案替换过程循环。通常更快(尤其是在外部查询的上下文中使用时)、更简单且自动为空安全：

CREATE OR REPLACE FUNCTION public.reduce_to_percentages(int[])
RETURNS float[] AS
$func$
SELECT ARRAY (SELECT $1[d][2]::float / $1[d][1]
FROM   generate_subscripts($1,1) d)
$func$  LANGUAGE sql IMMUTABLE;

相关：

• 按一级取消对数组的排序

备注：

• 将红利投射到float就足够了。float / integer自动返回float。

• 没有数据类型int[][]。只有int[]。Postgres允许这种表示法，但忽略了数组类型的额外数组维度，因为所有维度的数据类型本身都是相同的。详细信息：

  • 映射postgresql text[]类型和Java类型

• 使函数IMMUTABLE(因为它是)具有更好的性能并允许索引。相关：

  • 不可更改、稳定和可变关键字如何影响函数的行为
  • PostgreSQL支持"；不区分重音"；校勘

---

或者，您可以将函数声明为STRICT(同义词：RETURNS NULL ON NULL INPUT)。微小的区别：它为NULL输入返回NULL，而不是像上面那样的空数组('{}')。

CREATE OR REPLACE FUNCTION public.reduce_to_percentages(int[][])
RETURNS float[] AS
$function$
...
$function$ LANGUAGE plpgsql IMMUTABLESTRICT;

手册：

RETURNS NULL ON NULL INPUT或STRICT表示函数每当它的任何参数为null时，总是返回null。如果如果指定了参数，则当存在空参数；而是自动假定为空结果。

但由于多种原因，上面的简单SQL函数更可取。

Answer 2

在函数体中，只需检查参数是否为空。如果要为null参数返回一个空数组，请在声明部分为变量a添加一个初始值(否则函数将返回null)。

CREATE OR REPLACE FUNCTION public.reduce_to_percentages(int[][])
RETURNS float[] AS
$function$
DECLARE
s int[];
a float[] = '{}';     -- initial value
BEGIN
IF $1 IS NOT NULL THEN
FOREACH s SLICE 1 IN ARRAY $1 LOOP
a := a || s[2]::float / s[1]::float;
END LOOP;
END IF;
RETURN a;
END;
$function$
LANGUAGE plpgsql VOLATILE;

---

我做了一个简单的测试。这是一个适度的设置：

create table test (val int[]);
insert into test
select (array[array[ri(100), ri(100)], array[ri(100), ri(100)]])
from generate_series(1, 1000000);

ri(100)是我的函数，返回1-100之间的随机整数。因此，该表包含一百万行，其中一列中有两个由两个整数组成的数组。我试着让测试尽可能简单和典型。

以下查询已经用Erwin的函数执行了10次，用我的变体执行了10遍：

select sum(v[1]), sum(v[2])
from (
select reduce_to_percentages(val) v
from test
) s;

十次测试的平均执行时间：

• 欧文函数11940ms
• klin函数4750ms

也许我的功能是鼻子上涂着口红的猪，但它是一只速度很快的猪。