arrays - 将数组的数组作为参数传递给函数

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

请注意，传递了一个文本数组，然后将其“转换”到函数内部。这是必要的，因为 Postgresql 只能处理匹配维度的数组，并且传递的内部数组的维度可能不同。我可以在传递之前“修复”它们，方法是添加一些特殊值(如零)以使它们与最长的长度相同，但我认为在函数内部处理它更清晰。

我错过了什么吗？这是最好的方法吗？

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. 子查询而不是 CTE 以获得更好的性能。

2. 空数组的简化测试:检查文字 '{}' 而不是函数调用。

3. 减少一个用于展开数组的子查询级别。

结果:

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

对于普通读者:包装多维整数数组是必要的，因为 Postgres 要求(引用错误消息):

multidimensional arrays must have array expressions with matching dimensions

替代路线 将使用二维文本数组 并使用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 而不是横向交叉连接。