Postgres Namedquery In Clause

Ignition
,
1

Hi everyone,

I'm currently in the process of creating a named query that will fetch valve_data from a postgres database. This is done by running the following code:

SELECT * FROM valve_data WHERE valve_name IN (:tags )

For some reason this query isn't returning any value when I test it with the following value for :tags:
('CM_670BFV099030')

But when I run this in the Postgres database itself it does return values. Is my syntax for the tags wrong? I think I tried every possible method.

image
image636×241 21.3 KB

If anyone has any idea..

Thanks in advance.

2

Trying to parameterize values for the in key word is troublesome. I think it is considered query structure and not just a value substitution. You would have to change the parameter from value to queryString. This is usually not recommended unless your parameters are sanitized (see SQL injection).

3

Example

select *
from table
where id in {values}

values = "(1,2,3)"

more info
Using a querystring parameter in a named query IN clause - Ignition - Inductive Automation Forum

Ignition Perspective Named Query with WHERE IN Clause - Ignition - Inductive Automation Forum

4

Since you are using PostgreSQL, a competent database, you have another option, as long as you use a "prep" query via script instead of a Named Query. See this discussion: