Public Triggers

Summary

Public Triggers
Trigger Functions
pgq.jsontriga()	Trigger function that puts row data in JSON-encoded form into queue.
pgq.logutriga()	Trigger function that puts row data in urlencoded form into queue.
pgq.sqltriga()	Trigger that generates queue events containing partial SQL.

Trigger Functions

pgq.jsontriga()

Trigger function that puts row data in JSON-encoded form into queue.

Purpose

Convert row data into easily parseable form.

Trigger parameters

arg1	queue name
argX	any number of optional arg, in any order

Optional arguments

SKIP	The actual operation should be skipped (BEFORE trigger)
ignore=col1[,col2]	don’t look at the specified arguments
pkey=col1[,col2]	Set pkey fields for the table, autodetection will be skipped
backup	Put urlencoded contents of old row to ev_extra2
colname=EXPR	Override field value with SQL expression. Can reference table columns. colname can be: ev_type, ev_data, ev_extra1 .. ev_extra4
when=EXPR	If EXPR returns false, don’t insert event.

Queue event fields

ev_type	I/U/D ‘:’ pkey_column_list
ev_data	column values urlencoded
ev_extra1	table name
ev_extra2	optional urlencoded backup

Regular listen trigger example

CREATE TRIGGER triga_nimi AFTER INSERT OR UPDATE ON customer
FOR EACH ROW EXECUTE PROCEDURE pgq.jsontriga('qname');

Redirect trigger example

CREATE TRIGGER triga_nimi BEFORE INSERT OR UPDATE ON customer
FOR EACH ROW EXECUTE PROCEDURE pgq.jsontriga('qname', 'SKIP');

pgq.logutriga()

Trigger function that puts row data in urlencoded form into queue.

Purpose

Used as producer for several PgQ standard consumers (cube_dispatcher, queue_mover, table_dispatcher). Basically for cases where the consumer wants to parse the event and look at the actual column values.

Trigger parameters

arg1	queue name
argX	any number of optional arg, in any order

Optinal arguments

SKIP	The actual operation should be skipped (BEFORE trigger)
ignore=col1[,col2]	don’t look at the specified arguments
pkey=col1[,col2]	Set pkey fields for the table, autodetection will be skipped
backup	Put urlencoded contents of old row to ev_extra2
colname=EXPR	Override field value with SQL expression. Can reference table columns. colname can be: ev_type, ev_data, ev_extra1 .. ev_extra4
when=EXPR	If EXPR returns false, don’t insert event.

Queue event fields

ev_type	I/U/D ‘:’ pkey_column_list
ev_data	column values urlencoded
ev_extra1	table name
ev_extra2	optional urlencoded backup

Regular listen trigger example

CREATE TRIGGER triga_nimi AFTER INSERT OR UPDATE ON customer
FOR EACH ROW EXECUTE PROCEDURE pgq.logutriga('qname');

Redirect trigger example

CREATE TRIGGER triga_nimi BEFORE INSERT OR UPDATE ON customer
FOR EACH ROW EXECUTE PROCEDURE pgq.logutriga('qname', 'SKIP');

pgq.sqltriga()

pgq.sqltriga() RETURNS trigger AS

Trigger that generates queue events containing partial SQL. It autodetects table structure.

Purpose

Replication events, that only need changed column values.

Parameters

arg1	queue name
argX	any number of optional arg, in any order

Optinal arguments

SKIP	The actual operation should be skipped (BEFORE trigger)
ignore=col1[,col2]	don’t look at the specified arguments
pkey=col1[,col2]	Set pkey fields for the table, PK autodetection will be skipped
backup	Put urlencoded contents of old row to ev_extra2
colname=EXPR	Override field value with SQL expression. Can reference table columns. colname can be: ev_type, ev_data, ev_extra1 .. ev_extra4
when=EXPR	If EXPR returns false, don’t insert event.

Queue event fields

ev_type	I/U/D
ev_data	partial SQL statement
ev_extra1	table name
ev_extra2	optional urlencoded backup