arrays - 以多维数组为参数的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 值，并在尝试 FOREACH $1 时导致异常:

SELECT reduce_to_percentages(counts) FROM objects;

Answer 1

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

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;

相关:

• Unnest array by one level

注意事项:

• 将红利 转换为float 就足够了。 float/integer 自动返回 float。

• 没有数据类型 int[][]。只是 int[]。 Postgres 允许这种表示法，但忽略数组类型的附加数组维度，因为数据类型本身对于所有维度都是相同的。详情:

  • mapping postgresql text[][] type and Java type

• 使函数IMMUTABLE(因为它是)以获得更好的性能并允许索引。相关:

  • How do IMMUTABLE, STABLE and VOLATILE keywords effect behaviour of function?
  • Does PostgreSQL support "accent insensitive" collations?

---

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

CREATE OR REPLACE FUNCTION public.reduce_to_percentages(int[][])
  RETURNS float[] AS
$function$
 ...
$function$ LANGUAGE plpgsql IMMUTABLE <b>STRICT</b>;

The manual:

RETURNS NULL ON NULL INPUT or STRICT indicates that the function always returns null whenever any of its arguments are null. If this parameter is specified, the function is not executed when there are null arguments; instead a null result is assumed automatically.

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