How to pass integer array into IN clause?

Question

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

Into what and how I can convert such array?

score 5 · Accepted Answer · answered Nov 01 '10 at 12:55

5

I had same problem lately.

Look here:

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

Hope it helps.

Here is a sample 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

answered Nov 01 '10 at 12:55

rsc

4,234
2
30
28

How do you negate it, to find ids *NOT* in the array? Simply using != doesn't work for me. – Cerin Feb 10 '11 at 13:42
1

@Cerin :please use `!= all` instead of `!= any` – rsc Feb 11 '11 at 08:42

score 1 · Answer 2 · edited May 23 '17 at 12:21

1

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

Unless this (PostgreSQL) is what you're talking about.

edited May 23 '17 at 12:21

Community

1
1

answered Nov 01 '10 at 12:43

egrunin

24,650
8
50
93

score 1 · Answer 3 · answered Nov 02 '10 at 04:52

1

i usually use implicit conversion

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

answered Nov 02 '10 at 04:52

Bonshington

3,970
2
25
20

Question was not about how to get integer array ) I have such array by default from input params. – Timur Sadykov Nov 03 '10 at 15:34

How to pass integer array into IN clause?

3 Answers3