Decoding an array of composite types (v5)

[kszafran]

Hello! I'm trying to decode an array of ROWs. Here's a code sample:

package main

import (
	"database/sql"
	"errors"
	"fmt"
	"github.com/jackc/pgx/v5/pgtype"
	_ "github.com/jackc/pgx/v5/stdlib"
	"testing"
)

func TestCompositeArray(t *testing.T) {
	db, err := sql.Open("pgx", "...")
	if err != nil {
		fmt.Println(err)
		t.FailNow()
	}

	m := pgtype.NewMap()

	var arr []Row
	err = db.QueryRow("SELECT ARRAY[ROW('hello', 123)]").Scan(m.SQLScanner(&arr))
	if err != nil {
		fmt.Println(err)
		t.FailNow()
	}
	fmt.Printf("%#v\n", arr)
}

type Row struct {
	First  string
	Second int
}

func (r *Row) ScanNull() error {
	return errors.New("not supported")
}

func (r *Row) ScanIndex(i int) any {
	switch i {
	case 0:
		return &r.First
	case 1:
		return &r.Second
	default:
		panic("unexpected index")
	}
}

It doesn't work and I'm not quite sure how to proceed. The documentation mentions that custom types need to be registered, but I haven't figured out if it applies here and how to go about doing it. Likely, there's something else wrong with my approach.

[kszafran]

I got it to work as shown below. I'm not sure if there's a simpler way, and if I could use CompositeIndexScanner somehow.

package main

import (
	"database/sql"
	"fmt"
	"github.com/jackc/pgx/v5/pgtype"
	_ "github.com/jackc/pgx/v5/stdlib"
	"testing"
)

func TestCompositeArray(t *testing.T) {
	db, err := sql.Open("pgx", "...")
	if err != nil {
		fmt.Println(err)
		t.FailNow()
	}

	m := pgtype.NewMap()

	text, _ := m.TypeForOID(pgtype.TextOID)
	int4, _ := m.TypeForOID(pgtype.Int4OID)

	var oid uint32 = 999998
	var oidArr uint32 = 999999

	m.RegisterType(&pgtype.Type{
		Codec: &pgtype.CompositeCodec{
			Fields: []pgtype.CompositeCodecField{
				{
					Name: "first",
					Type: text,
				},
				{
					Name: "second",
					Type: int4,
				},
			},
		},
		Name: "something",
		OID:  oid,
	})

	typ, _ := m.TypeForOID(oid)

	m.RegisterType(&pgtype.Type{
		Codec: &pgtype.ArrayCodec{
			ElementType: typ,
		},
		Name: "_something",
		OID:  oidArr,
	})

	m.RegisterDefaultPgType(&[]Something{}, "_something")

	var arr []Something
	err = db.QueryRow("SELECT ARRAY[ROW('hello', 123)]").Scan(m.SQLScanner(&arr))
	if err != nil {
		fmt.Println(err)
		t.FailNow()
	}
	fmt.Printf("%#v\n", arr)
}

type Something struct {
	First  string
	Second int
}

[jackc]

A complication in your first example is that the text format of a record type does not include the type information for its fields. The binary format does and that is what is used by pgx by default. Except it can't use the binary format when used through database/sql.

Registering types helps pgx know what to do in these situations. Though to be honest, I'm not sure how your second example is working. I don't see how the OIDs 999998 or 999999 would ever do anything. Because your query is returning a value of type _record/record[] or OID 2287.

postgres@[local]:5015 pgx_test=# SELECT pg_typeof(ARRAY[ROW('hello', 123)]);
 pg_typeof
───────────
 record[]
(1 row)

postgres@[local]:5015 pgx_test=# SELECT pg_typeof(ARRAY[ROW('hello', 123)])::oid;
 pg_typeof
───────────
      2287
(1 row)

---

I would suggest looking at https://pkg.go.dev/github.com/jackc/pgx/[email protected]/pgtype#CompositeTextScanner. You could implement Scan on your Something struct and use CompositeStructScanner to do the actual parsing.