with this - it means that nothing was made by SPI manager.

NOTE! SPI_finish() MUST be called by connected procedure or you may get

unpredictable results!

unpredictable results! But you are able to don't call SPI_finish() if you

abort transaction (via elog(WARN)).

int SPI_exec(char *query, int tcount)

this query is done!

Data changes visibility

PostgreSQL data changes visibility rule: during query execution data

changes made by query itself (via SQL-function, SPI-function, triggers)

are invisible to the query scan.

For example, in query

INSERT INTO a SELECT * FROM a

tuples inserted are invisible for SELECT' scan.

But also note that

changes made by query Q are visible by queries which are started after

query Q, no matter - are they started inside Q (during execution of Q) or

after Q is done.

Last example of usage SPI function below demonstrates visibility rule.

Examples

There are complex examples in contrib/spi and in

-----

3 <<< 10 is max value only, 3 is real # of tuples

(1 row)

vac=> delete from a;

DELETE 3

vac=> insert into a values (execq('select * from a', 0) + 1);

INSERT 167712 1

vac=> select * from a;

x

-

1 <<< no tuples in a (0) + 1

(1 row)

vac=> insert into a values (execq('select * from a', 0) + 1);

NOTICE:EXECQ: 0

INSERT 167713 1

vac=> select * from a;

x

-

1

2 <<< there was single tuple in a + 1

(2 rows)

-- This demonstrates data changes visibility rule:

vac=> insert into a select execq('select * from a', 0) * x from a;

NOTICE:EXECQ: 1

NOTICE:EXECQ: 2

NOTICE:EXECQ: 1

NOTICE:EXECQ: 2

NOTICE:EXECQ: 2

INSERT 0 2

vac=> select * from a;

x

-

1

2

2 <<< 2 tuples * 1 (x in first tuple)

6 <<< 3 tuples (2 + 1 just inserted) * 2 (x in second tuple)

(4 rows) ^^^^^^^^

tuples visible to execq() in different invocations

PostgreSQL Trigger Programming Guide

For the lack of Procedural Language (PL) in current version of

PostgreSQL, there is only ability to specify call to a C-function as trigger

action.

Also, STATEMENT-level trigger events are not supported in current

version, and so you are only able to specify BEFORE | AFTER

INSERT|DELETE|UPDATE of a tuple as trigger event.

If trigger event occures, trigger manager (called by Executor)

initializes global structure TriggerData *CurrentTriggerData (described

below) and calls trigger function to handle event.

Trigger function must be created before trigger creation as function

not accepting any arguments and returns opaque.

Actually, there are two specific features in triggers handling.

First, in CREATE TRIGGER one may specify arguments for trigger

function (EXECUTE PROCEDURE tfunc (aa,'bb', 1)), and these arguments

will be passed to trigger function in CurrentTriggerData.

It allows to use single function for many triggers and process events in

different ways.

Also, function may be used for triggering different relations (these

functions are named as "general trigger functions").

Second, trigger function has to return HeapTuple to upper Executor.

No matter for triggers fired AFTER operation (INSERT, DELETE, UPDATE),

but it allows to BEFORE triggers:

- return NULL to skip operation for current tuple (and so tuple

will not be inserted/updated/deleted);

- return pointer to another tuple (INSERT and UPDATE only) which will be

inserted (as new version of updated tuple if UPDATE) instead of

original tuple.

Note, that there is no initialization performed by CREATE TRIGGER

handler. It will be changed in the future.

Also, if more than one trigger defined for the same event on the same

relation then order of trigger firing is unpredictable. It may be changed in

the future.

Also, if a trigger function executes SQL-queries (using SPI) then these

queries may fire triggers again. This is known as cascading of triggers.

There is no explicit limitation for number of cascade levels.

If a trigger is fired by INSERT and inserts new tuple in the same

relation then this trigger will be fired again. Currently, there is nothing

provided for synchronization (etc) of these cases. It may be changed. At

the moment, there is function funny_dup17() in the regress tests which uses

some technics to stop recursion (cascading) of itself...

Interaction with trigger manager

As it's mentioned above when function is called by trigger manager

structure TriggerData *CurrentTriggerData is NOT NULL and initialized. And

so, it's better to check CurrentTriggerData against being NULL in the

begining and set it to NULL just after fetching information - to prevent

calls to trigger function not from trigger manager.

struct TriggerData is defined in src/include/commands/trigger.h:

typedef struct TriggerData

{

TriggerEventtg_event;

Relationtg_relation;

HeapTupletg_trigtuple;

HeapTupletg_newtuple;

Trigger*tg_trigger;

} TriggerData;

tg_event

describes event for what function is called. You may use macros

to deal with tg_event:

TRIGGER_FIRED_BEFORE(event) returns TRUE if trigger fired BEFORE;

TRIGGER_FIRED_AFTER(event) returns TRUE if trigger fired AFTER;

TRIGGER_FIRED_FOR_ROW(event) returns TRUE if trigger fired for

ROW-level event;

TRIGGER_FIRED_FOR_STATEMENT(event) returns TRUE if trigger fired for

STATEMENT-level event;

TRIGGER_FIRED_BY_INSERT(event) returns TRUE if trigger fired by INSERT;

TRIGGER_FIRED_BY_DELETE(event) returns TRUE if trigger fired by DELETE;

TRIGGER_FIRED_BY_UPDATE(event) returns TRUE if trigger fired by UPDATE.

tg_relation

is pointer to structure describing triggered relation. Look @

src/include/utils/rel.h about this structure. The most interest things

are tg_relation->rd_att (descriptor of relation tuples) and

tg_relation->rd_rel->relname (relation' name. This is not char*, but

NameData - use SPI_getrelname(tg_relation) to get char* to copy of name).

tg_trigtuple

is tuple (pointer) for which trigger is fired. This is tuple to being

inserted (if INSERT), deleted (if DELETE) or updated (if UPDATE).

If INSERT/DELETE then this is what you are to return to Executor if

you don't want to replace tuple with another one (INSERT) or skip

operation.

tg_newtuple

is pointer to new version of tuple if UPDATE and NULL if INSERT/DELETE.

This is what you are to return to Executor if UPDATE and you don't want

to replace tuple with another one or skip operation.

tg_trigger

is pointer to structure Trigger defined in src/include/utils/rel.h:

typedef struct Trigger

{

char*tgname;

Oidtgfoid;

func_ptrtgfunc;

int16tgtype;

int16tgnargs;

int16tgattr[8];

char**tgargs;

} Trigger;

tgname is trigger' name, tgnargs is number of arguments in tgargs, tgargs

is array of pointers to arguments specified in CREATE TRIGGER. Other

members are for internal use.

Data changes visibility

PostgreSQL data changes visibility rule: during query execution data

changes made by query itself (via SQL-function, SPI-function, triggers)

are invisible to the query scan.

For example, in query

INSERT INTO a SELECT * FROM a

tuples inserted are invisible for SELECT' scan.

But keep in mind notices about visibility in SPI documentation:

changes made by query Q are visible by queries which are started after

query Q, no matter - are they started inside Q (during execution of Q) or

after Q is done.

This is true for triggers as well. And so, though tuple being inserted

(tg_trigtuple) is not visible to queries in BEFORE trigger, this tuple (just

inserted) is visible to queries in AFTER trigger, and to queries in

BEFORE/AFTER triggers fired after this!

Examples

There are complex examples in contrib/spi and in

src/test/regress/regress.c.

This is very simple example of trigger usage. Function trigf reports

about number of tuples in triggered relation ttest and in trigger fired

BEFORE INSERT/UPDATE checks against is attribute x NULL and skips operations

for NULLs (ala NOT NULL implementation using triggers without aborting

transaction if NULL).

----------------------------------------------------------------------------

#include "executor/spi.h"/* this is what you need to work with SPI */

#include "commands/trigger.h"/* -"- and triggers */

HeapTupletrigf(void);

HeapTuple

trigf()

{

TupleDesctupdesc;

HeapTuplerettuple;

char*when;

boolchecknull = false;

boolisnull;

intret, i;

if (!CurrentTriggerData)

elog(WARN, "trigf: triggers are not initialized");

/* tuple to return to Executor */

if (TRIGGER_FIRED_BY_UPDATE(CurrentTriggerData->tg_event))

rettuple = CurrentTriggerData->tg_newtuple;

else

rettuple = CurrentTriggerData->tg_trigtuple;

/* check for NULLs ? */

if (!TRIGGER_FIRED_BY_DELETE(CurrentTriggerData->tg_event) &&

TRIGGER_FIRED_BEFORE(CurrentTriggerData->tg_event))

checknull = true;

if (TRIGGER_FIRED_BEFORE(CurrentTriggerData->tg_event))

when = "before";

else

when = "after ";

tupdesc = CurrentTriggerData->tg_relation->rd_att;

CurrentTriggerData = NULL;

/* Connect to SPI manager */

if ((ret = SPI_connect()) < 0)

elog(WARN, "trigf (fired %s): SPI_connect returned %d", when, ret);

/* Get number of tuples in relation */

ret = SPI_exec("select count(*) from ttest", 0);

if (ret < 0)

elog(WARN, "trigf (fired %s): SPI_exec returned %d", when, ret);

i = SPI_getbinval(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1, &isnull);

elog (NOTICE, "trigf (fired %s): there are %d tuples in ttest", when, i);

SPI_finish();

if (checknull)

{

i = SPI_getbinval(rettuple, tupdesc, 1, &isnull);

if (isnull)

rettuple = NULL;

}

return (rettuple);

}

----------------------------------------------------------------------------

Now, compile and

create table ttest (x int4);

create function trigf () returns opaque as

'...path_to_so' language 'c';

vac=> create trigger tbefore before insert or update or delete on ttest

for each row execute procedure trigf();

CREATE

vac=> create trigger tafter after insert or update or delete on ttest

for each row execute procedure trigf();

CREATE

vac=> insert into ttest values (null);

NOTICE:trigf (fired before): there are 0 tuples in ttest

INSERT 0 0

-- Insertion skipped and AFTER trigger is not fired

vac=> select * from ttest;

x

-

(0 rows)

vac=> insert into ttest values (1);

NOTICE:trigf (fired before): there are 0 tuples in ttest

NOTICE:trigf (fired after ): there are 1 tuples in ttest

^^^^^^^^

remember about visibility

INSERT 167793 1

vac=> select * from ttest;

x

-

1

(1 row)

vac=> insert into ttest select x * 2 from ttest;

NOTICE:trigf (fired before): there are 1 tuples in ttest

NOTICE:trigf (fired after ): there are 2 tuples in ttest

^^^^^^^^

remember about visibility

INSERT 167794 1

vac=> select * from ttest;

x

-

1

2

(2 rows)

vac=> update ttest set x = null where x = 2;

NOTICE:trigf (fired before): there are 2 tuples in ttest

UPDATE 0

vac=> update ttest set x = 4 where x = 2;

NOTICE:trigf (fired before): there are 2 tuples in ttest

NOTICE:trigf (fired after ): there are 2 tuples in ttest

UPDATE 1

vac=> select * from ttest;

x

-

1

4

(2 rows)

vac=> delete from ttest;

NOTICE:trigf (fired before): there are 2 tuples in ttest

NOTICE:trigf (fired after ): there are 1 tuples in ttest

NOTICE:trigf (fired before): there are 1 tuples in ttest

NOTICE:trigf (fired after ): there are 0 tuples in ttest

^^^^^^^^

remember about visibility

DELETE 2

vac=> select * from ttest;

x

-

(0 rows)