Sending array as parameter in postgresql

Question

I am trying to write a postgresql function that takes in an array and returns the users don't have id which belongs to the array. I wrote something like this:

CREATE OR REPLACE FUNCTION user_api.get_user(banned_list TEXT[])
  RETURNS SETOF JSONB
AS $$
  SELECT to_jsonb(result)
  FROM (
    SELECT
           *
        FROM my_user.user_info
        WHERE my_user.user_info.user_id NOT IN (banned_list::TEXT[])

  ) AS result;
$$ LANGUAGE SQL SECURITY DEFINER;

But it's throwing error like this

ERROR:  operator does not exist text <> text[]
LINE 24: ...     WHERE my_user.user_info.user_id NOT IN (no...
                                                              ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

I tried using CAST function too, doesn't work.

Thanks in advance for help.

klin · Accepted Answer · 2017-10-12 16:50:34Z

1

You cannot use the IN operator on an array. You should use ALL() instead:

    WHERE my_user.user_info.user_id <> ALL(banned_list)

Note, that this will work well only if the type of my_user.user_info.user_id is text. If it is an integer column then you should use an array of integers as the argument:

CREATE OR REPLACE FUNCTION user_api.get_user(banned_list int[])
  RETURNS SETOF JSONB
AS $$
  SELECT to_jsonb(result)
  FROM (
    SELECT
           *
        FROM my_user.user_info
        WHERE my_user.user_info.user_id <> ALL(banned_list)

  ) AS result;
$$ LANGUAGE SQL SECURITY DEFINER;

answered Oct 12, 2017 at 16:50

klin

123k15 gold badges240 silver badges262 bronze badges

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

Collectives™ on Stack Overflow

Sending array as parameter in postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related