calling functions with positional and named function arguments

[ibotty]

hi,

this goes a way into query generation, so if this is out of scope for postgresql-simple, just tell me.

i have to call sql functions with a variable number of arguments, so i needed something along the lines of the following.

This usage

query conn "SELECT my_function(?);" (Only $ FunctionArguments [FunctionArgument True] [NamedFunctionArgument "key" 4])

will generate the following query.

SELECT my_function(True, "key" := 4);

the implementation is not pretty (ExistentialQuantification) but works:

data NamedFunctionArgument = forall a. ToField a => NamedFunctionArgument Identifier a
  deriving (Typeable)

instance ToField NamedFunctionArgument where
    toField (NamedFunctionArgument ident a) =
        Many [toField ident, Plain (fromByteString " := "), toField a]

data FunctionArgument = forall a. ToField a => FunctionArgument a
  deriving (Typeable)

instance ToField FunctionArgument where
    toField (FunctionArgument a) = toField a

data  FunctionArguments = FunctionArguments [FunctionArgument] [NamedFunctionArgument]
  deriving (Typeable)

instance ToField FunctionArguments where
    toField (FunctionArguments pos named) =
        Many . intersperse (Plain (fromByteString ", ")) $
            map toField pos ++ map toField named

[ibotty]

i forgot to say, that one could of course use Action instead of the existential quantified types, but that leads to ugly usage imo.

[lpsmith]

It looks reasonable (if a little verbose), and potentially useful, but is it of general interest enough to be included in postgresql-simple?

I mean, these definitions work perfectly well outside of postgresql-simple. Which doesn't completely rule it out in my book, but then it becomes a judgement call of whether or not the functionality is of sufficiently general interest.

[ibotty]

i can live with it living in my project and it is verbose.

i wrap the query call to something like the following.

callFunction
  :: FromRow a
  => Connection
  -> QualifiedIdentifier -- ^ function to call
  -> [FunctionArgument] -- ^ positional arguments
  -> [NamedFunctionArgument] -- ^ named arguments
  -> IO [a]

i don't have a need to write function arguments or named function arguments by hand, so i did not bother, but there is of course a reasonable fromString instance for FunctionArgument and an easy operator .= :: ToField a => Identifier -> a -> NamedFunctionArgument.

i did not propose that originally, because i thought it's not in the spirit of postgresql-simple.

[ibotty]

i now had the pleasure to call postgresql functions and write arguments by hand, so i modified the code to the following.

the following usage

query conn "SELECT function(?)" (FunctionArguments (arg, arg') ["optArg" := arg''])

is made possible with the following

data FunctionArguments p = FunctionArguments p [NamedFunctionArgument]

instance ToRow p => ToRow (FunctionArguments p) where
    toRow (FunctionArguments p n) = toRow $ p :.  n

instance ToRow p => ToField (FunctionArguments p) where
    toField = P.Many . P.toRow

(:=) :: ToField a => Identifier -> a -> NamedFunctionArgument
label := a = NamedFunctionArgument label a

with NamedFunctionArgument from above. the existential in namedFunctionArgument can be avoided when using toField in(:=)explicitly (but i would not like exposingActionin the public interface), or when also using aToRow` instance instead of an array. the latter case allows abusing the api with not-named arguments in later positions though. of course one might introduce another typeclass a la ToRow for named function arguments to make it more robust and more flexible...

i used the above api in three applications within the last weeks so i'd like to see it either included within postgresql-simple or i'd have to upload a nearly empty package, because i get bored copying that :).

[lpsmith]

Well, this is an interesting idea, but I still don't think it's of a sufficiently general interest. I'm happy to keep this issue open for now in case anybody else would like to chime in and convince me that I'm wrong, however. =)

I have postgresql-simple related code that gets copied around a fair bit myself. In some cases I do think that the topic of the code would be of enough interest to include, but that the particular code isn't quite general enough yet. For example, I have ToField/FromField instances for tstzrange, but these instances are basically assuming that all periods are either empty or of the form [not null, not null), which was a good assumption back when I was using temporal-postgres extension, but not the new built-in range types. (Not to mention that hey, range types now include more than just ranges of timestamps.)