CODEWITHSUNDEEP
postgresqlhaskell
Boris
Boris

Reputation: 624

Postgresql-simple and values of PostgreSQL-specific types

I would like to work with some the database-specific datatypes like money, point, inet, etc. It is possible to insert them as strings. However, I could not find an example of how to fetch them since they are not in the list passed to FromField.mkCompats function.

When I try to fetch such a value, there is an exception

*** Exception: Incompatible {errSQLType = "money", errHaskellType = "Text", errMessage = "types incompatible"}

Is there a way to do this not resorting to the low-level postgresql-libpq?

Upvotes: 1

Views: 848

Answers (2)

lpsmith
lpsmith

Reputation: 707

Yes, postgresql-simple can do this! However, previously this was not well documented. Check out the example code in the FromField module.

What you can do is define a type for representing Money, (e.g. a newtype for Text) and then defining a FromField instance that provides the appropriate conversion. A canonical conversion would first check the type, then check if the data is not null, and finally attempt to perform the conversion. Since money is a built-in type provided by postgresql proper, it has a stable type OID and for typechecking purposes all you need to do is to use the typeOID function.

In cases where the type is provided by an extension to postgresql, the type OID can vary from database to database. In this case, I recommend using the typename function to get the string which does not vary.

Unfortunately, it is not (yet?) possible to add conversions to existing FromField instances from additional postgresql types.

(Sorry for the late reply, I don't normally visit Stack Overflow.)

Upvotes: 2

David S
David S

Reputation: 13851

I don't know anything about haskell, but, you should be able to just typecast the values in the query. Such as:

 select your_money_field::text from your_table;

Again, I don't know how you are querying... if you are using some kind of ORM then you might not be able to do this.

Upvotes: 0

Related Questions