PostgreSQL 8.0.0 �����ĵ���PostgreSQL �й� ������ | ||||
---|---|---|---|---|
Prev | Fast Backward | Chapter 32. ������ | Fast Forward | Next |
������һ���� C д�ķdz��Ĵ�����ʹ�õ����ӡ� ����trigf����� ttest ���������� �������������ͼ�ѿ�ֵ���뵽�ֶ� x �Ҳ����˵ ������Ϊһ���ǿ�Լ�������˳������Լ����ʱ�Թ�������
���ȣ����壺
CREATE TABLE ttest ( x integer );
�����Ǵ�����������Դ���룺
#include "postgres.h" #include "executor/spi.h" /* ����SPI��ʱ��Ҫ�õ�ͷ�ļ� */ #include "commands/trigger.h" /* �ô�����ʱҪ�õ�ͷ�ļ� */ 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 fired 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(INFO, "trigf (fired %s): SPI_connect returned %d", when, ret); /* ��ȡ��ϵ�е������� */ ret = SPI_exec("SELECT count(*) FROM ttest", 0); if (ret < 0) elog(NOTICE, "trigf (fired %s): SPI_exec returned %d", when, ret); /* count(*) ���� int8������ҪС��ת�� */ i = (int) DatumGetInt64(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) { (void) SPI_getbinval(rettuple, tupdesc, 1, &isnull); if (isnull) rettuple = NULL; } return PointerGetDatum(rettuple); }
������Դ���������������������������
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();
��������Բ��Դ������IJ�����
vac=> INSERT INTO ttest VALUES (NULL); INFO:trigf (fired before): there are 0 tuples in ttest INSERT 0 0 -- ���뱻���ԣ�AFTER ������û�д��� vac=> SELECT * FROM ttest; x --- (0 rows) vac=> 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 ^^^^^^^^ ����һ�����ǽ����йؿ����ԵĶ����� INSERT 167793 1 vac=> SELECT * FROM ttest; x --- 1 (1 row) vac=> 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 ^^^^^^^^ ���ǵ����ǽ����Ĺ��ڿ����Ե�ԭ���� INSERT 167794 1 vac=> SELECT * FROM ttest; x --- 1 2 (2 rows) vac=> UPDATE ttest SET x = NULL where x = 2; INFO: trigf (fired before): there are 2 rows in ttest UPDATE 0 vac=> 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) vac=> DELETE FROM ttest; INFO: trigf (fired before): there are 2 rows in ttest INFO: trigf (fired after ): there are 1 rows in ttest INFO: trigf (fired before): there are 1 rows in ttest INFO: trigf (fired after ): there are 0 rows in ttest ^^^^^^^^ ���ǵ����ǽ����Ĺ��ڿ����Ե�ԭ���� DELETE 2 vac=> SELECT * FROM ttest; x --- (0 rows)
�� src/test/regress/regress.c �� contrib/spi �ﻹ�и����ӵ����ӡ�