Running a SELECT * FROM table query in Pgx?

Question

I'm using PostgreSQL with , and I'm trying to run a SELECT * FROM query in Pgx.

I can't seem to get the iteration down, as it only returns the last key in the table. I'm also trying to serve this as JSON on running the echo HTTP server library.

main.go function ( importing connection.Conn from my database connection file)

func getLanguages(c echo.Context) (err error) {

    Conn := connection.Conn
    type Row struct {
        Id       int
        Language string
        Name     string
    }

    rowArray := Row{}

    rows, err := Conn.Query(context.Background(), "SELECT * FROM languages")

    defer rows.Close()

    // rowsCount := 0

    for rows.Next() {
        err := rows.Scan(&rowArray.Id, &rowArray.Language, &rowArray.Name)
        if err != nil {
            log.Fatal(err)
        }
    }

    fmt.Println(rowArray)

    return c.JSON(http.StatusOK, rowArray)

} 

Expected Output

 id |  language  |        name         
----+------------+---------------------
  1 | none       | Plaintext
  2 | go         | Golang
  3 | python     | Python
  4 | js         | JavaScript
  5 | jsx        | React JSX
  6 | ts         | TypeScript
  7 | tsx        | React TSX
  8 | tsconfig   | TSConfig
  9 | rb         | Ruby
 10 | sql        | SQL
 11 | sol        | Solidity (Ethereum)
 12 | html       | HTML
 13 | css        | CSS
 14 | csharp     | C#
 15 | haskell    | Haskell
 16 | rust       | Rust
 17 | scala      | Scala
 18 | svg        | SVG
 19 | graphql    | GraphQL
 20 | php        | PHP
 21 | powershell | PowerShell
 22 | yaml       | YAML
 23 | json       | JSON

Actual Output

{"Id":23,"Language":"json","Name":"JSON"} 

Any help?

Mcauliffe

Answer 1

rowArray := Row{} creates a single instance of Row. You then loop through the results and store them in that same variable. So each iteration will overwrite whatever was retrieved previously. If there are multiple results then use a slice; something like the following, untested, code:

rows, err := Conn.Query(context.Background(), "SELECT * FROM languages")
if err != nil {
    log.Fatal(err)
}
defer rows.Close()

var rowSlice []Row
for rows.Next() {
    var r Row
    err := rows.Scan(&r.Id, &r.Language, &r.Name)
    if err != nil {
        log.Fatal(err)
    }
   rowSlice = append(rowSlice, r)
}
if err := rows.Err(); err != nil {
    log.Fatal(err)
}

fmt.Println(rowSlice)

Engel

Thank you so much! I'm a beginner at go, and I had no idea about a lot of stuff, do thank you for educating me about the usage of slices! – Mcauliffe

If you have not come across slices previously then I'd recommend completing A Tour of Go which covers the basics really well. – Engel