Go sql return multiple row from Scan

Question

I am using Postgresql database . Here is my code block where I want to return multiple rows:

type NewsPaper struct {
    language string
    logo_url string
    slug string
    ranking string
    title string
    search_term string
}

func GetNewsPapers()  []byte{
    db := GetDB()
    newspaper := NewsPaper{}
    var json = jsoniter.ConfigCompatibleWithStandardLibrary

    rows, err := db.Query(`SELECT title, language, ranking, slug, search_term, logo_url FROM public.news_newspaper`)
    if err != nil {
        log.Println(err)
    }

    defer rows.Close()
    //results := []NewsPaper  // error : type []NewsPaper is not an expression
    for rows.Next() {

        err = rows.Scan(&newspaper.title, &newspaper.language, &newspaper.ranking, &newspaper.slug, &newspaper.search_term, &newspaper.logo_url)
        if err != nil {
            log.Println(err)
        }
        //results = append(results, newspaper)
    }
    fmt.Println(newspaper)  // printing last row
    err = rows.Err()
    if err != nil {
        panic(err)
    }
    values, _ := json.Marshal(newspaper)
    //fmt.Println(values)

    return values
}

Problem is its returning last row from the table. I also tried to store result in array, but I am not succeed.

If you are going to write a lot of code you can also consider SelectContext method of jmoiron.github.io/sqlx. If you need more info on Go and postgres I can also recommend medium.com/avitotech/… — Oleg
– Oleg, Commented May 14, 2020 at 9:47

Eklavya · Accepted Answer · 2020-05-14 05:56:38Z

6

You are setting same struct variable for every row that's why you are getting last row info only.

Use results := []NewsPaper{} for create empty slice and create new struct for every row. This should work, I added some comments.

rows, err := db.Query(`SELECT title, language, ranking, slug, search_term, logo_url FROM public.news_newspaper`)
if err != nil {
    log.Println(err)
}
results := []NewsPaper{} // creating empty slice
defer rows.Close()
for rows.Next() {
    newspaper := NewsPaper{} // creating new struct for every row
    err = rows.Scan(&newspaper.title, &newspaper.language, &newspaper.ranking, &newspaper.slug, &newspaper.search_term, &newspaper.logo_url)
    if err != nil {
        log.Println(err)
    }
    results = append(results, newspaper) // add new row information
}
fmt.Println(results)  // printing result

answered May 14, 2020 at 5:56

Eklavya

18.6k4 gold badges33 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Oleg · Accepted Answer · 2020-05-14 10:30:02Z

An alternative way for basic CRUD

import (
    "github.com/jmoiron/sqlx"
    "context"
)


type NewsPaper struct {
    Language   string `json:"language" db:"language"`
    LogoURL    string `json:"logo_url" db:"logo_url"`
    Slug       string `json:"slug" db:"slug"`
    Ranking    string `json:"ranking" db:"ranking"`
    Title      string `json:"title" db:"title"`
    SearchTerm string `json:"search_term" db:"search_term"`
}

type NewsPaperRepository interface {
    List(ctx context.Context) ([]NewsPaper, error)
}

type newsPaperRepository struct {
    db *sqlx.DB
}

func (r *newsPaperRepository) List(ctx context.Context) ([]NewsPaper, error) {

    const selectQuery = `SELECT title, language, ranking, slug, search_term, logo_url FROM public.news_newspaper`
    var newsPapers []NewsPaper
    if err := r.db.SelectContext(ctx, &newsPapers, selectQuery); err != nil {
        return nil, fmt.Errorf("news paper repository/problem while trying to retrieve news papers from database: %w", err)
    }
    return newsPapers, nil
}

Collectives™ on Stack Overflow

Go sql return multiple row from Scan

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related