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
– shawnt00, Commented Sep 18, 2022 at 6:41
@shawnt00 thats a no go. I tried it... also the last comment on this post stackoverflow.com/questions/17138792/… 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
– NaN, Commented Sep 18, 2022 at 6:45
@shawnt00 I id'd the problem. Answer below. Ty for your time — NaN
– NaN, Commented Sep 18, 2022 at 7:02

NaN · Accepted Answer · 2022-09-18 07:01:43Z

1

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;

answered Sep 18, 2022 at 7:01

NaN

1251 silver badge12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related