sqlgopgx

How to pass a custom type value to pgx query in Go


I have a custom type:

type CustomData struct{
    data string
}

and I want to pass it as a string parameter to an SQL query

d := CustomData{data: "help, i need somebody"}

_, err := tx.Exec(ctx, `INSERT INTO tab (data) VALUES ($1)`, d)

I get an error

failed to encode args[0]: unable to encode CustomData{data: "help, i need somebody"} into text format for varchar (OID 1043): cannot find encode plan

Which interface should CustomData implement to be unmarshalled as a string?


UPDATE

I implemented pgtype.TextValuer interface as @Adrian wrote in the comment.

func (d *CustomData) TextValue() (pgtype.Text, error) {
    return pgtype.Text{
        String: d.data,
        Valid:  true,
    }, nil
}

Unfortunately it didn't change an error message.


Solution

  • Per the package docs:

    pgx uses the pgtype package to converting Go values to and from PostgreSQL values. It supports many PostgreSQL types directly and is customizable and extendable. User defined data types such as enums, domains, and composite types may require type registration. See that package's documentation for details.

    Per that package's documentation:

    Generally, all Codecs will support interfaces that can be implemented to enable scanning and encoding. For example, PointCodec can use any Go type that implements the PointScanner and PointValuer interfaces. So rather than use pgtype.Point and application can directly use its own point type with pgtype as long as it implements those interfaces.

    Meaning you can implement pgtype.TextScanner and pgtype.TextValuer on any type to create the behavior of a string value.