Proposal: Custom go type mapping per query argument

[0xjac]

Based on the side note in #43, detailed in a separate issue here as requested per @jschaf

Currently, pggen allows to map an SQL type to a Go type using the --go-type <sql-type>=<go-type> argument (repeated for each SQL type which we want to map). This implies that an SQL type is only ever mapped to one specific Go type for all arguments of all queries.

However, it might be useful to map a SQL type to different Go types based on context. This can apply either to parameters across queries or within queries, as well as the return value. A strong argument in favor of different mappings is the fact that PostgreSQL does not distinguish between a type which does not contain NULLs and a NULL-able type.

As a simple example, consider a schema to track flights, we are interested in the flight number, departure time, ETA, and arrival time. The SQL table for the data could look like:

CREATE TABLE flights (
    flight_number INTEGER PRIMARY KEY,
    departure TIMESTAMP WITH TIME ZONE NOT NULL,
    eta TIMESTAMP WITH TIME ZONE,
    arrival TIMESTAMP WITH TIME ZONE
)

We see the issue here, the departure/arrival times and ETA are all of type timestamptz. However, while the departure time is never NULL, the ETA and arrival time may be NULL. (This makes sense since the departure time is scheduled and known in advance, while the arrival time will only be known upon landing and the ETA is an estimation which may not be present, for example if the flight is delayed or canceled.)

Let's consider some queries for flights:

-- name: InsertFlight :exec
INSERT INTO flights (flight_number, departure, eta)
    VALUES (pggen.arg('flightNumber'), pggen.arg('departure'), pggen.arg('eta'));

-- GetTodaysFlights :many
SELECT f.flight_number, f.departure, f.arrival, f.eta
FROM flights f
WHERE CURRENT_DATE <= f.departure AND f.departure < CURRENT_DATE + 1
ORDER BY f.departure DESC;

-- GetTodaysDepartures :many
SELECT f.departure
FROM flights f
WHERE CURRENT_DATE <= f.departure AND f.departure < CURRENT_DATE + 1
ORDER BY f.departure DESC;

For those queries, all timezone arguments can be mapped to *time.Time but not to time.Time as this would fail for GetTodaysFlights for flights without an arrival time and/or an ETA. Having *time.Time implies that the departure parameter in InsertFlight will be a pointer which is not needed, and will fail at runtime if a nil pointer is given.

It would be much better to individually map parameters and return values to the desired go types. This mapping would take precedence over the global mapping (via --go-type) and of course if not specifed, the then global mapping would be used.

This individual mapping could be specified in the query comment, reusing the existing syntax used to specify the function name and return cardinality. Assuming the following global mapping: --go-type "timestamptz=*time.Time" --go-type "integer=int", the queries can be defined as:

-- name: InsertFlight :exec
-- arg: flightNumber int
-- arg: departure time.Time
INSERT INTO flights (flight_number, departure, eta)
    VALUES (pggen.arg('flightNumber'), pggen.arg('departure'), pggen.arg('eta'));

-- GetTodaysFlights :many
-- return: departure time.Time
SELECT f.flight_number, f.departure, f.arrival, f.eta
FROM flights f
WHERE CURRENT_DATE <= f.departure AND f.departure < CURRENT_DATE + 1
ORDER BY f.departure DESC;

-- GetTodaysDepartures :many
-- return: departure time.Time
SELECT f.departure
FROM flights f
WHERE CURRENT_DATE <= f.departure AND f.departure < CURRENT_DATE + 1
ORDER BY f.departure DESC;

The format here is:

• argument: --arg: <arg-name> <go-type>
• return column --return: <column-name> <go-type>

Note that:

1. The value of <go-type> should be identical to that in the global mapping: --go-type <sql-type>=<go-type>.
2. Even if the return is single column, the column name is still specified, but the return value does not need to be a struct with a single column, it can jut be a slice (with :many) or an instance (with :one) of the column type. The custom type specified is always for the column (of a single row), not for many rows, in this case it should be a slice of the type.

So the Go functions should look like:

func InsertFlight(flightNumber int, departure time.Time, eta *time.Time) error

type GetTodaysFlightsRow struct {
   FlightNumber int    `json:"flight_number"`
   Departure time.Time `json:"departure"`
   ETA *time.Time      `json:"eta"`
   Arrival *time.Time  `json:"arrival"`
}

func GetTodaysFlights() ([]GetTodaysDeparturesRow, error)

func GetTodaysDepartures() ([]time.Time, error)

Hopefully it should be straight forward enough to implement and avoid any corner cases I can think of.