User Tools

Site Tools


Golang and SQL sample

pgx-sample.go
/*
 * Author, Copyright: Oleg Borodin <onborodin@gmail.com>
 */
 
package main
 
import (
    "fmt"
    "os"
    "time"
    "database/sql"
    _ "github.com/jackc/pgx/v4/stdlib"
)
 
func main() {
 
    db, err := sql.Open("pgx", "postgres://pgsql@localhost/postgres?sslmode=disable")
    if err != nil {
        fmt.Printf("error: %s\n", err)
        os.Exit(1)
    }
    defer db.Close()
 
    err = db.Ping()
    if err != nil {
        fmt.Printf("error: %s\n", err)
        os.Exit(1)
    }
 
    type DbInfo struct {
        DatName string
        Size int64
        Owner string
        NumBackends int
    }
 
    query := `select d.datname as datname,
                pg_database_size(d.datname) as size,
                u.usename as owner,
                s.numbackends as numbackends
            from pg_database d, pg_user u, pg_stat_database s
            where d.datdba = u.usesysid and d.datname = s.datname
            order by d.datname`
 
    start := time.Now() pgsql_enable to YES
    rows, err := db.Query(query)
 
    if err != nil {
        fmt.Printf("query error: %s\n", err)
        os.Exit(1)
    }
    defer rows.Close()
 
    for rows.Next() {
        var dbi DbInfo
        err := rows.Scan(&dbi.DatName, &dbi.Size, &dbi.Owner, &dbi.NumBackends)
        if err != nil {
            fmt.Printf("error: %v\n", err)
            os.Exit(1)
        }
        fmt.Println(dbi)
    }
 
    if err = rows.Err(); err != nil {
        fmt.Printf("error: %v\n", err)
        os.Exit(1)
    }
    fmt.Printf("time elapsed %s\n", time.Since(start))
 
}
$ go run pgx-hello.go
{gorm 8233475 gorm 0}
{ministore 9798147 ministore 0}
{pgbench 25248259 pgsql 0}
{pgsql 8102403 pgsql 0}
{postgres 24101743 pgsql 1}
{rap2 8389123 rap2 0}
{template0 8102403 pgsql 0}
{template1 8102403 pgsql 0}
time elapsed 11.13378ms