plgo

README

plgo

Tool for easily creating PostgreSQL extensions with stored procedures and triggers in golang. It creates wrapper code, PostgreSQL extension files and builds your package.

contribution of all kind welcome!

Prerequisites for Linux

sudo apt-get install postgresql-server-dev-X.Y #Replace X.Y with your version of Postgres

installation

go get -u gitlab.com/microo8/plgo/plgo

write functions

Creating new stored procedures with plgo is easy:

Create a package where your procedures will be declared:

//must be main package

package main

import (
	"log"
	"strings"
	"time"

	"gitlab.com/microo8/plgo"
)

//from every exported function will be generated a stored procedure
//functions can take (and return) any golang builtin type (like string, int, float64, []int, ...)

func Meh() {
    //NoticeLogger for printing notice messages to elog
    logger := plgo.NewNoticeLogger("", log.Ltime|log.Lshortfile)
    logger.Println("meh")
}

//ConcatAll concatenates all values of an column in a given table
func ConcatAll(tableName, colName string) string {
    //ErrorLogger for printing error messages to elog
    logger := plgo.NewErrorLogger("", log.Ltime|log.Lshortfile)
    db, err := plgo.Open() //open the connection to DB
    if err != nil {
        logger.Fatalf("Cannot open DB: %s", err)
    }
    defer db.Close() //db must be closed
    query := "select " + colName + " from " + tableName
    stmt, err := db.Prepare(query, nil) //prepare an statement
    if err != nil {
        logger.Fatalf("Cannot prepare query statement (%s): %s", query, err)
    }
    rows, err := stmt.Query() //execute statement
    if err != nil {
        logger.Fatalf("Query (%s) error: %s", query, err)
    }
    var ret string
    for rows.Next() { //iterate over the rows
        var val string
        rows.Scan(&val)
        ret += val
    }
    return ret
}

//CreatedTimeTrigger is an trigger function
//trigger function must have the first argument of type *plgo.TriggerData
//and must return *plgo.TriggerRow
func CreatedTimeTrigger(td *plgo.TriggerData) *plgo.TriggerRow {
    td.NewRow.Set(4, time.Now()) //set the 4th column to now()
    return td.NewRow //return the new modified row
}

//ConcatArray concatenates an array of strings
//function arguments (and return values) can be also array types of the golang builtin types
func ConcatArray(strs []string) string {
    return strings.Join(strs, "")
}

create extension

build the PostgreSQL extension with $ plgo [path/to/package]

this will create an directory named build, where the compiled shared object will be and also all files needed for the extension installation (like Makefile, extention.sql, ...)

install extension

go to the build directory and install your new extension:

$ cd build
$ sudo make install with_llvm=no

this installs your extension to DB. You can then use this extension in db:

CREATE EXTENSION myextention;

Finally you can happily run your the functions in your queries select concatarray(array['foo','bar'])

output:

 concatarray
-------------
 foobar
(1 row)

use of goroutines

Using goroutines is possible, but very tricky. The allocation of the stack for the goroutine is bigger than max_stack_depth. Running an procedure that spins-up some goroutines ends with crashing:

ERROR:  stack depth limit exceeded
HINT:  Increase the configuration parameter "max_stack_depth" (currently 7680kB), after ensuring the platform's stack depth limit is adequate.

Setting it to the kernel maximum (ulimit -s) doesn't help. But the size of allocated stack is checked by the DB only when calling some statement. So you can probably play with that. You get all the data from the DB at the beginning of your procedure and then spin-up some goroutines, after that don't touch the DB. But I don't recommend doing it.

todo

• Own type definition!
• Background Worker Processes!
• Functions returning SETOF

Documentation

Index

• func NewErrorLogger(prefix string, flag int) *log.Logger
• func NewNoticeLogger(prefix string, flag int) *log.Logger
• type DB
  • func Open() (*DB, error)
  • func (db *DB) Close() error
  • func (db *DB) Prepare(query string, types []string) (*Stmt, error)
• type Datum
• type Row
  • func (row *Row) Scan(args ...interface{}) error
• type Rows
  • func (rows *Rows) Columns() ([]string, error)
  • func (rows *Rows) Next() bool
  • func (rows *Rows) Scan(args ...interface{}) error
• type Stmt
  • func (stmt *Stmt) Exec(args ...interface{}) error
  • func (stmt *Stmt) Query(args ...interface{}) (*Rows, error)
  • func (stmt *Stmt) QueryRow(args ...interface{}) (*Row, error)
• type TriggerData
  • func (td *TriggerData) FiredAfter() bool
  • func (td *TriggerData) FiredBefore() bool
  • func (td *TriggerData) FiredByDelete() bool
  • func (td *TriggerData) FiredByInsert() bool
  • func (td *TriggerData) FiredByTruncate() bool
  • func (td *TriggerData) FiredByUpdate() bool
  • func (td *TriggerData) FiredForRow() bool
  • func (td *TriggerData) FiredForStatement() bool
  • func (td *TriggerData) FiredInstead() bool
• type TriggerRow
  • func (row *TriggerRow) Scan(args ...interface{}) error
  • func (row *TriggerRow) Set(i int, val interface{})

Functions

func NewErrorLogger

func NewErrorLogger(prefix string, flag int) *log.Logger

NewErrorLogger creates an logger that writes into ERROR elog

func NewNoticeLogger

func NewNoticeLogger(prefix string, flag int) *log.Logger

NewNoticeLogger creates an logger that writes into NOTICE elog

Types

type DB

type DB struct{}

DB represents the db connection, can be made only once

func Open

func Open() (*DB, error)

Open returns DB connection and runs SPI_connect

func (*DB) Close

func (db *DB) Close() error

Close closes the DB connection

func (*DB) Prepare

func (db *DB) Prepare(query string, types []string) (*Stmt, error)

Prepare prepares an SQL query and returns a Stmt that can be executed query - the SQL query types - an array of strings with type names from postgresql of the prepared query

type Datum

type Datum C.Datum

Datum is the return type of postgresql

type Row

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

Row represents a single row from running a query

func (*Row) Scan

func (row *Row) Scan(args ...interface{}) error

Scan scans the args from Row

type Rows

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

Rows represents the result of running a prepared Stmt with Query

func (*Rows) Columns

func (rows *Rows) Columns() ([]string, error)

Columns returns the names of columns

func (*Rows) Next

func (rows *Rows) Next() bool

Next sets the Rows to another row, returs false if there isn't another must be first called to set the Rows to the first row

func (*Rows) Scan

func (rows *Rows) Scan(args ...interface{}) error

Scan takes pointers to variables that will be filled with the values of the current row

type Stmt

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

Stmt represents the prepared SQL statement

func (*Stmt) Exec

func (stmt *Stmt) Exec(args ...interface{}) error

Exec executes a prepared query Stmt with no result

func (*Stmt) Query

func (stmt *Stmt) Query(args ...interface{}) (*Rows, error)

Query executes the prepared Stmt with the provided args and returns multiple Rows result, that can be iterated

func (*Stmt) QueryRow

func (stmt *Stmt) QueryRow(args ...interface{}) (*Row, error)

QueryRow executes the prepared Stmt with the provided args and returns one row result

type TriggerData

type TriggerData struct {
	OldRow *TriggerRow
	NewRow *TriggerRow
	// contains filtered or unexported fields
}

TriggerData represents the data passed by the trigger manager

func (*TriggerData) FiredAfter

func (td *TriggerData) FiredAfter() bool

FiredAfter returns true if the trigger fired after the operation.

func (*TriggerData) FiredBefore

func (td *TriggerData) FiredBefore() bool

FiredBefore returns true if the trigger fired before the operation.

func (*TriggerData) FiredByDelete

func (td *TriggerData) FiredByDelete() bool

FiredByDelete returns true if the trigger was fired by a DELETE command.

func (*TriggerData) FiredByInsert

func (td *TriggerData) FiredByInsert() bool

FiredByInsert returns true if the trigger was fired by an INSERT command.

func (*TriggerData) FiredByTruncate

func (td *TriggerData) FiredByTruncate() bool

FiredByTruncate returns true if the trigger was fired by a TRUNCATE command.

func (*TriggerData) FiredByUpdate

func (td *TriggerData) FiredByUpdate() bool

FiredByUpdate returns true if the trigger was fired by an UPDATE command.

func (*TriggerData) FiredForRow

func (td *TriggerData) FiredForRow() bool

FiredForRow returns true if the trigger fired for a row-level event.

func (*TriggerData) FiredForStatement

func (td *TriggerData) FiredForStatement() bool

FiredForStatement returns true if the trigger fired for a statement-level event.

func (*TriggerData) FiredInstead

func (td *TriggerData) FiredInstead() bool

FiredInstead returns true if the trigger fired instead of the operation.

type TriggerRow

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

TriggerRow is used in TriggerData as NewRow and OldRow

func (*TriggerRow) Scan

func (row *TriggerRow) Scan(args ...interface{}) error

Scan sets the args from the TriggerRow

func (*TriggerRow) Set

func (row *TriggerRow) Set(i int, val interface{})

Set sets the i'th value in the row