5

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?

Improve this question

3 Answers 3

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

How do you negate it, to find ids NOT in the array? Simply using != doesn't work for me.
@Cerin :please use != all instead of != any
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.

Improve this answer

Comments

1

i usually use implicit conversion

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

Improve this answer

1 Comment

Question was not about how to get integer array ) I have such array by default from input params.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.