传递一个数组的数组作为参数传递给函数 [英] Passing an array of arrays as parameter to a function
问题描述
Web应用程序可以发送到功能的数组的数组像
A web application can send to a function an array of arrays like
[
[
[1,2],
[3,4]
],
[
[],
[4,5,6]
]
]
外数组的长度 N'GT; 0 。中间阵列在本实施例是恒定长度，2。和内部数组长度ñ方式＆gt; = 0
The outer array length is n > 0. The middle arrays are of constant length, 2 in this example. And the inner arrays lengths are 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
不过，我想我可以这样做更好
But I think I can do better like this
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的只能处理匹配的尺寸阵列和传递内部数组可以在尺寸上有所不同。通过增加像零一些特殊的价值，使他们最长的一个长度相同传递之前，我可以修理他们，但我认为这是清洁处理的函数内部。
Notice that a text array was passed and then "casted" inside the function. That was necessary as Postgresql can only deal with arrays of matched dimensions and the passed inner arrays can vary in dimension. I could "fix" them before passing by adding some special value like zero to make them all the same length of the longest one but I think it is cleaner to deal with that inside the function.
我缺少的东西吗？它是最好的办法？
Am I missing something? Is it the best approach?
推荐答案
我喜欢你的第二个方法。
I like your second approach.
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 = '{}')
;
暗示只有轻微的改进：
Suggesting only minor improvements:
-
子查询，而不是为热膨胀系数表现略好。
Subqueries instead of CTEs for slightly better performance.
简化了空数组试验：检查对文字'{} 而不是函数调用
Simplified test for empty array: checking against literal '{}' instead of function call.
有关展开阵列少了一个子查询的水平。
One less subquery level for unwrapping the array.
结果：
a | b
--+---
2 | 3
7 | 4
1 | 4
5 | 1
对于一般读者：包装纸整数的多维数组是必要的，因为Postgres的要求（报价错误信息）：
For the casual reader: Wrapping the multi-dimensional array of integer is necessary, since Postgres demands that (quoting error message):
多维数组必须有相匹配的尺寸阵列前pressions
multidimensional arrays must have array expressions with matching dimensions
在备用路由将与一个 2维的文字排列并UNNEST它使用 generate_subscripts（）：
An alternate route would be with a 2-dimensional text array and unnest it using 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 = '{}');
可能会更快，可以测试？
Might be faster, can you test?
在之前的版本9.3人会使用一个明确的 CROSS JOIN 而不是横向交叉连接。
In versions before 9.3 one would use an explicit CROSS JOIN instead of lateral cross joining.
这篇关于传递一个数组的数组作为参数传递给函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
