Pg: Add custom scanner

[pahanini]

I need to fetch millions rows from database, but I do not want to store this data into variable. I just want send it into network.

I seepg.Scan() returns orm.ColumnScanner interface and I tried to implement my own:

type MyScanner struct {
}

func (s *MyScanner) ScanColumn(colIdx int, colName string, b []byte) error {
    // send data to network here
    return nil
}

And then I used this struct in Query

   s := MyScanner{}
   db.Query(&s, ```SELECT name FROM foo```)

But I've got this error:

panic: pg: can't find column=name in model=MyScanner [recovered]

Is it possible to implement custom scanner or may be another way to process millions or rows without storing it in variable exists?

[vmihailenco]

MyScanner must implement orm.Model interface. For example see pg.Strings - https://github.com/go-pg/pg/blob/master/pg.go#L85-L153

[pahanini]

Here is a final solution (it also related to this issue https://github.com/go-pg/pg/issues/82)

type Scanner struct {
    id int
    cs orm.ColumnScanner
}

func NewScanner() *Scanner {
    s := Scanner{}
    s.cs = orm.Scan(&s.id)
    return &s
}

func (s *Scanner) NewModel() orm.ColumnScanner {
    return s
}

func (s Scanner) AddModel(_ orm.ColumnScanner) error {
        //         
        // send data to network here !
        // 
    return nil
}

func (s *Scanner) ScanColumn(colIdx int, name string, b []byte) error {
    return s.cs.ScanColumn(colIdx, name, b)
}

func (s *Scanner) Init() error {
    return nil
}

func (Scanner) AfterQuery(_ orm.DB) error {
    return nil
}

func (Scanner) AfterSelect(_ orm.DB) error {
    return nil
}

func (Scanner) BeforeInsert(_ orm.DB) error {
    return nil
}

func (Scanner) AfterInsert(_ orm.DB) error {
    return nil
}

func (Scanner) BeforeUpdate(_ orm.DB) error {
    return nil
}

func (Scanner) AfterUpdate(_ orm.DB) error {
    return nil
}

func (Scanner) BeforeDelete(_ orm.DB) error {
    return nil
}

func (Scanner) AfterDelete(_ orm.DB) error {
    return nil
}

scan := NewScanner()
_, err := db.Query(scan, sql)

[vmihailenco]

Thanks for sharing. It is definitely possible to simplify orm.Model interface by removing before/after hooks - I will give it a try.

As for the https://github.com/go-pg/pg/issues/82 - keep in mind that query results are not buffered anywhere. Both Postgres and Golang try to read all results at once. The only difference with ScanColumn is that you don't create intermediate Go representation of the data and instead send it to the network.

[vmihailenco]

With #721 code can be simplified to:

type Scanner struct {
    id int
    cs orm.ColumnScanner
}

func NewScanner() *Scanner {
    s := Scanner{}
    s.cs = orm.Scan(&s.id)
    return &s
}

func (s *Scanner) Init() error {
    return nil
}

func (s *Scanner) NewModel() orm.ColumnScanner {
    return s
}

func (s Scanner) AddModel(_ orm.ColumnScanner) error {
        //         
        // send data to network here !
        // 
    return nil
}

func (s *Scanner) ScanColumn(colIdx int, name string, b []byte) error {
    return s.cs.ScanColumn(colIdx, name, b)
}

scan := NewScanner()
_, err := db.Query(scan, sql)

Also in most cases you should be able to send data in ScanColumn:

func (s *Scanner) ScanColumn(colIdx int, name string, b []byte) error {
        log.Println("number is", string(b))
    return nil
}