To avoid complex 1-to-1 mappings between Postgres tables, I thought it would be a good idea to use composite types instead. So I created these two types, and a table to hold them:
CREATE TYPE my_point AS (x INTEGER, y INTEGER);
CREATE TYPE my_rectangle AS (p1 my_point, p2 my_point);
CREATE TABLE zoo (id INTEGER, r my_rectangle, p my_point);
(The types and tables are just for the example. In the real project, they are quite different, so there's no point in proposing alternative solutions, such as using the built-in Postgres point type)
So far so good. It works as expected when doing manual queries in psql etc. Now to the problem:
I'm trying to access these types using go, pgx and sqlx. For the my_point type, I got a working solution quite easily by mostly copying the point implementation from pgtype. But no such luck for the my_rectangle type. I've tried to come up with multiple solutions, but run into different problems with each. The error I get with the code below for example is:
Unknown OID for model.MyPointType{Point:model.MyPoint{X:20, Y:21}, Status:0x2}
Here's my test function:
func TestCompositeTypes(t *testing.T) {
// Setup database connection ...
conn := ...
ci := conn.ConnInfo()
// Get OID for "my_point"
var myPointOID uint32
err = conn.QueryRow(context.Background(), `select 'my_point'::regtype::oid`).Scan(&myPointOID)
require.NoError(t, err)
// Get OID for "my_rectangle"
var myRectangleOID uint32
err = conn.QueryRow(context.Background(), `select 'my_rectangle'::regtype::oid`).Scan(&myRectangleOID)
require.NoError(t, err)
ci.RegisterDataType(pgtype.DataType{
Value: &MyPointType{},
Name: "my_point",
OID: myPointOID,
})
ci.RegisterDataType(pgtype.DataType{
Value: &MyRectangleType{},
Name: "my_rectangle",
OID: myRectangleOID,
})
p := MyPointType{
Point: MyPoint{50, 100},
Status: pgtype.Present,
}
// This works!
_, err = db.Connection.Exec(
"INSERT INTO zoo (id, p) VALUES ($1, $2::my_point)",
100,
p
)
require.NoError(t, err)
r := MyRectangleType{
P1: MyPointType{Point: MyPoint{20, 21}, Status: pgtype.Present},
P2: MyPointType{Point: MyPoint{30, 31}, Status: pgtype.Present},
Status: pgtype.Present,
}
// This does *not* work! (see error above)
_, err = db.Connection.Exec(
"INSERT INTO zoo (id, r) VALUES ($1, $2::my_rectangle)",
100,
r
)
require.NoError(t, err)
}
Here's the implementation of MyPoint:
type MyPoint struct {
X int
Y int
}
type MyPointType struct {
Point MyPoint
Status pgtype.Status
}
func (p *MyPointType) AssignTo(dst interface{}) error {
return fmt.Errorf("cannot assign %v to %T", p, dst)
}
func (p MyPointType) Get() interface{} {
switch p.Status {
case pgtype.Present:
return p
case pgtype.Null:
return nil
default:
return p.Status
}
}
func parsePoint(src []byte) (*MyPointType, error) {
if src == nil || bytes.Compare(src, []byte("null")) == 0 {
return &MyPointType{Status: pgtype.Null}, nil
}
if len(src) < 5 {
return nil, fmt.Errorf("invalid length for my_point: %v", len(src))
}
if src[0] == '"' && src[len(src)-1] == '"' {
src = src[1 : len(src)-1]
}
parts := strings.SplitN(string(src[1:len(src)-1]), ",", 2)
if len(parts) < 2 {
return nil, fmt.Errorf("invalid format for my_point")
}
x, err := strconv.ParseInt(parts[0], 10, 32)
if err != nil {
return nil, err
}
y, err := strconv.ParseInt(parts[1], 10, 32)
if err != nil {
return nil, err
}
return &MyPointType{Point: MyPoint{int(x), int(y)}, Status: pgtype.Present}, nil
}
func (p *MyPointType) Set(src interface{}) error {
if src == nil {
p.Status = pgtype.Null
return nil
}
err := fmt.Errorf("cannot convert %v to MyPointType", src)
var parsed *MyPointType
switch value := src.(type) {
case string:
parsed, err = parsePoint([]byte(value))
case []byte:
parsed, err = parsePoint(value)
default:
return err
}
if err != nil {
return err
}
*p = *parsed
return nil
}
func (p MyPointType) EncodeBinary(ci *pgtype.ConnInfo, buf []byte) ([]byte, error) {
switch p.Status {
case pgtype.Null:
return nil, nil
case pgtype.Undefined:
return nil, errUndefined
}
// This is how pgtype.Point does it, but I think my implementation below
// works similarly and is more safe...?
//
// buf = pgio.AppendUint64(buf, math.Float64bits(src.P.X))
// buf = pgio.AppendUint64(buf, math.Float64bits(src.P.Y))
// return buf, nil
return (pgtype.CompositeFields{int32(p.Point.X), int32(p.Point.Y)}).EncodeBinary(ci, buf)
}
And here's the implementation of MyRectangleType:
type MyRectangleType struct {
P1 MyPointType
P2 MyPointType
Status pgtype.Status
}
func (r *MyRectangleType) AssignTo(dst interface{}) error {
return fmt.Errorf("cannot assign %v to %T", r, dst)
}
func (r MyRectangleType) Get() interface{} {
switch r.Status {
case pgtype.Present:
return r
case pgtype.Null:
return nil
default:
return r.Status
}
}
func parseRectangle(src []byte) (*MyRectangleType, error) {
if src == nil || bytes.Compare(src, []byte("null")) == 0 {
return &MyRectangleType{Status: pgtype.Null}, nil
}
if len(src) < 5 {
return nil, fmt.Errorf("invalid length for my_rectangle: %v", len(src))
}
if src[0] == '"' && src[len(src)-1] == '"' {
src = src[1 : len(src)-1]
}
parts := strings.SplitN(string(src[1:len(src)-1]), ",", 2)
if len(parts) < 2 {
return nil, fmt.Errorf("invalid format for my_rectangle")
}
p1, err := parsePoint([]byte(parts[0]))
if err != nil {
return nil, err
}
p2, err := parsePoint([]byte(parts[1]))
if err != nil {
return nil, err
}
return &MyRectangleType{P1: *p1, P2: *p2, Status: pgtype.Present}, nil
}
func (r *MyRectangleType) Set(src interface{}) error {
if src == nil {
r.Status = pgtype.Null
return nil
}
err := fmt.Errorf("cannot convert %v to MyRectangleType", src)
var parsed *MyRectangleType
switch value := src.(type) {
case string:
parsed, err = parseRectangle([]byte(value))
case []byte:
parsed, err = parseRectangle(value)
default:
return err
}
if err != nil {
return err
}
*r = *parsed
return nil
}
func (r MyRectangleType) EncodeBinary(ci *pgtype.ConnInfo, buf []byte) ([]byte, error) {
switch r.Status {
case pgtype.Null:
return nil, nil
case pgtype.Undefined:
return nil, errUndefined
}
return (pgtype.CompositeFields{
r.P1,
r.P2,
}).EncodeBinary(ci, buf)
}
What do I do wrong? What can be improved? I'm more concerned about readability than performance.
