如何用Haskell的postgresql-simple处理变长元组构建复杂SQL查询

这并非postgresql-simple的设计局限，而是需要利用其ToRow类型类和函数式技巧来动态构建查询参数与SQL片段。以下是几种实用方案：

方案1：用Row组合子动态拼接参数 #

postgresql-simple提供了Row类型（含Nil和(:.)构造器），天生支持ToRow实例，可以用来动态组合不同类型的参数：

首先导入相关模块：

import Database.PostgreSQL.Simple.Row (Row(..))
import Data.List (intercalate)
import Data.Maybe (catMaybes)

定义过滤器类型，绑定SQL片段和对应参数：

data Filter = Filter Query Row

编写查询构建函数，自动拼接条件和参数：

buildFilteredQuery :: Query -> [Filter] -> (Query, Row)
buildFilteredQuery baseQuery filters =
    let active = [ (cond, param) | Filter cond param <- filters ]
        (conds, params) = unzip active
        whereClause = case conds of
            [] -> ""
            _ -> " WHERE " ++ intercalate " AND " (map show conds)
        combinedParams = foldr (\p acc -> p <> acc) Nil params
    in (fromString $ show baseQuery ++ whereClause, combinedParams)

改造后的查询执行函数，无需分支判断：

runQuery' :: Connection -> Maybe Int -> Maybe String -> Maybe Int -> IO ()
runQuery' conn mId mName mAge = do
    let base = "SELECT * FROM (VALUES (1, 'Chris', 25)) AS t (id, name, age)"
        filters = catMaybes
            [ (\v -> Filter "id = ?" (Only v :. Nil)) <$> mId
            , (\v -> Filter "name = ?" (Only v :. Nil)) <$> mName
            , (\v -> Filter "age = ?" (Only v :. Nil)) <$> mAge
            ]
        (finalQuery, params) = buildFilteredQuery (fromString base) filters
    if null filters
        then putStrLn "No filters applied."
        else query conn finalQuery params >>= printResult

方案2：使用Formatter安全构建参数化SQL #

postgresql-simple的Formatter类型可以更安全地构建带参数的SQL，自动处理占位符和参数转义，避免SQL注入风险：

导入格式化模块：

import Database.PostgreSQL.Simple.Format (Formatter, format, (%))
import Data.Maybe (catMaybes)

编写格式化函数动态生成查询：

buildFormatter :: Maybe Int -> Maybe String -> Maybe Int -> Formatter
buildFormatter mId mName mAge =
    format "SELECT * FROM (VALUES (1, 'Chris', 25)) AS t (id, name, age)"
    <> case catMaybes filters of
        [] -> mempty
        conds -> format " WHERE " <> foldr (\c acc -> acc <> format " AND " <> c) (head conds) (tail conds)
    where
        filters = 
            [ (\v -> format "id = %" v) <$> mId
            , (\v -> format "name = %" v) <$> mName
            , (\v -> format "age = %" v) <$> mAge
            ]

执行查询时直接解析Formatter得到SQL和参数：

runQuery'' :: Connection -> Maybe Int -> Maybe String -> Maybe Int -> IO ()
runQuery'' conn mId mName mAge = do
    let (finalQuery, params) = format $ buildFormatter mId mName mAge
    if null params
        then putStrLn "No filters applied."
        else query conn finalQuery params >>= printResult

方案3：自定义ToRow实例（固定参数集合场景） #

如果你的过滤器参数集合固定，可以自定义数据类型并实现ToRow实例，让代码更简洁：

data UserFilters = UserFilters
    { filterId :: Maybe Int
    , filterName :: Maybe String
    , filterAge :: Maybe Int
    } deriving (Show)

instance ToRow UserFilters where
    toRow (UserFilters mId mName mAge) =
        concat $ catMaybes
            [ toRow . Only <$> mId
            , toRow . Only <$> mName
            , toRow . Only <$> mAge
            ]

buildSQL :: UserFilters -> Query
buildSQL (UserFilters mId mName mAge) =
    let base = "SELECT * FROM (VALUES (1, 'Chris', 25)) AS t (id, name, age)"
        conds = catMaybes
            [ "id = ?" <$ mId
            , "name = ?" <$ mName
            , "age = ?" <$ mAge
            ]
    in fromString $ case conds of
        [] -> base
        _ -> base ++ " WHERE " ++ intercalate " AND " conds

使用时直接传入自定义类型：

runQuery''' :: Connection -> UserFilters -> IO ()
runQuery''' conn filters = do
    let sql = buildSQL filters
    if null (toRow filters)
        then putStrLn "No filters applied."
        else query conn sql filters >>= printResult

postgresql-simple的元组参数设计是为了保证类型安全，确保参数数量与SQL占位符严格对应。通过Row组合子、Formatter或自定义ToRow实例，完全可以实现动态查询构建，彻底摆脱繁琐的分支判断。

内容的提问来源于stack exchange，提问作者Chris Stryczynski