# 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 contents of old row to ev_extra2 (urlenc/json)
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 backup of old row (urlenc/json)

#### 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

> pgq.sqltriga() RETURNS trigger AS

Trigger that generates queue events containing partial SQL.
Close