How to cast string input into "where in"

Question

I have a Thingworx Service (SQL Query) in my Database Thing, eg. GetCookiesByIds

I have a parameter ids (STRING) with value 1,2,3

I would like to make a query:

SELECT * FROM Cookies WHERE id IN ([[ids]])`

But I have an exception.

How to cast my parameter ids into the valid format?

Answer 1

A simple an portable option is:

select *
from cookies
where ',' || $1 || ',' like '%,' || id || ',%'

... where $1 represents the CSV string that is the parameter to your query.

In Postgres, we can also use arrays:

select *
from cookies
where id = any(string_to_array($1, ','))

Answer 2

A)

SELECT * FROM cookies WHERE id = any([[ids]]::int[])

The value of ids must be like: {1,2,3}

---

B)

SELECT * FROM cookies WHERE id = any(
    CONCAT( '{', [[ids]], '}' ) ::int[]
)

The value of ids must be like: 1,2,3

Answer 3

The syntax if you want to do that is like this

SELECT * FROM Cookies WHERE id IN (<<ids>>)

The Thingworx service will concatenate your input parameter in the query if you use <<>> and define it as actual parameters if you use [[]]

Answer 4

Add some more details to Tom V's correct answer...

CONTEXT

Surrounding the input parameter with double square brackets [[IDs]] is parameter substitution, which treats the input as a parameter ONLY. In order to pass a string variable in the input parameter, you must use the double angle brackets <<IDs>> which allows for string substitution.

But be WARNED, string substitution can expose your database to SQL injection so be sure to sanitize the input and secure the services using string substitution.

EXAMPLE

Input Parameter: IDs (STRING)
[the value can have a space but MUST be comma separated to return multiple values]

IDs = 26,504

Output Parameter: result (Infotable)
[in my example an Infotable is returned, but the output can be any data type]

Query:

SELECT * FROM Cookies WHERE id IN (<<IDs>>);

(Referenced article can be found here)

image shows id casting