How to deal with custom types?

[sir4ur0n]

Hi, I have several PostgreSQL custom types, e.g.:

CREATE TYPE foo AS ENUM ('foo', 'bar', 'baz');

How is one supposed to handle these with hasql-th please? Currently if I just type e.g. SELECT x :: foo, I get this error:

No codec exists for type: foo

It might also be nice to document it for future users :sweat_smile: :pray:

Ideally, one could register custom codecs, so that the SQL code can be used interchangeably in Haskell or directly in an SQL editor/PSQL. E.g.:

instance HasqlThCodec Foo where
  sqlTypeName = "foo"
  encode Foo{..} = -- ...
  decode .. = Foo {..}

getFoo = [maybeStatement|SELECT x :: foo from foos|]

[TheOddler]

I'm getting the same error now, did you figure out how this works?

[TheOddler]

So I found something that works for my use case:

        INSERT INTO users (name, status)
        SELECT name, status :: my_status_enum
        FROM UNNEST ($1 :: text[], $2 :: text[])
          AS t(name, status)

So in Haskell you'll need to map your enum to text as well. Then in SQL it'll accept the input as being a text, but then you cast again in SQL to your enum value.

[TheOddler]

Oh, another alternative that is more succinct, in SQL just cast twice: $2 :: text[] :: my_status_enum[] works as well!

INSERT INTO users (name, status)
SELECT *
FROM UNNEST ($1 :: text[], $2 :: text[] :: my_status_enum[])

[sir4ur0n]

No. Because of this problem and a few others, my team has moved away from Hasql 🤷

[domenkozar]

Oh, another alternative that is more succinct, in SQL just cast twice: $2 :: text[] :: my_status_enum[] works as well!

INSERT INTO users (name, status)
SELECT *
FROM UNNEST ($1 :: text[], $2 :: text[] :: my_status_enum[])

Using Data.Vector.unzip3 with dimap if you have a list of tuples works well.