How to implement this fuzzy search sql query in Opaleye?

Question

I have an sql query that I am attempting to convert to Opaleye.

I'll simplify my code to focus on the point of my question, namely how to fuzzy search with Opaleye.

The Haskell (with some existing Opaleye structures) is

namesTable :: O.Table NamesColumnWrite NamesColumnRead
namesTable = O.Table "names" (pNames Names { id = O.optional "id"
                                            , licenseNumber = O.required "license_number"
                                            , fullName = O.required "full_name"
                                            })

import qualified Database.PostgreSQL.Simple as PGS

data Names' id' licenseNumber' fullName' =
    Names
        { id           :: id'
        , licenseNumber     :: licenseNumber'
        , fullName          :: fullName'
        } deriving (Show, Eq)

namesQuery :: Query NamesColumnRead
namesQuery = queryTable NamesTable

type NamesRead = Names' Int String String
type NamesWrite = Names' (Maybe Int) String String
type NamesColumnWrite = Names' (Maybe (O.Column O.PGInt4)) (O.Column O.PGText) (O.Column O.PGText)
type NamesColumnRead = Names' (O.Column O.PGInt4) (O.Column O.PGText) (O.Column O.PGText)

getNamesByFuzzyLicenseNumber :: PGS.Connection -> String -> IO [NamesRead]
getNamesByFuzzyLicenseNumber conn licNumber = do
  let query' = "SELECT id, license_number, full_name FROM get_names_by_fuzzy_license_number_fn(?)"
  PGS.query conn query' [licNumber]

And the Sql is

CREATE OR REPLACE FUNCTION get_names_by_license_number_fuzzy_fn(p_license_number VARCHAR)
  RETURNS TABLE
    (id INT
    ,license_number VARCHAR
    ,full_name TEXT
    )
AS $$
  SELECT
    id,
    license_number,
    full_name
  FROM names
  WHERE license_number LIKE '%' || p_license_number || '%'
$$
LANGUAGE sql;

By 'fuzzy search', I mean it searches for any row with a licence number containing as a substring the search parameter p_license_number's value.

How can I convert this fuzzy search query to Opaleye?

This would involve getting rid of the Sql file and replacing the getNamesByLicenseNumber with a Haskell Opaleye query. I expect the answer could be something like

getNamesByLicenseNumber :: String -> Query NamesColumnRead
getNamesByLicenseNumber licNumber = proc () -> do
  names <- namesQuery -< ()
  restrict -< pgString licNumber .== licenseNumber (names :: NamesColumnRead)
  returnA -< names

except with fuzzy instead of strict matching.

Answer 1

I'm not sure what exactly your question is because what you have looks pretty much fine. There are two things I would improve. Firstly, pass the string as an argument to a QueryArr rather than as a function argument. Secondly use like rather than .==.

getNamesByLicenseNumber :: QueryArr (Column PGText) NamesColumnRead
getNamesByLicenseNumber = proc licNumber -> do
  names <- namesQuery -< ()
  restrict -< licNumber `like` licenseNumber (names :: NamesColumnRead)
  returnA -< names

Does this do what you want?