| Документация по PostgreSQL 9.4.1 | |||
|---|---|---|---|
| Пред. | Уровень выше | Глава 36. Триггеры | След. |
36.4. Полный пример триггера
Вот очень простой пример триггерной функции, написанной на C. (Примеры триггеров для процедурных языков могут быть найдены в документации на процедурные языки.)
Функция trigf сообщает количество строк в таблице ttest и пропускает операцию для строки при попытке вставить пустое значение в столбец x. (Таким образом, триггер действует как ограничение NOT NULL, но не прерывает транзакцию.)
Вначале определение таблицы:
CREATE TABLE ttest (
x integer
);Теперь исходный код триггерной функции:
#include "postgres.h"
#include "executor/spi.h" /* это нужно для работы с SPI */
#include "commands/trigger.h" /* ... с триггерами ... */
#include "utils/rel.h" /* ... и с таблицами */
#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif
extern Datum trigf(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(trigf);
Datum
trigf(PG_FUNCTION_ARGS)
{
TriggerData *trigdata = (TriggerData *) fcinfo->context;
TupleDesc tupdesc;
HeapTuple rettuple;
char *when;
bool checknull = false;
bool isnull;
int ret, i;
/* Убедимся, что функция вызвана триггером */
if (!CALLED_AS_TRIGGER(fcinfo))
elog(ERROR, "trigf: not called by trigger manager");
/* Строка, которую будем возвращать */
if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
rettuple = trigdata->tg_newtuple;
else
rettuple = trigdata->tg_trigtuple;
/* Проверяем на пустые значения */
if (!TRIGGER_FIRED_BY_DELETE(trigdata->tg_event)
&& TRIGGER_FIRED_BEFORE(trigdata->tg_event))
checknull = true;
if (TRIGGER_FIRED_BEFORE(trigdata->tg_event))
when = "before";
else
when = "after ";
tupdesc = trigdata->tg_relation->rd_att;
/* Подключаемся к менеджеру SPI */
if ((ret = SPI_connect()) < 0)
elog(ERROR, "trigf (сработал %s): SPI_connect вернула %d", when, ret);
/* Получаем число строк в таблице */
ret = SPI_exec("SELECT count(*) FROM ttest", 0);
if (ret < 0)
elog(ERROR, "trigf (сработал %s): SPI_exec вернула %d", when, ret);
/* count(*) возвращает int8, требуется преобразование */
i = DatumGetInt64(SPI_getbinval(SPI_tuptable->vals[0],
SPI_tuptable->tupdesc,
1,
&isnull));
elog (INFO, "trigf (сработал %s): в таблице ttest %d строк", when, i);
SPI_finish();
if (checknull)
{
SPI_getbinval(rettuple, tupdesc, 1, &isnull);
if (isnull)
rettuple = NULL;
}
return PointerGetDatum(rettuple);
}После компиляции исходного кода (см. Подраздел 35.9.6) объявляем функцию и триггеры:
CREATE FUNCTION trigf() RETURNS trigger
AS 'filename'
LANGUAGE C;
CREATE TRIGGER tbefore BEFORE INSERT OR UPDATE OR DELETE ON ttest
FOR EACH ROW EXECUTE PROCEDURE trigf();
CREATE TRIGGER tafter AFTER INSERT OR UPDATE OR DELETE ON ttest
FOR EACH ROW EXECUTE PROCEDURE trigf();Теперь можно проверить работу триггера:
=> INSERT INTO ttest VALUES (NULL);
INFO: trigf (сработал before): в таблице ttest 0 строк
INSERT 0 0
-- Вставка записи пропущена (NULL значение), поэтому AFTER триггер не сработал
=> SELECT * FROM ttest;
x
---
(0 rows)
=> INSERT INTO ttest VALUES (1);
INFO: trigf (сработал before): в таблице ttest 0 строк
INFO: trigf (сработал after ): в таблице ttest 1 строк
^^^^^^^
вспомним, что говорили о видимости
INSERT 167793 1
vac=> SELECT * FROM ttest;
x
---
1
(1 row)
=> INSERT INTO ttest SELECT x * 2 FROM ttest;
INFO: trigf (сработал before): в таблице ttest 1 строк
INFO: trigf (сработал after ): в таблице ttest 2 строк
^^^^^^^
вспомним, что говорили о видимости
INSERT 167794 1
=> SELECT * FROM ttest;
x
---
1
2
(2 rows)
=> UPDATE ttest SET x = NULL WHERE x = 2;
INFO: trigf (сработал before): в таблице ttest 2 строк
UPDATE 0
=> UPDATE ttest SET x = 4 WHERE x = 2;
INFO: trigf (сработал before): в таблице ttest 2 строк
INFO: trigf (сработал after ): в таблице ttest 2 строк
UPDATE 1
vac=> SELECT * FROM ttest;
x
---
1
4
(2 rows)
=> DELETE FROM ttest;
INFO: trigf (сработал before): в таблице ttest 2 строк
INFO: trigf (сработал before): в таблице ttest 1 строк
INFO: trigf (сработал after ): в таблице ttest 0 строк
INFO: trigf (сработал after ): в таблице ttest 0 строк
^^^^^^^
вспомним, что говорили о видимости
DELETE 2
=> SELECT * FROM ttest;
x
---
(0 rows)Более сложные примеры можно найти в src/test/regress/regress.c и в spi.
| Пред. | Начало | След. |
| Триггерные функции на языке C | Уровень выше | Триггеры событий |
| PostgreSQL 9.4.1 Documentation | |||
|---|---|---|---|
| Prev | Up | Chapter 36. Triggers | Next |
36.4. A Complete Trigger Example
Here is a very simple example of a trigger function written in C. (Examples of triggers written in procedural languages can be found in the documentation of the procedural languages.)
The function trigf reports the number of rows in the table ttest and skips the actual operation if the command attempts to insert a null value into the column x. (So the trigger acts as a not-null constraint but doesn't abort the transaction.)
First, the table definition:
CREATE TABLE ttest (
x integer
);This is the source code of the trigger function:
#include "postgres.h"
#include "executor/spi.h" /* this is what you need to work with SPI */
#include "commands/trigger.h" /* ... triggers ... */
#include "utils/rel.h" /* ... and relations */
#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif
extern Datum trigf(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(trigf);
Datum
trigf(PG_FUNCTION_ARGS)
{
TriggerData *trigdata = (TriggerData *) fcinfo->context;
TupleDesc tupdesc;
HeapTuple rettuple;
char *when;
bool checknull = false;
bool isnull;
int ret, i;
/* make sure it's called as a trigger at all */
if (!CALLED_AS_TRIGGER(fcinfo))
elog(ERROR, "trigf: not called by trigger manager");
/* tuple to return to executor */
if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
rettuple = trigdata->tg_newtuple;
else
rettuple = trigdata->tg_trigtuple;
/* check for null values */
if (!TRIGGER_FIRED_BY_DELETE(trigdata->tg_event)
&& TRIGGER_FIRED_BEFORE(trigdata->tg_event))
checknull = true;
if (TRIGGER_FIRED_BEFORE(trigdata->tg_event))
when = "before";
else
when = "after ";
tupdesc = trigdata->tg_relation->rd_att;
/* connect to SPI manager */
if ((ret = SPI_connect()) < 0)
elog(ERROR, "trigf (fired %s): SPI_connect returned %d", when, ret);
/* get number of rows in table */
ret = SPI_exec("SELECT count(*) FROM ttest", 0);
if (ret < 0)
elog(ERROR, "trigf (fired %s): SPI_exec returned %d", when, ret);
/* count(*) returns int8, so be careful to convert */
i = DatumGetInt64(SPI_getbinval(SPI_tuptable->vals[0],
SPI_tuptable->tupdesc,
1,
&isnull));
elog (INFO, "trigf (fired %s): there are %d rows in ttest", when, i);
SPI_finish();
if (checknull)
{
SPI_getbinval(rettuple, tupdesc, 1, &isnull);
if (isnull)
rettuple = NULL;
}
return PointerGetDatum(rettuple);
}After you have compiled the source code (see Section 35.9.6), declare the function and the triggers:
CREATE FUNCTION trigf() RETURNS trigger
AS 'filename'
LANGUAGE C;
CREATE TRIGGER tbefore BEFORE INSERT OR UPDATE OR DELETE ON ttest
FOR EACH ROW EXECUTE PROCEDURE trigf();
CREATE TRIGGER tafter AFTER INSERT OR UPDATE OR DELETE ON ttest
FOR EACH ROW EXECUTE PROCEDURE trigf();Now you can test the operation of the trigger:
=> INSERT INTO ttest VALUES (NULL);
INFO: trigf (fired before): there are 0 rows in ttest
INSERT 0 0
-- Insertion skipped and AFTER trigger is not fired
=> SELECT * FROM ttest;
x
---
(0 rows)
=> INSERT INTO ttest VALUES (1);
INFO: trigf (fired before): there are 0 rows in ttest
INFO: trigf (fired after ): there are 1 rows in ttest
^^^^^^^^
remember what we said about visibility.
INSERT 167793 1
vac=> SELECT * FROM ttest;
x
---
1
(1 row)
=> INSERT INTO ttest SELECT x * 2 FROM ttest;
INFO: trigf (fired before): there are 1 rows in ttest
INFO: trigf (fired after ): there are 2 rows in ttest
^^^^^^
remember what we said about visibility.
INSERT 167794 1
=> SELECT * FROM ttest;
x
---
1
2
(2 rows)
=> UPDATE ttest SET x = NULL WHERE x = 2;
INFO: trigf (fired before): there are 2 rows in ttest
UPDATE 0
=> UPDATE ttest SET x = 4 WHERE x = 2;
INFO: trigf (fired before): there are 2 rows in ttest
INFO: trigf (fired after ): there are 2 rows in ttest
UPDATE 1
vac=> SELECT * FROM ttest;
x
---
1
4
(2 rows)
=> DELETE FROM ttest;
INFO: trigf (fired before): there are 2 rows in ttest
INFO: trigf (fired before): there are 1 rows in ttest
INFO: trigf (fired after ): there are 0 rows in ttest
INFO: trigf (fired after ): there are 0 rows in ttest
^^^^^^
remember what we said about visibility.
DELETE 2
=> SELECT * FROM ttest;
x
---
(0 rows)There are more complex examples in src/test/regress/regress.c and in spi.