pgtype: custom types support

[aight8]

Currently custom declared types declared in pgtype are not supported.

// this is typically located in pgx connection config AfterConnect function:

ci := conn.ConnInfo()

ci.RegisterDataType(pgtype.DataType{
    Value: new(shopspring.Numeric),
    Name: "numeric",
    OID: pgtype.NumericOID,
})

// register custom enums/composit types with combination of:
// pgxtype.LoadDataType
// ci.RegisterDataType
// ci.RegisterDefaultPgType

I know pggen is in heavy development, but I create this issue to make sure what I mean.

[jschaf]

Definitely! Custom type support is something I want. I have a few ideas for how to support custom types.

1. --postgres-type to map a Postgres type name for all queries to a fully qualified Go type, like: --postgres-type 'author_id=github.com/jschaf/pggen/authors.AuthorID'.

2. --postgres-column to map a Postgres table column to a fully qualified Go type, like: --postgres-column 'author.author_id=github.com/jschaf/pggen/authors.AuthorID'. This will only work for simple queries where the table column appears directly as an output column as in SELECT author_id FROM author. pggen can't infer the column name for queries like: SELECT coalesce(author_id, 1) FROM author.

Another wrinkle is how to support nullable types. Maybe add --postgres-nullable-type and --postgres-nullable-column. Otherwise, if we map a type to int or string, there's no way to know if the value was actually null.

[jschaf]

Since pggen is calling Scan and providing the decoders it will ignore user registered types on a pgx.Conn. That means we'll have to declare the types at pggen build time using flags like above.

[jschaf]

This is live on the main branch:

pggen gen go \
    --schema-glob example/custom_types/schema.sql \
    --query-glob example/custom_types/query.sql \
    --go-type 'text=github.com/jschaf/pggen/example/custom_types/mytype.String'

This will break if the custom type is in the same package as query.sql but should mostly work other than that. Open an issue if you find anything broken.

[jschaf]

This will break if the custom type is in the same package as query.sql

This bug is fixed with an example in the custom_types example.

[jschaf]

Maybe related: https://github.com/jackc/pgx/issues/953