How can I use pointers like `*string` or `*int` for nullable columns in generated models?

[ikozinov]

Curently we have support custom types, but it would be nice to have support for pointers to standard types (string, int, time.Time etc...)

This is how it is done in pgx driver

pgx can map nulls in two ways. The first is Null* types that have a data field and a valid field. They work in a similar fashion to database/sql. The second is to use a pointer to a pointer.

https://pkg.go.dev/github.com/jackc/pgx@v2.11.0+incompatible#hdr-Null_Mapping

[jschaf]

Yea, I've been thinking about this for a while because I really want it; it's a pain to use pgtype.Text opposed to *string. The key thing I'm trying to limit is the combinatorial explosion resulting from adding additional codegen options.The code generator is already quite complicated due to how different pieces interact (:many, :one, :exec, and using Row structs when more than 1 output param)

I think there's 3 basic options:

1. Use native, non-pointer type like int - doesn't support null.
2. Use native pointer types like *int - supports null with nil value.
3. Use pgtype custom type like pgtype.Int4 - supports null with the Status field.

Right now, pggen use option 3 by default and promotes some columns to option 1 if it can prove the column is not null.

I think I can extend --go-flag to support option 2 with --go-type int4=*int or --go-type text=*string. That doesn't add too much complexity and has the nice benefit of not adding another flag.

A second thing to explore: instruct pggen to assume all columns are not null so that pggen uses int instead of *int. It'll be a long while before pggen can infer nullability with any depth, so it might be a helpful shortcut. I think that's best set as a per query option, like:

-- name: FindThings :many :assumeNotNull
SELECT 1;

[jschaf]

Looks like --go-type int4=*int works "out-of-the-box". I added an example in 1357311 (side note, that's a really cool short hash, all odd digits and no hex digits). I need to fix a double pointer bug but that should be pretty painless.

[jschaf]

I added a bunch of examples and it all seems to work:

pggen gen go \
    --schema-glob "example/go_pointer_types/schema.sql" \
    --query-glob "example/go_pointer_types/query.sql" \
    --go-type "int8=*int" \
    --go-type "int4=*int" \
    --go-type "text=*string" \
    --go-type "_int8=[]int" \
    --go-type "_int4=[]int"

Give it a go and let me know if it works.