< Prev: CASE
Up: SQL Expressions

SQL Expressions: calling PL/pgSQL functions

Contents

Overview

  contents
There is support for generating type safe definitions from your PL/pgSQL functions, with some caveats: I am already using this feature, but should be considered a WIP. Feedback on it is very much welcome.

Code generation

  contents
The first step would be to generate the function definitions. Make sure you have installed the sqgen-postgres command line tool. 
$ go get github.com/bokwoon95/go-structured-query/cmd/sqgen-postgres
Run sqgen-postgres functions as shown below (replace the $variables accordingly). sqgen will dump a functions.go into a tables folder in the current working directory. If you need any more customization read the flags at sqgen-postgres functions --help. 
$ sqgen-postgres functions --database 'postgres://$username:$password@localhost:$port/$database?sslmode=disable'
Given an simple function like the following 
CREATE FUNCTION simple_func(str TEXT, num INT, b BOOL, tz TIMESTAMPTZ)
RETURNS TABLE (out_str TEXT, out_num INT, out_b BOOL, out_tz TIMESTAMPTZ) AS $$
BEGIN
    RETURN QUERY SELECT str, num, b, tz;
END $$ LANGUAGE plpgsql;
The following definitions will be generated 
type FUNCTION_SIMPLE_FUNC struct {
    *sq.FunctionInfo
    OUT_STR sq.StringField
    OUT_NUM sq.NumberField
    OUT_B   sq.BooleanField
    OUT_TZ  sq.TimeField
}

func SIMPLE_FUNC(
    str string,
    num int,
    b bool,
    tz time.Time,
) FUNCTION_SIMPLE_FUNC {
    return SIMPLE_FUNC_(str, num, b, tz)
}

func SIMPLE_FUNC_(
    str interface{},
    num interface{},
    b interface{},
    tz interface{},
) FUNCTION_SIMPLE_FUNC {
    f := FUNCTION_SIMPLE_FUNC{
        Schema:    "public",
        Name:      "simple_func",
        Arguments: []interface{}{str, num, b, tz},
    }
    f.OUT_STR = sq.NewStringField("out_str", f.FunctionInfo)
    f.OUT_NUM = sq.NewNumberField("out_num", f.FunctionInfo)
    f.OUT_B = sq.NewBooleanField("out_b", f.FunctionInfo)
    f.OUT_TZ = sq.NewTimeField("out_tz", f.FunctionInfo)
    return f
}
Two versions of the same function are generated: SIMPLE_FUNC and SIMPLE_FUNC_. Usually you will use the more strongly-typed SIMPLE_FUNC, but the loosely-typed SIMPLE_FUNC_ exists for you to use it in other contexts: for example, passing in a StringField or NumberField in place of a string or int as an argument. The function is essentially a struct and its member fields are the outputs of the function, which allows you to treat the function call like any other Table (or Field).

Using the function

  contents
If you just want to call a function for its side effects, you can invoke the function using SELECT. 
// SELECT my_function(1, 'abc');
_, err := sq.Select(tables.MY_FUNCTION(1, "abc")).Exec(DB, 0)
If you want to scan the results of the function back into Go, you can use treat the function call like a Table and use Struct Mapping to map the results. 
var outStr string
var outNum int
var outB bool
var outTz time.Time

// SELECT out_str, out_num, out_b, out_tz
// FROM simple_func('abc', 123, true, NOW());
f := tables.SIMPLE_FUNC("abc", 123, true, time.Now())
err := sq.From(f).
    SelectRowx(func(row *sq.Row) {
        outStr = row.String(f.OUT_STR)
        outNum = row.Int(f.OUT_NUM)
        outB = row.Bool(f.OUT_B)
        outTime = row.Time(f.OUT_TZ)
    }).
    Fetch(DB)
< Prev: CASE
Up: SQL Expressions