CODEWITHSUNDEEP
sqlarraysdatabasepostgresql
Shafaet
Shafaet

Reputation: 435

Sending array as parameter in postgresql

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.

Upvotes: 1

Views: 97

Answers (1)

klin
klin

Reputation: 121494

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;

Upvotes: 1

Related Questions