You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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:
CREATETABLEflights (
flight_number INTEGERPRIMARY KEY,
departure TIMESTAMP WITH TIME ZONENOT 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 :execINSERT INTO flights (flight_number, departure, eta)
VALUES (pggen.arg('flightNumber'), pggen.arg('departure'), pggen.arg('eta'));
-- GetTodaysFlights :manySELECTf.flight_number, f.departure, f.arrival, f.etaFROM flights f
WHERECURRENT_DATE<=f.departureANDf.departure<CURRENT_DATE+1ORDER BYf.departureDESC;
-- GetTodaysDepartures :manySELECTf.departureFROM flights f
WHERECURRENT_DATE<=f.departureANDf.departure<CURRENT_DATE+1ORDER BYf.departureDESC;
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:
The value of <go-type> should be identical to that in the global mapping: --go-type <sql-type>=<go-type>.
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.
Extends the query annotation to specify custom go types using the same
qualified go type format as for the general custom type mapping.
Closesjschaf#46
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
NULL
s and aNULL
-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:
We see the issue here, the departure/arrival times and ETA are all of type
timestamptz
. However, while the departure time is neverNULL
, the ETA and arrival time may beNULL
. (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:
For those queries, all timezone arguments can be mapped to
*time.Time
but not totime.Time
as this would fail forGetTodaysFlights
for flights without an arrival time and/or an ETA. Having*time.Time
implies that thedeparture
parameter inInsertFlight
will be a pointer which is not needed, and will fail at runtime if anil
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:The format here is:
--arg: <arg-name> <go-type>
--return: <column-name> <go-type>
Note that:
<go-type>
should be identical to that in the global mapping:--go-type <sql-type>=<go-type>
.: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:
Hopefully it should be straight forward enough to implement and avoid any corner cases I can think of.
The text was updated successfully, but these errors were encountered: