how to evalute expression values with parameter in postgres sql

Question

I was trying to evaluate a regular expression in PostgreSQL in where clause.

I have a column with value like the example (1,2) below. When I write a query in where clause I am passing parameter values as either "a" or "a,b" or "b" then it should return only the rows that contain "a" or "a,b" or "b".

When I pass "a" then it should check the row value and evaluate the "AND" , "OR" conditions as well.

Example:1

((((a.b.city == "x" AND a.b.state == "y" AND a.b.country == "z")) OR 
   (dis(x.y.d1, x.y.d2, "47.6015", "-122.3304") <= 5)) 
  AND ((p.q.test == "**a**") OR (p.q.test == "b")))

Example:2

((((a.b.city == "x" AND a.b.state == "y" AND a.b.country == "z")) OR
   (dis(x.y.d1, x.y.d2, "123", "-456") <= 5)) AND ((p.q.test == "a,b")

Here is a sample query.

select * from testtable where  column ='parameter'

Please suggest.

For a question like this, a table of sample data and expected results makes things a lot easier to understand. So does cutting out unrelated noise in your queries. Please always supply your PostgreSQL version. — Craig Ringer
– Craig Ringer, Commented Dec 26, 2013 at 8:28

Craig Ringer · Accepted Answer · 2013-12-26 08:27:12Z

It's really hard to figure out quite what you're asking, but I think you want IN(...).

E.g.

 p.q.test IN ('a', 'b')

is equivalent to

 p.q.test = 'a' OR p.q.test = 'b'

You can pass an array as a query parameter if the list to match is dynamic:

 p.q.test IN (?)

or a comma-separated list to match against:

 p.q.test = ANY (regexp_split_to_array(?,','))

If your p.q.test is a comma-separated value list, then split it, and compare it to an array of possible matches using the && (array overlaps) operator:

WITH test(x) AS (VALUES ('a,b'), ('a'), ('b'), ('a,b,c'), ('b,c'), ('d,e'), ('e')) 
SELECT x, regexp_split_to_array(x, ',') && ARRAY['a','b'] FROM test;

   x   | ?column? 
-------+----------
 a,b   | t
 a     | t
 b     | t
 a,b,c | t
 b,c   | t
 d,e   | f
 e     | f
(7 rows)

If && doesn't quite match what you want, look at the arrays manual; maybe the @> or <@ (contains / contained by) operators are what you need.

Collectives™ on Stack Overflow

how to evalute expression values with parameter in postgres sql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related