Public Triggers

Summary
Non-automatic SQL trigger.
Automatic SQL trigger.
Trigger function that puts row data in urlencoded form into queue.

Trigger Functions

pgq. logtriga()

Non-automatic SQL trigger.  It puts row data in partial SQL form into queue.  It does not auto-detect table structure, it needs to be passed as trigger arg.

Purpose

Used by Londiste to generate replication events.  The “partial SQL” format is more compact than the urlencoded format but cannot be parsed, only applied.  Which is fine for Londiste.

Parameters

arg1queue name
arg2column type spec string where each column corresponds to one char (k/v/i). if spec string is shorter than column list, rest of columns default to ‘i’.

Column types

kpkey column
vnormal data column
iignore column

Queue event fields

ev_typeI/U/D
ev_datapartial SQL statement
ev_extra1table name

pgq. sqltriga()

Automatic SQL trigger.  It puts row data in partial SQL form into queue.  It autodetects table structure.

Purpose

Written as more flexible version of logtriga to handle exceptional cases where there is no primary key index on table etc.

Parameters

arg1queue name
argXany number of optional arg, in any order

Optinal arguments

SKIPThe actual operation should be skipped
ignore=col1[,col2]don’t look at the specified arguments
pkey=col1[,col2]Set pkey fields for the table, autodetection will be skipped

Queue event fields

ev_typeI/U/D
ev_datapartial SQL statement
ev_extra1table name

pgq. logutriga()

pgq.logutriga() RETURNS TRIGGER AS

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

arg1queue name
argXany number of optional arg, in any order

Optinal arguments

SKIPThe actual operation should be skipped
ignore=col1[,col2]don’t look at the specified arguments
pkey=col1[,col2]Set pkey fields for the table, autodetection will be skipped

Queue event fields

ev_typeI/U/D ‘:’ pkey_column_list
ev_datacolumn values urlencoded
ev_extra1table name

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.logutriga() RETURNS TRIGGER AS
Trigger function that puts row data in urlencoded form into queue.