Data types

New SQL data types backed by Go types, composite types, polymorphic functions and SQL-defined types.

Data types#

A Go type marked with //gopgext:type is a SQL data type. Its underlying type is its representation: a fixed-size scalar (bool, int16, int32, int64/int, float32, float64, pg.Oid: passed by value), [N]byte (a fixed-size type passed by reference, INTERNALLENGTH = N, like point or uuid: Go gets a copy of its bytes; no arrays, sets or columns of it) or a variable-size one ([]byte, string, or pg.Varlena to avoid copying arguments). The type’s I/O functions are //gopgext:functions named by the options, and gopgext creates the type (a shell type, its functions, then CREATE TYPE) before the other functions, which can then use the Go type in their signatures:

// Vector is a vector of float4.
//
//gopgext:type input=VectorIn output=VectorOut typmod_in=VectorTypmodIn receive=VectorRecv send=VectorSend storage=external
type Vector pg.Varlena

//gopgext:function immutable parallel=safe
func VectorIn(s pg.CString, typ pg.Oid, typmod int32) (Vector, error) { ... }   // '[1,2,3]', vector(3)

//gopgext:function immutable parallel=safe
func VectorOut(v Vector) pg.CString { ... }

//gopgext:function immutable parallel=safe
func VectorTypmodIn(mods []pg.CString) (int32, error) { ... }                   // vector(3)

//gopgext:function immutable parallel=safe
func VectorRecv(buf pg.StringInfo, typ pg.Oid, typmod int32) (Vector, error) { ... }  // binary COPY, protocol

//gopgext:function immutable parallel=safe
func VectorSend(v Vector) []byte { ... }

//gopgext:function immutable parallel=safe
func L2Distance(a, b Vector) (float64, error) { ... }                          // l2_distance(vector, vector)
OptionMeaning
name=sql_nameSQL name (default: snake_case of the Go name)
input=, output= (required), receive=, send=, typmod_in=, typmod_out=, analyze=the type’s functions (Go names)
storage=plain|external|extended|mainSTORAGE (variable-size types)
alignment=char|int2|int4|doubleALIGNMENT (default: from the representation)
category=X, preferredCATEGORY, PREFERRED

Arrays of these types work like arrays of scalars ([]Vector is vector[]). Operators, operator classes, casts and aggregates are SQL in sql/*.sql (appended after the generated objects, so they can use them): examples/vector is a complete example (the core of pgvector).

Composite types#

//gopgext:type on a struct creates a composite type (CREATE TYPE ... AS) with a column per field (names in snake_case or from pg:"name" tags), usable as arguments and results, and for tables (CREATE TABLE t OF reading). Columns can be any of the types above but polymorphic ones and other composites; a NULL column needs a pointer field.

// Reading is a sensor's temperatures.
//
//gopgext:type
type Reading struct {
	Sensor string
	Temps  []Celsius // an array of a //gopgext:type
	Note   *string
}

//gopgext:function immutable
func Calibrate(r Reading, offset float64) Reading { ... } // calibrate(reading, float8) RETURNS reading

Polymorphic functions#

pg.AnyElement and pg.AnyArray are anyelement and anyarray: a value of the type the function is called with (Type()), which Go can format (String(), the type’s output function), create from text (pg.ParseAny(typ, s)), return as is, or collect (AnyArray.Elements(), flattened; Dims(), pg.NewAnyArray(typ, values)). Results must have the type PostgreSQL resolves from the arguments.

//gopgext:function immutable
func FirstNonNull(xs pg.AnyArray) *pg.AnyElement { ... } // first_non_null(anyarray) RETURNS anyelement

//gopgext:function immutable
func Elements(xs pg.AnyArray) iter.Seq[pg.AnyElement] { ... } // RETURNS SETOF anyelement

Types defined in SQL, and existing extensions’ scripts#

Data types, operators, operator classes and casts can also be entirely SQL: put them in sql/*.sql, and implement the functions they need in Go with manual (the CREATE FUNCTION is yours, so one Go function can back several SQL declarations, e.g. the output function of several types) and, to keep an existing extension’s SQL unchanged, symbol=:

// sql/types.sql: CREATE FUNCTION isbn13_in(cstring) RETURNS isbn13
//                    AS 'MODULE_PATHNAME', 'isbn_in' LANGUAGE C IMMUTABLE STRICT; ...
//                CREATE TYPE isbn13 (INPUT = isbn13_in, OUTPUT = ean13_out, LIKE = int8);

//gopgext:function symbol=isbn_in manual
func IsbnIn(s pg.CString) (int64, error) { ... }

Go parameter and result types must match the SQL declaration’s representation (a type LIKE int8 is an int64 in Go); a declaration with fewer arguments than the Go function raises an error when Go reads the missing one. An error returned by an input function called by pg_input_is_valid() or pg_input_error_info() is reported softly (PostgreSQL 16+), as input functions should. contrib/isn is a complete example: 8 types with operators, btree and hash operator classes and casts.