Variable (decided at runtime) number of ? substitutions?

[michalrus]

If I have a query that once takes (a,b) and the other time only (Only a), how to do that?

For the time being, this seems sensible:

newtype ManualToRow = ManualToRow [DB.Action]

instance ToRow ManualToRow where
  toRow = id

But, I think, I’d much rather like to have a possibility to return an empty Action somehow…

Is there anything available already?

[cimmanon]

There are a few ways this can be done.

The first way is to write the query with the expectation that every time you run this query, you'll always pass in every value. This involves making use of coalesce.

INSERT INTO blog_tags (entry_id, tag) VALUES (coalesce(?, currval(blog_entries_entry_id_seq)), ?)

The next option is to use CTEs and CASE statements in the WHERE clause. There are some operators that just don't play nicely with coalesce, such as @@ when performing a fulltext search. Note that this can sometimes create a slow query plan. Again, you will be passing in all values.

WITH
    options (keyword, price) AS (VALUES (?, ?))
SELECT
    *
FROM
    foo
    , options
WHERE
    CASE WHEN options.price IS NOT NULL THEN foo.price && options.price END
    AND CASE WHEN options.keyword IS NOT NULL THEN to_tsvector(foo.name) @@ plainto_tsquery(options.price) END

The last method involves creating a ToField instance for a custom type. This is what I prefer to use when writing search queries where the search fields are optional.

import Data.List (intercalate)
import Database.PostgreSQL.Simple.ToField (ToField(..), Action(..))

data FooSearch = FooSearch
    { keyword :: Maybe Text
    , price :: PGRange Int
    }

instance ToField FooSearch where
    toField s = buildWhereClause
        [ if price s == (PGRange NegInfinity PosInfinity) then Nothing else Just [ Plain "price :: INT <@ ", toField $ price s, Plain " :: INT4RANGE" ]
        , (\x -> [ Plain "to_tsvector(name) @@ plainto_tsquery(", toField x, Plain ")" ]) <$> keyword s
        ]

buildWhereClause :: [Maybe [Action]] -> Action
buildWhereClause = combineActionsWith (Plain " AND ")

combineActionsWith :: Action -> [Maybe [Action]] -> Action
combineActionsWith joiner xs = case intercalate [joiner] (catMaybes xs) of
    [] -> Plain "1 = 1"
    xs' -> Many xs'

If you run it like this:

query "SELECT * FROM foo WHERE ?" (Only $ FooSearch Nothing $ PGRange NegInfinity PosInfinity)

You get a query that looks like this:

SELECT * FROM foo WHERE 1 = 1

[michalrus]

Thank you very much for the inspiration! 💞