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

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;

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??

I don't know how Postgres handles parameters but perhaps you just want `in ($1)`? — shawnt00, Sep 18 '22 at 06:41
@shawnt00 thats a no go. I tried it... also the last comment on this post https://stackoverflow.com/questions/17138792/pass-array-literal-to-postgresql-function suggest that the operator use is correct. So Im not sure `ERROR: operator does not exist: text = text[] LINE 1: ...CROSS JOIN jsonb_each(data) WHERE key IN ($1)' L...` — NaN, Sep 18 '22 at 06:45
@shawnt00 I id'd the problem. Answer below. Ty for your time — NaN, Sep 18 '22 at 07:02

score 1 · Accepted Answer · answered Sep 18 '22 at 07:01

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;

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

1 Answers1