Custom Types does not work with DOMAIN

[Louis-Tian]

Custom type conversion does not seem to work with Domain.

I want to create a custom id field based on int4, but that id field is to be represented as base32 encoded string in javascript land.

create domain appId as int4 not null;
create table app (
	id appId
);

select oid from pg_catelog.pg_type where typname = 'appId'; // returns 17842

const sql = postgres({
  user: 'postgres',
  pass: 'password',
  types: {
   appId: {
      to: 17842,
      from: [17842],
      serialize: (value: string) => parseInt(value, 32),
      parse: (value: string) => { 
         console.log("parsing") // never invoked
         return parseInt(value).toString(32)
      }
    }
  },
});
console.log(await sql`select id from app`) // -> Result(1) [ { id: 1262438631 } ]

the id will still return as an number because the parse function is never invokes.

Now if we change the 17842 (oid of appId) to 23 (oid of int4), then the parse function does get invoked as expected.

[Louis-Tian]

Found this old thread https://www.postgresql.org/message-id/flat/D71A1574-A772-11D7-913D-0030656EE7B2%40icx.net

Looks like a (IMO bad) decision was made 20 years ago.
The RowDescription always uses a baseType oid for domain types.

[Louis-Tian]

As an alternative, I wonder whether if it's possible to expose to the table oid and attribute name returned by the RowDescription in the parser function?

[porsager]

@Louis-Tian Perhaps we could include these oid "aliases" in the array type fetch?

postgres/src/connection.js

Lines 744 to 755 in 61c4d5b

	async function fetchArrayTypes() {
	needsTypes = false
	const types = await new Query([`
	select b.oid, b.typarray
	from pg_catalog.pg_type a
	left join pg_catalog.pg_type b on b.oid = a.typelem
	where a.typcategory = 'A'
	group by b.oid, b.typarray
	order by b.oid
	`], [], execute)
	types.forEach(({ oid, typarray }) => addArrayType(oid, typarray))
	}

I've been wanting to allow setting custom types by name as well, so maybe there's two birds to aim for here :)

[porsager]

Ah, wrote to quickly - that of course won't help as we don't get the real oid..

About including the column info in the parser call, that's a great idea! We're already doing that for the transform helpers - I'm gonna add that 👍

[Louis-Tian]

I think supporting the parser with column information is as simple as adding the column to the function calls at

postgres/src/connection.js

Lines 498 to 499 in 61c4d5b

	? column.parser(x.toString('utf8', index + 1, index += length))
	: column.parser(x.toString('utf8', index, index += length))

But, It's not obvious how to support the same in the serializer, since the serialisation is done on the binding parameters which, if I am not mistaken, is completely context free from table and column it is associated with in the query statement.

There is a fundamental problem. Even if the PostgreSQL does returns the Domain's own oid in the RowDescription, we will still have same problem for serializing it on the way back.

[MaoHolden]

Hey there, took me a while to figure this out, is it ok if I add a small text on custom types mentioning the problem, this issue and the maillist link https://www.postgresql.org/message-id/flat/D71A1574-A772-11D7-913D-0030656EE7B2%40icx.net ? Would for sure be useful for some people

[MaoHolden]

What's up, what about referencing types either by oid or by typname, if its a string then its a typname. Would you be open to a PR for this? I can't find any way to differentiate between my domains currently.