how to add casts to Values or similar parameter lists

[ibotty]

hi,

apart from wrapping every element in a wrapper type that decorates it with a cast, how can i do the following

query conn "INSERT INTO t (col) VALUES (? :: my_type);" (Only myVal)

to

 query conn "INSERT INTO t (col) VALUES ?;" (Only (Values [myVal]))

or is that what i am supposed to do. if so, should that be in postgresql-simple or mentioned in the documentation (i might send a pull request).

[ibotty]

what do you think about the following untested code using type level strings. that way no dynamic typing gets in.

newtype TypedData (t :: Symbol) a = TypedData a

instance (SingI t, ToField a) => ToField (TypedData t a) where
    toField f = withSing (toField' f)
      where
        toField' :: ToField a => TypedData t a -> Sing t -> Action
        toField' (TypedData a) t =
            Many [ P.toField a
                 , Plain (fromString " :: ")
                 , toField . Identifier . T.pack $ fromSing t
                 ]             

edit: of course a simple

data TypedData' a = TypedData' Text a

is also possible.

[lpsmith]

This seems like a plausible candidate for inclusion, although I don't quite see the use case yet. I'm still not clear on what real advantage there is to using type-level strings to encode the postgresql types though... after all, postgresql-simple (sadly) doesn't have any real support for static checking (like templatepg) or at least optional checking at program start up or perhaps programmatically called (after all schemas can and do change, and programs don't always get recompiled when they do.)

Although this does have some significant advantages too... templatepg doesn't really support parametrized identifiers, which is one of several features of postgresql-simple that would be very difficult to implement in something like templatepg. Dynamic SQL in general difficult to statically type, but unfortunately dynamic sql is sometimes necessary as pg's protocol-level parameters don't cover every use case.

[reiddraper]

I'm running into this issue as well. I'm trying to insert data while also doing a join, using a Common Table Expression. Here's a simplified example:

WITH received_data(a, b, c)  AS
    (VALUES ('WZIbrTHCwDq7C+WP77K4cg==', 'eneration_value'::myEnum, '2015-01-08 11:54:15.311204-06'::timestamptz)
    INSERT INTO existing(id, eb, ec)
    (SELECT join_table.id, b, c FROM received_data INNER JOIN join_table ON (join_table.token = received_data.a));

Column b is some enumeration type, and column c is a timestamptz.