pg

package module
v3.1.1+incompatible Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Apr 4, 2015 License: BSD-3-Clause Imports: 24 Imported by: 0

README

PostgreSQL client for Golang Build Status

Supports:

  • Basic types: integers, floats, string, bool, time.Time, and pointers to these types.
  • sql.NullBool, sql.NullString, sql.NullInt64 and sql.Float64.
  • sql.Scanner and sql/driver.Valuer interfaces.
  • Arrays.
  • Partially hstore.
  • Transactions.
  • Prepared statements.
  • Notifications: LISTEN/NOTIFY.
  • COPY FROM and COPY TO.
  • Timeouts. Client sends CancelRequest message on timeout.
  • Connection pool.
  • Queries are retried when possible.
  • PostgreSQL to Go struct mapping.

API docs: http://godoc.org/gopkg.in/pg.v2. Make sure to check examples: http://godoc.org/gopkg.in/pg.v2#pkg-examples.

Installation

Install:

go get gopkg.in/pg.v2

Changelog

v3

Not ready for production.

v2
  • Support for named placeholders:
a := &Article{Id: 1, Name: "Hello world"}
_, err := db.ExecOne(`UPDATE articles SET name = ?name WHERE id = ?id`, a)
  • CopyFrom/CopyTo support:
r := strings.NewReader("hello\t5\nworld\t5\nfoo\t3\nbar\t3\n")
res, err := t.db.CopyFrom(r, "COPY test FROM STDIN")
  • Simplify collections:
type Articles []*Article

func (articles *Articles) New() interface{} {
    a := &Article{}
    *articles = append(*articles, a)
    return a
}
v1

Initial release.

Example

package pg_test

import (
	"fmt"

	"gopkg.in/pg.v2"
)

type User struct {
	Name   string
	Emails []string
}

type Users []*User

func (users *Users) New() interface{} {
	u := &User{}
	*users = append(*users, u)
	return u
}

func CreateUser(db *pg.DB, user *User) error {
	_, err := db.ExecOne(`INSERT INTO users VALUES (?name, ?emails)`, user)
	return err
}

func GetUsers(db *pg.DB) ([]*User, error) {
	var users Users
	_, err := db.Query(&users, `SELECT * FROM users`)
	if err != nil {
		return nil, err
	}
	return users, nil
}

func ExampleDB_Query() {
	db := pg.Connect(&pg.Options{
		User: "postgres",
	})
	defer db.Close()

	_, err := db.Exec(`CREATE TEMP TABLE users (name text, emails text[])`)
	if err != nil {
		panic(err)
	}

	err = CreateUser(db, &User{"admin", []string{"admin1@admin", "admin2@admin"}})
	if err != nil {
		panic(err)
	}

	err = CreateUser(db, &User{"root", []string{"root1@root", "root2@root"}})
	if err != nil {
		panic(err)
	}

	users, err := GetUsers(db)
	if err != nil {
		panic(err)
	}

	fmt.Println(users[0], users[1])
	// Output: &{admin [admin1@admin admin2@admin]} &{root [root1@root root2@root]}
}

Documentation

Overview

Package github.com/vmihailenco/pg implements a PostgreSQL client.

Example (ComplexQuery) 
package main

import (
	"fmt"

	"gopkg.in/pg.v3"
)

type ArticleFilter struct {
	Id         int64
	Name       string
	CategoryId int
}

func (f *ArticleFilter) FilterName() pg.Q {
	if f.Name == "" {
		return ""
	}
	return pg.MustFormatQ("AND name = ?", f.Name)
}

func (f *ArticleFilter) FilterCategory() pg.Q {
	if f.CategoryId == 0 {
		return ""
	}
	return pg.MustFormatQ("AND category_id = ?", f.CategoryId)
}

type Article struct {
	Id         int64
	Name       string
	CategoryId int
}

type Articles []*Article

func (articles *Articles) New() interface{} {
	a := &Article{}
	*articles = append(*articles, a)
	return a
}

func CreateArticle(db *pg.DB, article *Article) error {
	_, err := db.ExecOne(`
		INSERT INTO articles (name, category_id)
		VALUES (?name, ?category_id)
	`, article)
	return err
}

func GetArticle(db *pg.DB, id int64) (*Article, error) {
	article := &Article{}
	_, err := db.QueryOne(article, `SELECT * FROM articles WHERE id = ?`, id)
	return article, err
}

func GetArticles(db *pg.DB, f *ArticleFilter) ([]*Article, error) {
	var articles Articles
	_, err := db.Query(&articles, `
		SELECT * FROM articles WHERE 1=1 ?FilterName ?FilterCategory
	`, f)
	if err != nil {
		return nil, err
	}
	return articles, nil
}

func main() {
	db := pg.Connect(&pg.Options{
		User: "postgres",
	})
	defer db.Close()

	_, err := db.Exec(`CREATE TEMP TABLE articles (id serial, name text, category_id int)`)
	if err != nil {
		panic(err)
	}

	err = CreateArticle(db, &Article{Name: "article1", CategoryId: 1})
	if err != nil {
		panic(err)
	}

	err = CreateArticle(db, &Article{Name: "article2", CategoryId: 2})
	if err != nil {
		panic(err)
	}

	articles, err := GetArticles(db, &ArticleFilter{})
	if err != nil {
		panic(err)
	}
	fmt.Printf("%d %v %v\n", len(articles), articles[0], articles[1])

	articles, err = GetArticles(db, &ArticleFilter{CategoryId: 1})
	if err != nil {
		panic(err)
	}
	fmt.Printf("%d %v\n", len(articles), articles[0])

}

Output:

2 &{1 article1 1} &{2 article2 2}
1 &{1 article1 1}
Example (Json) 
package main

import (
	"database/sql/driver"
	"encoding/json"
	"fmt"

	"gopkg.in/pg.v3"
)

type jsonMap map[string]interface{}

func (m *jsonMap) Scan(value interface{}) error {
	return json.Unmarshal(value.([]byte), m)
}

func (m jsonMap) Value() (driver.Value, error) {
	b, err := json.Marshal(m)
	if err != nil {
		return nil, err
	}
	return string(b), nil
}

type Item struct {
	Id   int64
	Data jsonMap
}

type Items []*Item

func (items *Items) New() interface{} {
	i := &Item{}
	*items = append(*items, i)
	return i
}

func CreateItem(db *pg.DB, item *Item) error {
	_, err := db.ExecOne(`INSERT INTO items VALUES (?id, ?data)`, item)
	return err
}

func GetItem(db *pg.DB, id int64) (*Item, error) {
	item := &Item{}
	_, err := db.QueryOne(item, `
		SELECT * FROM items WHERE id = ?
	`, id)
	return item, err
}

func GetItems(db *pg.DB) ([]*Item, error) {
	var items Items
	_, err := db.Query(&items, `
		SELECT * FROM items
	`)
	return items, err
}

func main() {
	db := pg.Connect(&pg.Options{
		User: "postgres",
	})
	defer db.Close()

	_, err := db.Exec(`CREATE TEMP TABLE items (id serial, data json)`)
	if err != nil {
		panic(err)
	}

	item := &Item{
		Id:   1,
		Data: jsonMap{"hello": "world"},
	}
	if err := CreateItem(db, item); err != nil {
		panic(err)
	}

	item, err = GetItem(db, 1)
	if err != nil {
		panic(err)
	}
	fmt.Println(item)
}

Output:

&{1 map[hello:world]}

Index

Examples

Constants

This section is empty.

Variables

View Source 
var (
	ErrSSLNotSupported = errorf("pg: SSL is not enabled on the server")

	ErrNoRows    = errorf("pg: no rows in result set")
	ErrMultiRows = errorf("pg: multiple rows in result set")
)
View Source 
var (
	Discard = discardLoader{}
)

Functions

func AppendQ 

func AppendQ(dst []byte, src string, params ...interface{}) ([]byte, error)

func Decode 

func Decode(dst interface{}, f []byte) error

func DecodeValue 

func DecodeValue(dst reflect.Value, f []byte) error

Types

type Appender 

type Appender interface {
	Append([]byte) []byte
}

type DB 

type DB struct {
	// contains filtered or unexported fields
}

Thread-safe.

func Connect 

func Connect(opt *Options) *DB
Example 
db := pg.Connect(&pg.Options{
	User: "postgres",
})
err := db.Close()
fmt.Println(err)

Output:

<nil>

func (*DB) Begin 

func (db *DB) Begin() (*Tx, error)
Example 
tx, err := db.Begin()
if err != nil {
	panic(err)
}

_, err = tx.Exec("CREATE TEMP TABLE test()")
if err != nil {
	panic(err)
}

err = tx.Rollback()
if err != nil {
	panic(err)
}

_, err = db.Exec("SELECT * FROM test")
fmt.Println(err)

Output:

ERROR #42P01 relation "test" does not exist:

func (*DB) Close 

func (db *DB) Close() error

func (*DB) CopyFrom 

func (db *DB) CopyFrom(r io.Reader, q string, args ...interface{}) (*Result, error)
Example 
_, err := db.Exec("CREATE TEMP TABLE words(word text, len int)")
if err != nil {
	panic(err)
}

r := strings.NewReader("hello,5\nfoo,3\n")
_, err = db.CopyFrom(r, "COPY words FROM STDIN WITH CSV")
if err != nil {
	panic(err)
}

buf := &bytes.Buffer{}
_, err = db.CopyTo(&NopWriteCloser{buf}, "COPY words TO STDOUT WITH CSV")
if err != nil {
	panic(err)
}
fmt.Println(buf.String())

Output:

hello,5
foo,3

func (*DB) CopyTo 

func (db *DB) CopyTo(w io.WriteCloser, q string, args ...interface{}) (*Result, error)

func (*DB) Exec 

func (db *DB) Exec(q string, args ...interface{}) (res *Result, err error)
Example 
res, err := db.Exec("CREATE TEMP TABLE test()")
fmt.Println(res.Affected(), err)

Output:

0 <nil>

func (*DB) ExecOne 

func (db *DB) ExecOne(q string, args ...interface{}) (*Result, error)

func (*DB) Listen 

func (db *DB) Listen(channels ...string) (*Listener, error)

func (*DB) Prepare 

func (db *DB) Prepare(q string) (*Stmt, error)
Example 
stmt, err := db.Prepare("SELECT $1::text, $2::text")
if err != nil {
	panic(err)
}

var s1, s2 string
_, err = stmt.QueryOne(pg.LoadInto(&s1, &s2), "foo", "bar")
fmt.Println(s1, s2, err)

Output:

foo bar <nil>

func (*DB) Query 

func (db *DB) Query(f Factory, q string, args ...interface{}) (res *Result, err error)
Example 
package main

import (
	"fmt"

	"gopkg.in/pg.v3"
)

type User struct {
	Name   string
	Emails []string
}

type Users []*User

func (users *Users) New() interface{} {
	u := &User{}
	*users = append(*users, u)
	return u
}

func CreateUser(db *pg.DB, user *User) error {
	_, err := db.ExecOne(`INSERT INTO users VALUES (?name, ?emails)`, user)
	return err
}

func GetUsers(db *pg.DB) ([]*User, error) {
	var users Users
	_, err := db.Query(&users, `SELECT * FROM users`)
	if err != nil {
		return nil, err
	}
	return users, nil
}

func main() {
	db := pg.Connect(&pg.Options{
		User: "postgres",
	})
	defer db.Close()

	_, err := db.Exec(`CREATE TEMP TABLE users (name text, emails text[])`)
	if err != nil {
		panic(err)
	}

	err = CreateUser(db, &User{"admin", []string{"admin1@admin", "admin2@admin"}})
	if err != nil {
		panic(err)
	}

	err = CreateUser(db, &User{"root", []string{"root1@root", "root2@root"}})
	if err != nil {
		panic(err)
	}

	users, err := GetUsers(db)
	if err != nil {
		panic(err)
	}

	fmt.Println(users[0], users[1])
}

Output:

&{admin [admin1@admin admin2@admin]} &{root [root1@root root2@root]}

func (*DB) QueryOne 

func (db *DB) QueryOne(model interface{}, q string, args ...interface{}) (*Result, error)
Example 
var user struct {
	Name string
}

res, err := db.QueryOne(&user, `
        WITH users (name) AS (VALUES (?))
        SELECT * FROM users
    `, "admin")
fmt.Println(res.Affected(), err)
fmt.Println(user)

Output:

1 <nil>
{admin}

func (*DB) RunInTransaction 

func (db *DB) RunInTransaction(fn func(*Tx) error) error

RunInTransaction runs a function in a transaction. If function returns an error transaction is rollbacked, otherwise transaction is committed.

type Error 

type Error interface {
	Field(byte) string
}

type F 

type F string

SQL field.

func (F) Append 

func (f F) Append(dst []byte) []byte

type Factory 

type Factory interface {
	New() interface{}
}

type IntegrityError 

type IntegrityError struct {
	// contains filtered or unexported fields
}

func (IntegrityError) Error 

func (err IntegrityError) Error() string

func (IntegrityError) Field 

func (err IntegrityError) Field(k byte) string

type Ints 

type Ints []int64
Example 
var nums pg.Ints
_, err := db.Query(&nums, "SELECT generate_series(0, 10)")
fmt.Println(nums, err)

Output:

[0 1 2 3 4 5 6 7 8 9 10] <nil>

func (Ints) Append 

func (ints Ints) Append(dst []byte) []byte

func (*Ints) Load 

func (ints *Ints) Load(colIdx int, colName string, b []byte) error

func (*Ints) New 

func (ints *Ints) New() interface{}

type IntsSet 

type IntsSet map[int64]struct{}

func (IntsSet) Load 

func (set IntsSet) Load(colIdx int, colName string, b []byte) error

func (IntsSet) New 

func (set IntsSet) New() interface{}

type Listener 

type Listener struct {
	// contains filtered or unexported fields
}

Not thread-safe.

Example 
ln, _ := db.Listen("mychan")

done := make(chan struct{})
go func() {
	channel, payload, err := ln.Receive()
	fmt.Printf("%s %q %v", channel, payload, err)
	done <- struct{}{}
}()

_, _ = db.Exec("NOTIFY mychan, ?", "hello world")
<-done

Output:

mychan "hello world" <nil>

func (*Listener) Close 

func (l *Listener) Close() error

func (*Listener) Listen 

func (l *Listener) Listen(channels ...string) error

func (*Listener) Receive 

func (l *Listener) Receive() (channel string, payload string, err error)

func (*Listener) ReceiveTimeout 

func (l *Listener) ReceiveTimeout(readTimeout time.Duration) (channel, payload string, err error)

type Loader 

type Loader interface {
	Load(colIdx int, colName string, b []byte) error
}

func LoadInto 

func LoadInto(values ...interface{}) Loader
Example 
var s1, s2 string
_, err := db.QueryOne(pg.LoadInto(&s1, &s2), "SELECT ?, ?", "foo", "bar")
fmt.Println(s1, s2, err)

Output:

foo bar <nil>

func NewLoader 

func NewLoader(dst interface{}) (Loader, error)

type Options 

type Options struct {
	Network  string
	Host     string
	Port     string
	User     string
	Password string
	Database string
	SSL      bool

	// Params specify connection run-time configuration parameters.
	Params map[string]interface{}

	PoolSize int

	DialTimeout  time.Duration
	ReadTimeout  time.Duration
	WriteTimeout time.Duration

	IdleTimeout        time.Duration
	IdleCheckFrequency time.Duration
}

type Q 

type Q string

Raw SQL query.

func FormatQ 

func FormatQ(src string, params ...interface{}) (Q, error)

func MustFormatQ 

func MustFormatQ(src string, params ...interface{}) Q

func (Q) Append 

func (q Q) Append(dst []byte) []byte

func (Q) AppendRaw 

func (q Q) AppendRaw(dst []byte) []byte

type RawAppender 

type RawAppender interface {
	AppendRaw([]byte) []byte
}

type RecordReader 

type RecordReader interface {
	Read() ([]string, error)
}

type Result 

type Result struct {
	// contains filtered or unexported fields
}

func (*Result) Affected 

func (r *Result) Affected() int

type Stmt 

type Stmt struct {
	// contains filtered or unexported fields
}

Not thread-safe.

func (*Stmt) Close 

func (stmt *Stmt) Close() error

func (*Stmt) Exec 

func (stmt *Stmt) Exec(args ...interface{}) (res *Result, err error)

func (*Stmt) ExecOne 

func (stmt *Stmt) ExecOne(args ...interface{}) (*Result, error)

func (*Stmt) Query 

func (stmt *Stmt) Query(f Factory, args ...interface{}) (res *Result, err error)

func (*Stmt) QueryOne 

func (stmt *Stmt) QueryOne(model interface{}, args ...interface{}) (*Result, error)

type Strings 

type Strings []string
Example 
var strs pg.Strings
_, err := db.Query(
	&strs, "WITH users AS (VALUES ('foo'), ('bar')) SELECT * FROM users")
fmt.Println(strs, err)

Output:

[foo bar] <nil>

func (Strings) Append 

func (strings Strings) Append(dst []byte) []byte

func (*Strings) Load 

func (strings *Strings) Load(colIdx int, colName string, b []byte) error

func (*Strings) New 

func (strings *Strings) New() interface{}

type Tx 

type Tx struct {
	// contains filtered or unexported fields
}

Not thread-safe.

func (*Tx) Commit 

func (tx *Tx) Commit() error

func (*Tx) Exec 

func (tx *Tx) Exec(q string, args ...interface{}) (*Result, error)

func (*Tx) ExecOne 

func (tx *Tx) ExecOne(q string, args ...interface{}) (*Result, error)

func (*Tx) Prepare 

func (tx *Tx) Prepare(q string) (*Stmt, error)

TODO(vmihailenco): track and close prepared statements

func (*Tx) Query 

func (tx *Tx) Query(f Factory, q string, args ...interface{}) (*Result, error)

func (*Tx) QueryOne 

func (tx *Tx) QueryOne(model interface{}, q string, args ...interface{}) (*Result, error)

func (*Tx) Rollback 

func (tx *Tx) Rollback() error

Source Files

View all Source files

Directories

Path Synopsis
Utility functions used in pg package.
 Utility functions used in pg package.

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.