Function using '= ALL ($1)' returning no values when multiple params passed

NaN 0
Viewed 21
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;

If I call the function above with select test('123') it works fine. If I call it with select test('123','234') it returns nothing ie

test 
------
(0 rows)

However if I define it as

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;

then the function returns all the data but those that fit the condition

Any ideas??

postgresql
1 Answers

ALL was used as opposed to ANY the corrected function looks like this

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;
NaN0
Related