1

I'm trying to create a small function that will return all the clients in an array but I keep bumping into errors.

DO $$
DECLARE
    c_ids INTEGER[] := array[12879,16759];
BEGIN
  SELECT *
  FROM   client
  WHERE  id IN (c_ids);
END $$;

What would be the right way to do something like this? I need the c_ids to be a variable because I will be using it in many places in the query (the sample straight forward query is not the end query).

Bernardo
  • 475
  • 1
  • 5
  • 19
  • Not a super pro at postgres but I believe you need to use curly braces for the data. This post is an example with a loop but I think it might help you https://stackoverflow.com/questions/9783422/postgres-array-for-loop – Jacob H Feb 23 '18 at 15:31
  • @JacobH The curly braces are for writing an array as a string e.g. `'{1,2,3}'` vs `ARRAY[1,2,3]`. End result is the same. – 404 Feb 23 '18 at 16:31
  • @eurotrash Thanks for the explanation, that makes sense. – Jacob H Feb 23 '18 at 16:35

1 Answers1

5

The IN operator compares values using the = operator. If you want to check values in an array, you need to use ANY:

DO $$
DECLARE
    c_ids INTEGER[] := array[12879,16759];
BEGIN
  SELECT *
  FROM   client
  WHERE  id = ANY (c_ids);
END $$;