gopgext/trigger

Trigger and event trigger functions.

API reference: gopgext/trigger.

Trigger and event trigger functions. A //gopgext:function whose only parameter is a *trigger.Data is a trigger function (RETURNS trigger, no SQL arguments); its result is the row the operation goes on with:

//gopgext:function
func Stamp(t *trigger.Data) (*trigger.Row, error) {
	if t.When != trigger.Before || t.New == nil || len(t.Args) != 1 {
		return nil, pg.Errorf(trigger.CodeProtocolViolated, "stamp: BEFORE INSERT OR UPDATE FOR EACH ROW, with a column")
	}
	if err := trigger.Set(t.New, t.Args[0], time.Now()); err != nil { // a timestamptz column
		return nil, err
	}
	return t.New, nil // nil: skip the row
}
CREATE TRIGGER stamp BEFORE INSERT OR UPDATE ON items
	FOR EACH ROW EXECUTE FUNCTION stamp('changed_at');
APIPurpose
Data.When, Op, ForEachRowBefore/After/InsteadOf, Insert/Update/Delete/Truncate, row or statement level
Data.Name, Argsthe trigger’s name and arguments (EXECUTE FUNCTION f('a', 'b'))
Data.Table, Schema, TableOIDthe table or view
Data.Old, Data.Newthe rows before and after the change (row-level; nil when absent)
Data.Columns(), Column(name)the columns: name, number, type (and a domain’s base type), typmod, type name
Data.UpdatedColumns(), Changed(name)the columns an UPDATE assigns; whether a value changed
Row.Any, Text, IsNull; trigger.Get[T]a column as a pg.AnyElement, its text form, or a Go value of its type
Row.SetText, SetAny, SetNull; trigger.Setchange a column of the returned row (SetText applies typmods and domain checks)
*trigger.Event: Event, Tagan event trigger’s event (ddl_command_start, …) and command tag

A function without a row result lets the operation go on unchanged (New, or Old for DELETE); AFTER and statement-level triggers’ results are ignored, and an INSTEAD OF trigger returns nil when it did nothing. Rows are only valid during the call: the tuples stay in C, Go gets their values (pg.AnyElement values can be passed to pg.Exec/Query as is). A function whose only parameter is a *trigger.Event is an event trigger function, returning nothing or an error. examples/triggers shows them all; contrib/tcn, lo, autoinc, insert_username, moddatetime and refint are ports of PostgreSQL’s trigger modules.