How to pass an integer array to an IN clause?

Question

I want to pass an integer array from the input parameters of my postgresql function to the sql IN clause.

What and how can I convert such an array to?

Answer 1

I have had the same issue lately.

Look at here:

http://postgresql.1045698.n5.nabble.com/Using-PL-pgSQL-text-argument-in-IN-INT-INT-clause-re-post-td3235644.html

Hope this helps.

Here is an example function:

CREATE OR REPLACE FUNCTION "GetSetOfObjects"(ids text) RETURNS SETOF record AS $BODY$select * from objects where id = any(string_to_array($1,',')::integer[]);$BODY$ LANGUAGE sql IMMUTABLE 

Answer 2

Basically, you cannot. What you can do is pass a comma separated list and split it in your function. Here are some examples using SQL Server, but I'm sure they can be translated.

If this one (PostgreSQL) is what you are talking about.

Answer 3

I usually use implicit conversion

select '{1,2,3,4,5}' :: integer []