Adding Encoding/Decoding for custom PG Types

[sumo]

I'm trying to define a clean interface into a set of PG tables and using functions/stored procs using custom PG types. I've defined the following:

CREATE TYPE ledger.posting_entry AS (
	account_id int8,
	asset_id int8,
	amount money,
	details jsonb
);

create type posted_journal as (
	journal_id int8,
	posting_ids int8[]
);

CREATE or replace function ledger.post_journal(journal varchar, entries ledger.posting_entry[]) returns posted_journal
.
.
.

How can I define an encoding and decoding for the custom types? I've looked through the source for the existing mappings but am not clear how to actually map to a

ROW(a,as,am,d) :: ledger.posting_entry from haskell.

I can change the interface into the function as a last resort.

[nikita-volkov]

There's no encoder for composite types, it's Postgres to blame. There are work-arounds however. For more info and the reasoning see the following issues:

• Unable to encode array of composites #65
• Multi-Row Insert #25 (comment)

[sumo]

Thanks, I've managed to get the SQL to work but the corresponding Hasql fails. My code is below, I've more or less followed the method used in #65.

data PostingEntry a = PostingEntry {
  accountId :: Int64
  , assetId :: Int64
  , amount :: Decimal
  , details :: a
} deriving (Show, Eq)

newtype Journal = Journal { name :: Text }

data JournalEntry a = JournalEntry { 
  journalName :: Journal,
  postings :: [PostingEntry a]
}

newPostingSQL :: ByteString
newPostingSQL = toS $ [i| select ledger.post_journal($1, (array (
    select b :: ledger.posting_entry from unnest (cast($2 as int8[]), cast($3 as int8[]), cast($4 as int8[]), cast($5 as jsonb[])) as b
  )))
|]

postJournal :: ToJSON a => JournalEntry a -> Transaction (Int64, [Int64])
postJournal je = statement je postJournalStmt

postJournalStmt :: ToJSON a => Statement (JournalEntry a) (Int64, [Int64])
postJournalStmt = Statement newPostingSQL journalEntryEncoder journalPostingDecoder  True

journalEntryEncoder :: ToJSON a => HE.Params (JournalEntry a)
journalEntryEncoder = contramap (name . journalName) (HE.param HE.text)
  <> contramap (fmap accountId . postings) (HE.param (mkArray HE.int8))
  <> contramap (fmap assetId . postings) (HE.param (mkArray HE.int8))
  <> contramap (fmap (show . amount) . postings) (HE.param (mkArray unknown))
  <> contramap (fmap (toJSON . details) . postings) (HE.param (mkArray HE.jsonb))

mkArray :: Foldable t => HE.Value b -> HE.Value (t b)
mkArray a = HE.array (HE.dimension foldl' (HE.element a))

journalPostingDecoder :: HD.Result (Int64, [Int64])
journalPostingDecoder = singleRow ((,) <$> HD.column HD.int8 <*> HD.column (decodeArray HD.int8))

decodeArray :: HD.Value a -> HD.Value [a]
decodeArray d = HD.array (HD.dimension replicateM (HD.element d))

Unfortunately this fails when I attempt to execute the actual query. Looks like something about the serialisation for cast and unnest is not correct.

2018-12-13 14:21:23.881 AEDT [65132] ERROR:  22P02: malformed array literal: ""
2018-12-13 14:21:23.881 AEDT [65132] DETAIL:  Array value must start with "{" or dimension information.

The corresponding SQL works correctly because of the array operator

select ledger.post_journal('Lunch', ((select array (
		select b :: ledger.posting_entry from unnest (array[2,4], array [1,1], array [-10.50, 10.50], array['{ "description" :"lunch"}', '{ "description" :"lunch"}']) as b
	))))

Is this do-able with Hasql 1.x? I noticed a different API in #25 should I be using the dev branch instead?

[nikita-volkov]

Is this do-able with Hasql 1.x?

Yes.

I noticed a different API in #25 should I be using the dev branch instead?

Query was renamed to Statement in the recent releases of version 1.*. So what saw in the referred issues was the older API, not dev. The solutions there should absolutely be applicable.

---

Why do you cast your every parameter cast($2 as int8[])? The code should work without casting at all, if not then with explicit type specification at least ($2 :: int8[]).

There's too many things happening in your code. To determine the issue I suggest to gradually move from a simpler case step by step. Start by first ensuring that you've implemented the innermost select correctly.

[sumo]

Thanks, I simplified as suggested and it turns out that using unknown in place of a money type means that the server was unable to find the right function or cast the array correctly. Changed it to a numeric type and function resolution occurs correctly.