Reverse "expression operator ANY (array expression)"



  • 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).



  • I believe the only way to do this is using an EXISTS

    SELECT *
    FROM foo
    WHERE EXISTS(SELECT 1
        FROM (VALUES(bar), (baz), (qux)) v(Val)
        WHERE v.Val ~ 'ab.'
    );
    

    https://dbfiddle.uk/?rdbms=postgres_14&fiddle=193c9201046f6d560fc1ac6f41edb148

    This may be more or less verbose and/or performant than just writing each comparison individually.


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2