All we need is an easy explanation of the problem, so here it is.
Postgres conveniently allows :
SELECT * FROM foo WHERE bar ~ ANY (?)
Where ? is an array of any 1+ regex patterns for column "bar" to match.
How can I elegantly do the opposite – pick out rows where 1+ columns match one regex ?
SELECT * FROM foo WHERE ANY (bar, baz, qux) ~ ?
I pored over Row and Array Comparisons, and Functions and Operators, and more, but docs are light on examples.
Feels like I’ve got all the pieces, but can’t connect ’em.
"row_constructor operator row_constructor" seems closer to what I need, but ~ is not available :
Row constructor comparisons are allowed when the operator is =, <>, <, <=, > or >=.
Besides which, I’d have to somehow explode 1 bound regex pattern into a row on the right-hand side.
Postgres complains that the right-hand side must be an array, when I’ve tried.
It’s not clear to me whether Composite Type Comparisons would support "~" :
Composite type comparisons are allowed when the operator is =, <>, <, <=, > or >=, or has semantics similar to one of these.
Besides which, I’m even more lost as to how I’d construct multiple columns into a record on the left side, and 1 regex pattern into a record on the right of the operator "~".
This is an approximation of what I want in behaviour :
SELECT * FROM foo WHERE CONCAT(bar::text, baz::text, qux::text) ~ ?
but not ideal for reasons you can guess (and requires special handling/escaping depending on the regex pattern).
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
I believe the only way to do this is using an
SELECT * FROM foo WHERE EXISTS(SELECT 1 FROM (VALUES(bar), (baz), (qux)) v(Val) WHERE v.Val ~ 'ab.' );
This may be more or less verbose and/or performant than just writing each comparison individually.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂