使用"= ALL ($1)"的函数在传递多个参数时不返回任何值

Question

CREATE FUNCTION test(VARIADIC arr text[]) 
RETURNS TABLE (data_time TIMESTAMPTZ, id text, data jsonb) 
AS 'SELECT data_timestamp, key, value 
FROM hit_count 
CROSS JOIN jsonb_each(data) 
WHERE key = ALL ($1)' 
LANGUAGE SQL;

如果我用select test('123')调用上面的函数，它可以正常工作。如果我用select test('123','234')调用它，它什么也不返回，即

test 
------
(0 rows)

但是，如果我将其定义为

CREATE FUNCTION test(VARIADIC arr text[]) 
RETURNS TABLE (data_time TIMESTAMPTZ, id text, data jsonb) 
AS 'SELECT data_timestamp, key, value 
FROM hit_count 
CROSS JOIN jsonb_each(data) 
WHERE key != ALL ($1)' 
LANGUAGE SQL;

则函数返回除符合条件之外的所有数据

有什么想法吗？？

Answer 1

ALL被用来代替ANY，修正后的函数看起来像这个

CREATE FUNCTION test(VARIADIC arr text[]) 
RETURNS TABLE (data_time TIMESTAMPTZ, id text, data jsonb) 
AS 'SELECT data_timestamp, key, value 
FROM hit_count 
CROSS JOIN jsonb_each(data) 
WHERE key = ANY ($1)' 
LANGUAGE SQL;