将一个数组的数组作为参数传递给一个函数

Question

一个 Web 应用程序可以发送一个类似以下的 数组的数组 给函数：

[
    [
        [1,2],
        [3,4]
    ],
    [
        [],
        [4,5,6]
    ]
]

外层数组长度为n > 0。中间的数组长度是恒定的，在这个例子中为2。而内部数组的长度为n >= 0。

我可以像这样构建字符串：

with t(a, b) as (
    values (1, 4), (2, 3), (1, 4), (7, 3), (7, 4)
)
select distinct a, b
from t
where
    (a = any(array[1,2]) or array_length(array[1,2],1) is null)
    and
    (b = any(array[3,4]) or array_length(array[3,4],1) is null)
    or
    (a = any(array[]::int[]) or array_length(array[]::int[],1) is null)
    and
    (b = any(array[4,5,6]) or array_length(array[4,5,6],1) is null)
;
 a | b 
---+---
 7 | 4
 1 | 4
 2 | 3

但我认为我可以更好地做到这一点

with t(a, b) as (
    values (1, 4), (2, 3), (1, 4), (7, 3), (7, 4)
), u as (
    select unnest(a)::text[] as a
    from (values
        (
            array[
                '{"{1,2}", "{3,4}"}',
                '{"{}", "{4,5,6}"}'
            ]::text[]
        )
    ) s(a)
), s as (
    select a[1]::int[] as a1, a[2]::int[] as a2
    from u
)
select distinct a, b
from
    t
    inner join
    s on
        (a = any(a1) or array_length(a1, 1) is null)
        and
        (b = any(a2) or array_length(a2, 1) is null)
;
 a | b 
---+---
 7 | 4
 2 | 3
 1 | 4

请注意，传递了一个“text数组”，然后在函数内部进行了“转换”。这是必要的，因为Postgresql只能处理匹配维度的数组，而传递的内部数组可能维度不同。我可以通过添加一些特殊值（如零）来“修复”它们，使它们的长度都与最长的长度相同，但我认为在函数内部处理这个问题会更加清晰。我有什么遗漏吗？这是最佳方法吗？

- Clodoaldo Neto

---

3维数组的格式是以文本格式包装的，还是可以选择如何传递这些值？接收这些值的函数是plpgsql吗？Postgres版本是什么？ - Erwin Brandstetter

@Erwin 我可以选择如何传递值。前端是Python，非常灵活。那种格式只是我让它工作的方式。该函数现在在SQL中运行以简化操作，但它也可以是plpgsql或plpython。Postgresql 9.3。 - Clodoaldo Neto

Answer 1

我喜欢你的第二种方法。

SELECT DISTINCT t.*
FROM   (VALUES (1, 4), (5, 1), (2, 3), (1, 4), (7, 3), (7, 4)) AS t(a, b)
JOIN   (
   SELECT arr[1]::int[] AS a1
         ,arr[2]::int[] AS b1
   FROM   (
      SELECT unnest(ARRAY['{"{1,2}", "{3,4}"}'
                         ,'{"{}"   , "{4,5,6}"}'
                         ,'{"{5}"  , "{}"}'    -- added element to 1st dimension
                         ])::text[] AS arr     -- 1d text array
      ) sub
   ) s ON (a = ANY(a1) OR a1 = '{}')
      AND (b = ANY(b1) OR b1 = '{}')
;

仅建议进行微小改进：

1. 为了获得稍好的性能，使用子查询代替CTEs。

2. 对于空数组的简化测试：检查是否与字面量'{}'相等，而不是调用函数。

3. 去掉一层子查询以展开数组。

结果：

a | b
--+---
2 | 3
7 | 4
1 | 4
5 | 1

对于普通读者：必须包装整数的多维数组，因为Postgres要求（引用错误消息）：

多维数组必须具有匹配维度的数组表达式

一个替代方法是使用二维文本数组并使用generate_subscripts()进行展开：

WITH a(arr) AS (SELECT '{{"{1,2}", "{3,4}"}
                        ,{"{}", "{4,5,6}"}
                        ,{"{5}", "{}"}}'::text[]   -- 2d text array
             )
SELECT DISTINCT t.*
FROM  (VALUES (1, 4), (5, 1), (2, 3), (1, 4), (7, 3), (7, 4)) AS t(a, b)
JOIN  (
   SELECT arr[i][1]::int[] AS a1
         ,arr[i][2]::int[] AS b1
   FROM   a, generate_subscripts(a.arr, 1) i       -- using implicit LATERAL
   ) s ON (t.a = ANY(s.a1) OR s.a1 = '{}')
      AND (t.b = ANY(s.b1) OR s.b1 = '{}');

可能会更快，你能测试一下吗？

在9.3版本之前，人们会使用显式的CROSS JOIN而不是侧向交叉连接。

- Erwin Brandstetter

---

是的，在实际函数中，“generate_subscripts”版本始终更快。执行时间从10毫秒降至5毫秒。 - Clodoaldo Neto