Decode custom type

[sxwebdev]

Env
pgx version: 4.8.1
go version: 1.14.2

Problem

Hey. I have a question. I want to get a list of events containing a nested aggregated array with prices in one request. Prices, in turn, can contain their own nested structures, but in the example below, I did not indicate this. What is the correct way to decode data from PostgreSQL to structures? It would be great if you could help with my example below. Thank.

Example code

package testpgx

import (
	"context"
	"fmt"
	"log"
	"os"
	"time"

	"github.com/jackc/pgx/v4"
)

// Service ...
type Service struct {
	ID   int
	Name string
}

// Price ...
type Price struct {
	ID        int
	Value     int
	Category  string
	Available bool
	Services  []Service
	CreatedAt time.Time
	DeletedAt *time.Time
}

// Event ...
type Event struct {
	ID          int
	Name        string
	Description string
	Address     string
	StartDate   time.Time
	Duration    int
	Prices      []Price
	CreatedAt   time.Time
	DeletedAt   *time.Time
}

// GetEvents ...
func GetEvents(conn *pgx.Conn) ([]Event, error) {
	var items []Event

	rows, err := conn.Query(
		context.Background(),
		`
			SELECT 
				e.id,
				e.name,
				e.description,
				e.address,
				e.start_date,
				e.duration,
				e.created_at,
				e.deleted_at,
				array_agg(array[pr.*]) prices
			FROM events e LEFT JOIN
				(select
					p2.id,
					p2.value,
					p2.category,
					p2.available,
					p2.created_at,
					p2.deleted_at
				from prices p2
				group by p2.id) pr
				ON e.id = pr.event_id
			WHERE e.deleted_at IS NULL GROUP BY e.id ORDER BY e.id DESC
		`,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	for rows.Next() {
		var item Event

		rows.Scan(
			&item.ID,
			&item.Name,
			&item.Description,
			&item.Address,
			&item.StartDate,
			&item.Duration,
			&item.CreatedAt,
			&item.DeletedAt,
			&item.Prices,
		)

		items = append(items, item)
	}

	return items, nil
}

func main() {

	conn, err := pgx.Connect(context.Background(), os.Getenv("DATABASE_URL"))

	events, err := GetEvents(conn)
	if err != nil {
		log.Fatalln(err)
	}
	fmt.Printf("%+v\n", events)

}

[jackc]

There are several ways of doing this.

1. Define custom types. This requires writing code for each type. It gives the highest performance at the expense of writing a fair amount of code. https://pkg.go.dev/github.com/jackc/pgtype?tab=doc#example-package-CustomCompositeTypes
2. Use the generic composite and array type support. Not quite as fast, but easier to work with. See https://pkg.go.dev/github.com/jackc/pgtype?tab=doc#example-package-Composite as well as the code and tests for pgtype.CompositeType and ArrayType.
3. The previous two approaches require registering your data types with pgx. pgtype.CompositeFields is another option for ad hoc composite type handing, but it has some restrictions that are described in the documentation.

[nvcnvn]

Hi @jackc do we have example for Array of CustomCompositeTypes?
For example, I have my custom type implement already, how to implement the Encode and Decode function for the array alias?

// RichText respresnts rich_text custom type
type RichText struct {
	Raw      pgtype.Text
	Rendered pgtype.Text
}

// DecodeBinary implements
func (c *RichText) DecodeBinary(ci *pgtype.ConnInfo, src []byte) error {
	if src == nil {
		return errors.New("NULL values can't be decoded. Scan into a &*RichText to handle NULLs")
	}

	if err := (pgtype.CompositeFields{&c.Raw, &c.Rendered}).DecodeBinary(ci, src); err != nil {
		return err
	}

	return nil
}

// EncodeBinary implements
func (c RichText) EncodeBinary(ci *pgtype.ConnInfo, buf []byte) (newBuf []byte, err error) {
	return (pgtype.CompositeFields{&c.Raw, &c.Rendered}).EncodeBinary(ci, buf)
}

[jackc]

There are several ways of handling arrays, but the simplest is probably pgtype.ArrayType. The tests have a few examples of simple types, but I think it should work the same way for your composite type.

[ElliotNB]

If anyone reading this is using PostgreSQL user defined functions that return custom composite types with arrays, I've found that it's much easier to wrap the function (or query) in to_json. Then you can use the standard json.Unmarshal function to put the JSON response into a struct or empty interface map.

Something like this:

var result map[string]interface{}
q, err := conn.Query(context.Background(), "SELECT * FROM to_json(your_plpgsql_function_goes_here($1, $2, $3));", args...)
// error checking
q.Rows.Next()
err = q.Rows.Scan(&queryResponseJson)
// error checking again
err = json.Unmarshal([]byte(queryResponseJson), &result)
// error checking again
fmt.Println(result)

[jackc]

FWIW, I've done that a fair amount too. Much less ceremony. That said, there are two things to be aware of:

1. JSON numbers are funny things. Very large 64-bit integers or numerics may or may not work exactly right. You will want to test for yourself.
2. It is measurably slower with JSON, but in most cases it probably doesn't matter.