CODEWITHSUNDEEP
postgresqlgo
Kevin Shi
Kevin Shi

Reputation: 516

Scan Postgres composite type

I have a composite type defined for use in a table:

CREATE TYPE duration AS (
    hours NUMERIC,
    minutes NUMERIC
);

CREATE TABLE foo (
    id SERIAL PRIMARY KEY,
    my_duration duration
);

INSERT INTO foo (id, my_duration) VALUES
(1, ROW(1, 30));

How can I scan this with database/sql? When I implement the scanner interface with

func (d *Duration) Scan(value interface{}) error {
    log.Println(value)
    if value == nil {
        *d = Duration{
            Hours:   -1,
            Minutes: -1,
        }
        return nil
    }
    if duration, ok := value.(Duration); ok {
        *d = Duration(duration)
        return nil
    }
    return errors.New("Failed to scan duration")
}

The type of the interface comes out as []uint8, and the value itself is an encoded array, something like [40 50 44 51 51 41]. How can I parse the composite type properly?

edit: I have a Duration struct defined as

type Duration struct {
    Hours   int
    Minutes int
}

Upvotes: 1

Views: 863

Answers (1)

mkopriva
mkopriva

Reputation: 38203

The byte slice [40 50 44 51 51 41], when converted to a string and printed to stdout looks like this (2,33), see link.

So you can see that what you're getting back from the db is a tuple with two integers separated by a comma. With that knowledge you can implement the Scanner interface that parses the custom data type values.

func (d *Duration) Scan(value interface{}) error {
    if b, ok := value.([]byte); ok {
        ss := strings.Split(strings.Trim(string(b), "()"), ",")
        if len(ss) != 2 {
            return errors.New("unknown Duration format: " + string(b))
        }

        h, err := strconv.Atoi(ss[0])
        if err != nil {
            return err
        }
        m, err := strconv.Atoi(ss[1])
        if err != nil {
            return err
        }

        d.Hours, d.Minutes = h, m
    } else {
        d.Hours, d.Minutes = -1, -1
    }
    return nil
}

Upvotes: 2

Related Questions