Q - custom Value function for a slice

[kmansoft]

Hi,

I'm looking into converting from sql.DB to pgx, found an issue with converting between slices and JSONB columns.

type Category struct {
	Color int `json:"color"`
}

type CategorySlice []Category

func (slice CategorySlice) Value() (driver.Value, error) {
	if len(slice) == 0 {
		return nil, nil
	}

	json := jsoniter.ConfigDefault
	return json.Marshal(slice)
}

func (slice *CategorySlice) Scan(value interface{}) error {
	if value == nil {
		*slice = nil
		return nil
	}

	b, ok := value.([]byte)
	if !ok {
		return errors.New("type assertion to []byte failed")
	}

	json := jsoniter.ConfigDefault
	return json.Unmarshal(b, &slice)
}

and then

s3 := []Category{{Color: 1}, {Color: 2}, {Color: 3}}

_, err = pgConnection.Exec(
	context.Background(),
	`INSERT INTO json_test (json_3) VALUES ($1)`,
	s3)
if err != nil {
	log.Fatalf("pgsql exec failed: %s", err)
}

The Value function is not called when using pgx. It is when using sql.DB.

Looks like a bug?

[jackc]

Your are using a []Category as your argument. But Value is defined on CategorySlice. Use CategorySlice and it works.

package main

import (
	"context"
	"database/sql/driver"
	"log"
	"os"

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

type Category struct {
	Color int `json:"color"`
}

type CategorySlice []Category

func (slice CategorySlice) Value() (driver.Value, error) {
	if len(slice) == 0 {
		return nil, nil
	}

	return []byte(`{"foo": "bar"}`), nil
}

func main() {
	ctx := context.Background()

	conn, err := pgx.Connect(ctx, os.Getenv("DATABASE_URL"))
	if err != nil {
		log.Fatal(err)
	}
	defer conn.Close(ctx)

	categories := CategorySlice{{Color: 1}, {Color: 2}, {Color: 3}}

	var result string
	err = conn.QueryRow(ctx, `select $1::json`, categories).Scan(&result)
	if err != nil {
		log.Fatalf("QueryRow failed: %s", err)
	}

	log.Println(result)
}

Output:

jack@glados ~/dev/pgx_issues/pgx-2072 ±master⚡ » go run .
2024/07/03 23:48:32 {"foo": "bar"}