diff options
Diffstat (limited to '')
-rw-r--r-- | contrib/spi/Makefile | 28 | ||||
-rw-r--r-- | contrib/spi/autoinc--1.0.sql | 9 | ||||
-rw-r--r-- | contrib/spi/autoinc.c | 127 | ||||
-rw-r--r-- | contrib/spi/autoinc.control | 5 | ||||
-rw-r--r-- | contrib/spi/autoinc.example | 35 | ||||
-rw-r--r-- | contrib/spi/insert_username--1.0.sql | 9 | ||||
-rw-r--r-- | contrib/spi/insert_username.c | 92 | ||||
-rw-r--r-- | contrib/spi/insert_username.control | 5 | ||||
-rw-r--r-- | contrib/spi/insert_username.example | 20 | ||||
-rw-r--r-- | contrib/spi/moddatetime--1.0.sql | 9 | ||||
-rw-r--r-- | contrib/spi/moddatetime.c | 130 | ||||
-rw-r--r-- | contrib/spi/moddatetime.control | 5 | ||||
-rw-r--r-- | contrib/spi/moddatetime.example | 27 | ||||
-rw-r--r-- | contrib/spi/refint--1.0.sql | 14 | ||||
-rw-r--r-- | contrib/spi/refint.c | 656 | ||||
-rw-r--r-- | contrib/spi/refint.control | 5 | ||||
-rw-r--r-- | contrib/spi/refint.example | 82 |
17 files changed, 1258 insertions, 0 deletions
diff --git a/contrib/spi/Makefile b/contrib/spi/Makefile new file mode 100644 index 0000000..c9c34ff --- /dev/null +++ b/contrib/spi/Makefile @@ -0,0 +1,28 @@ +# contrib/spi/Makefile + +MODULES = autoinc insert_username moddatetime refint + +EXTENSION = autoinc insert_username moddatetime refint + +DATA = autoinc--1.0.sql \ + insert_username--1.0.sql \ + moddatetime--1.0.sql \ + refint--1.0.sql +PGFILEDESC = "spi - examples of using SPI and triggers" + +DOCS = $(addsuffix .example, $(MODULES)) + +# this is needed for the regression tests; +# comment out if you want a quieter refint package for other uses +PG_CPPFLAGS = -DREFINT_VERBOSE + +ifdef USE_PGXS +PG_CONFIG = pg_config +PGXS := $(shell $(PG_CONFIG) --pgxs) +include $(PGXS) +else +subdir = contrib/spi +top_builddir = ../.. +include $(top_builddir)/src/Makefile.global +include $(top_srcdir)/contrib/contrib-global.mk +endif diff --git a/contrib/spi/autoinc--1.0.sql b/contrib/spi/autoinc--1.0.sql new file mode 100644 index 0000000..22721e0 --- /dev/null +++ b/contrib/spi/autoinc--1.0.sql @@ -0,0 +1,9 @@ +/* contrib/spi/autoinc--1.0.sql */ + +-- complain if script is sourced in psql, rather than via CREATE EXTENSION +\echo Use "CREATE EXTENSION autoinc" to load this file. \quit + +CREATE FUNCTION autoinc() +RETURNS trigger +AS 'MODULE_PATHNAME' +LANGUAGE C; diff --git a/contrib/spi/autoinc.c b/contrib/spi/autoinc.c new file mode 100644 index 0000000..8bf7422 --- /dev/null +++ b/contrib/spi/autoinc.c @@ -0,0 +1,127 @@ +/* + * contrib/spi/autoinc.c + */ +#include "postgres.h" + +#include "access/htup_details.h" +#include "catalog/pg_type.h" +#include "commands/sequence.h" +#include "commands/trigger.h" +#include "executor/spi.h" +#include "utils/builtins.h" +#include "utils/rel.h" + +PG_MODULE_MAGIC; + +PG_FUNCTION_INFO_V1(autoinc); + +Datum +autoinc(PG_FUNCTION_ARGS) +{ + TriggerData *trigdata = (TriggerData *) fcinfo->context; + Trigger *trigger; /* to get trigger name */ + int nargs; /* # of arguments */ + int *chattrs; /* attnums of attributes to change */ + int chnattrs = 0; /* # of above */ + Datum *newvals; /* vals of above */ + bool *newnulls; /* null flags for above */ + char **args; /* arguments */ + char *relname; /* triggered relation name */ + Relation rel; /* triggered relation */ + HeapTuple rettuple = NULL; + TupleDesc tupdesc; /* tuple description */ + bool isnull; + int i; + + if (!CALLED_AS_TRIGGER(fcinfo)) + /* internal error */ + elog(ERROR, "not fired by trigger manager"); + if (!TRIGGER_FIRED_FOR_ROW(trigdata->tg_event)) + /* internal error */ + elog(ERROR, "must be fired for row"); + if (!TRIGGER_FIRED_BEFORE(trigdata->tg_event)) + /* internal error */ + elog(ERROR, "must be fired before event"); + + if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event)) + rettuple = trigdata->tg_trigtuple; + else if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event)) + rettuple = trigdata->tg_newtuple; + else + /* internal error */ + elog(ERROR, "cannot process DELETE events"); + + rel = trigdata->tg_relation; + relname = SPI_getrelname(rel); + + trigger = trigdata->tg_trigger; + + nargs = trigger->tgnargs; + if (nargs <= 0 || nargs % 2 != 0) + /* internal error */ + elog(ERROR, "autoinc (%s): even number gt 0 of arguments was expected", relname); + + args = trigger->tgargs; + tupdesc = rel->rd_att; + + chattrs = (int *) palloc(nargs / 2 * sizeof(int)); + newvals = (Datum *) palloc(nargs / 2 * sizeof(Datum)); + newnulls = (bool *) palloc(nargs / 2 * sizeof(bool)); + + for (i = 0; i < nargs;) + { + int attnum = SPI_fnumber(tupdesc, args[i]); + int32 val; + Datum seqname; + + if (attnum <= 0) + ereport(ERROR, + (errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION), + errmsg("\"%s\" has no attribute \"%s\"", + relname, args[i]))); + + if (SPI_gettypeid(tupdesc, attnum) != INT4OID) + ereport(ERROR, + (errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION), + errmsg("attribute \"%s\" of \"%s\" must be type INT4", + args[i], relname))); + + val = DatumGetInt32(SPI_getbinval(rettuple, tupdesc, attnum, &isnull)); + + if (!isnull && val != 0) + { + i += 2; + continue; + } + + i++; + chattrs[chnattrs] = attnum; + seqname = CStringGetTextDatum(args[i]); + newvals[chnattrs] = DirectFunctionCall1(nextval, seqname); + /* nextval now returns int64; coerce down to int32 */ + newvals[chnattrs] = Int32GetDatum((int32) DatumGetInt64(newvals[chnattrs])); + if (DatumGetInt32(newvals[chnattrs]) == 0) + { + newvals[chnattrs] = DirectFunctionCall1(nextval, seqname); + newvals[chnattrs] = Int32GetDatum((int32) DatumGetInt64(newvals[chnattrs])); + } + newnulls[chnattrs] = false; + pfree(DatumGetTextPP(seqname)); + chnattrs++; + i++; + } + + if (chnattrs > 0) + { + rettuple = heap_modify_tuple_by_cols(rettuple, tupdesc, + chnattrs, chattrs, + newvals, newnulls); + } + + pfree(relname); + pfree(chattrs); + pfree(newvals); + pfree(newnulls); + + return PointerGetDatum(rettuple); +} diff --git a/contrib/spi/autoinc.control b/contrib/spi/autoinc.control new file mode 100644 index 0000000..1d7a8e5 --- /dev/null +++ b/contrib/spi/autoinc.control @@ -0,0 +1,5 @@ +# autoinc extension +comment = 'functions for autoincrementing fields' +default_version = '1.0' +module_pathname = '$libdir/autoinc' +relocatable = true diff --git a/contrib/spi/autoinc.example b/contrib/spi/autoinc.example new file mode 100644 index 0000000..08880ce --- /dev/null +++ b/contrib/spi/autoinc.example @@ -0,0 +1,35 @@ +DROP SEQUENCE next_id; +DROP TABLE ids; + +CREATE SEQUENCE next_id START -2 MINVALUE -2; + +CREATE TABLE ids ( + id int4, + idesc text +); + +CREATE TRIGGER ids_nextid + BEFORE INSERT OR UPDATE ON ids + FOR EACH ROW + EXECUTE PROCEDURE autoinc (id, next_id); + +INSERT INTO ids VALUES (0, 'first (-2 ?)'); +INSERT INTO ids VALUES (null, 'second (-1 ?)'); +INSERT INTO ids(idesc) VALUES ('third (1 ?!)'); + +SELECT * FROM ids; + +UPDATE ids SET id = null, idesc = 'first: -2 --> 2' + WHERE idesc = 'first (-2 ?)'; +UPDATE ids SET id = 0, idesc = 'second: -1 --> 3' + WHERE id = -1; +UPDATE ids SET id = 4, idesc = 'third: 1 --> 4' + WHERE id = 1; + +SELECT * FROM ids; + +SELECT 'Wasn''t it 4 ?' as nextval, nextval ('next_id') as value; + +insert into ids (idesc) select textcat (idesc, '. Copy.') from ids; + +SELECT * FROM ids; diff --git a/contrib/spi/insert_username--1.0.sql b/contrib/spi/insert_username--1.0.sql new file mode 100644 index 0000000..0deb0bf --- /dev/null +++ b/contrib/spi/insert_username--1.0.sql @@ -0,0 +1,9 @@ +/* contrib/spi/insert_username--1.0.sql */ + +-- complain if script is sourced in psql, rather than via CREATE EXTENSION +\echo Use "CREATE EXTENSION insert_username" to load this file. \quit + +CREATE FUNCTION insert_username() +RETURNS trigger +AS 'MODULE_PATHNAME' +LANGUAGE C; diff --git a/contrib/spi/insert_username.c b/contrib/spi/insert_username.c new file mode 100644 index 0000000..a2e1747 --- /dev/null +++ b/contrib/spi/insert_username.c @@ -0,0 +1,92 @@ +/* + * contrib/spi/insert_username.c + * + * insert user name in response to a trigger + * usage: insert_username (column_name) + */ +#include "postgres.h" + +#include "access/htup_details.h" +#include "catalog/pg_type.h" +#include "commands/trigger.h" +#include "executor/spi.h" +#include "miscadmin.h" +#include "utils/builtins.h" +#include "utils/rel.h" + +PG_MODULE_MAGIC; + +PG_FUNCTION_INFO_V1(insert_username); + +Datum +insert_username(PG_FUNCTION_ARGS) +{ + TriggerData *trigdata = (TriggerData *) fcinfo->context; + Trigger *trigger; /* to get trigger name */ + int nargs; /* # of arguments */ + Datum newval; /* new value of column */ + bool newnull; /* null flag */ + char **args; /* arguments */ + char *relname; /* triggered relation name */ + Relation rel; /* triggered relation */ + HeapTuple rettuple = NULL; + TupleDesc tupdesc; /* tuple description */ + int attnum; + + /* sanity checks from autoinc.c */ + if (!CALLED_AS_TRIGGER(fcinfo)) + /* internal error */ + elog(ERROR, "insert_username: not fired by trigger manager"); + if (!TRIGGER_FIRED_FOR_ROW(trigdata->tg_event)) + /* internal error */ + elog(ERROR, "insert_username: must be fired for row"); + if (!TRIGGER_FIRED_BEFORE(trigdata->tg_event)) + /* internal error */ + elog(ERROR, "insert_username: must be fired before event"); + + if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event)) + rettuple = trigdata->tg_trigtuple; + else if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event)) + rettuple = trigdata->tg_newtuple; + else + /* internal error */ + elog(ERROR, "insert_username: cannot process DELETE events"); + + rel = trigdata->tg_relation; + relname = SPI_getrelname(rel); + + trigger = trigdata->tg_trigger; + + nargs = trigger->tgnargs; + if (nargs != 1) + /* internal error */ + elog(ERROR, "insert_username (%s): one argument was expected", relname); + + args = trigger->tgargs; + tupdesc = rel->rd_att; + + attnum = SPI_fnumber(tupdesc, args[0]); + + if (attnum <= 0) + ereport(ERROR, + (errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION), + errmsg("\"%s\" has no attribute \"%s\"", relname, args[0]))); + + if (SPI_gettypeid(tupdesc, attnum) != TEXTOID) + ereport(ERROR, + (errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION), + errmsg("attribute \"%s\" of \"%s\" must be type TEXT", + args[0], relname))); + + /* create fields containing name */ + newval = CStringGetTextDatum(GetUserNameFromId(GetUserId(), false)); + newnull = false; + + /* construct new tuple */ + rettuple = heap_modify_tuple_by_cols(rettuple, tupdesc, + 1, &attnum, &newval, &newnull); + + pfree(relname); + + return PointerGetDatum(rettuple); +} diff --git a/contrib/spi/insert_username.control b/contrib/spi/insert_username.control new file mode 100644 index 0000000..9d11064 --- /dev/null +++ b/contrib/spi/insert_username.control @@ -0,0 +1,5 @@ +# insert_username extension +comment = 'functions for tracking who changed a table' +default_version = '1.0' +module_pathname = '$libdir/insert_username' +relocatable = true diff --git a/contrib/spi/insert_username.example b/contrib/spi/insert_username.example new file mode 100644 index 0000000..2c1eeb0 --- /dev/null +++ b/contrib/spi/insert_username.example @@ -0,0 +1,20 @@ +DROP TABLE username_test; + +CREATE TABLE username_test ( + name text, + username text not null +); + +CREATE TRIGGER insert_usernames + BEFORE INSERT OR UPDATE ON username_test + FOR EACH ROW + EXECUTE PROCEDURE insert_username (username); + +INSERT INTO username_test VALUES ('nothing'); +INSERT INTO username_test VALUES ('null', null); +INSERT INTO username_test VALUES ('empty string', ''); +INSERT INTO username_test VALUES ('space', ' '); +INSERT INTO username_test VALUES ('tab', ' '); +INSERT INTO username_test VALUES ('name', 'name'); + +SELECT * FROM username_test; diff --git a/contrib/spi/moddatetime--1.0.sql b/contrib/spi/moddatetime--1.0.sql new file mode 100644 index 0000000..2ee61b8 --- /dev/null +++ b/contrib/spi/moddatetime--1.0.sql @@ -0,0 +1,9 @@ +/* contrib/spi/moddatetime--1.0.sql */ + +-- complain if script is sourced in psql, rather than via CREATE EXTENSION +\echo Use "CREATE EXTENSION moddatetime" to load this file. \quit + +CREATE FUNCTION moddatetime() +RETURNS trigger +AS 'MODULE_PATHNAME' +LANGUAGE C; diff --git a/contrib/spi/moddatetime.c b/contrib/spi/moddatetime.c new file mode 100644 index 0000000..3eb7004 --- /dev/null +++ b/contrib/spi/moddatetime.c @@ -0,0 +1,130 @@ +/* +moddatetime.c + +contrib/spi/moddatetime.c + +What is this? +It is a function to be called from a trigger for the purpose of updating +a modification datetime stamp in a record when that record is UPDATEd. + +Credits +This is 95%+ based on autoinc.c, which I used as a starting point as I do +not really know what I am doing. I also had help from +Jan Wieck <jwieck@debis.com> who told me about the timestamp_in("now") function. +OH, me, I'm Terry Mackintosh <terry@terrym.com> +*/ +#include "postgres.h" + +#include "access/htup_details.h" +#include "catalog/pg_type.h" +#include "commands/trigger.h" +#include "executor/spi.h" +#include "utils/builtins.h" +#include "utils/rel.h" + +PG_MODULE_MAGIC; + +PG_FUNCTION_INFO_V1(moddatetime); + +Datum +moddatetime(PG_FUNCTION_ARGS) +{ + TriggerData *trigdata = (TriggerData *) fcinfo->context; + Trigger *trigger; /* to get trigger name */ + int nargs; /* # of arguments */ + int attnum; /* positional number of field to change */ + Oid atttypid; /* type OID of field to change */ + Datum newdt; /* The current datetime. */ + bool newdtnull; /* null flag for it */ + char **args; /* arguments */ + char *relname; /* triggered relation name */ + Relation rel; /* triggered relation */ + HeapTuple rettuple = NULL; + TupleDesc tupdesc; /* tuple description */ + + if (!CALLED_AS_TRIGGER(fcinfo)) + /* internal error */ + elog(ERROR, "moddatetime: not fired by trigger manager"); + + if (!TRIGGER_FIRED_FOR_ROW(trigdata->tg_event)) + /* internal error */ + elog(ERROR, "moddatetime: must be fired for row"); + + if (!TRIGGER_FIRED_BEFORE(trigdata->tg_event)) + /* internal error */ + elog(ERROR, "moddatetime: must be fired before event"); + + if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event)) + /* internal error */ + elog(ERROR, "moddatetime: cannot process INSERT events"); + else if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event)) + rettuple = trigdata->tg_newtuple; + else + /* internal error */ + elog(ERROR, "moddatetime: cannot process DELETE events"); + + rel = trigdata->tg_relation; + relname = SPI_getrelname(rel); + + trigger = trigdata->tg_trigger; + + nargs = trigger->tgnargs; + + if (nargs != 1) + /* internal error */ + elog(ERROR, "moddatetime (%s): A single argument was expected", relname); + + args = trigger->tgargs; + /* must be the field layout? */ + tupdesc = rel->rd_att; + + /* + * This gets the position in the tuple of the field we want. args[0] being + * the name of the field to update, as passed in from the trigger. + */ + attnum = SPI_fnumber(tupdesc, args[0]); + + /* + * This is where we check to see if the field we are supposed to update + * even exists. + */ + if (attnum <= 0) + ereport(ERROR, + (errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION), + errmsg("\"%s\" has no attribute \"%s\"", + relname, args[0]))); + + /* + * Check the target field has an allowed type, and get the current + * datetime as a value of that type. + */ + atttypid = SPI_gettypeid(tupdesc, attnum); + if (atttypid == TIMESTAMPOID) + newdt = DirectFunctionCall3(timestamp_in, + CStringGetDatum("now"), + ObjectIdGetDatum(InvalidOid), + Int32GetDatum(-1)); + else if (atttypid == TIMESTAMPTZOID) + newdt = DirectFunctionCall3(timestamptz_in, + CStringGetDatum("now"), + ObjectIdGetDatum(InvalidOid), + Int32GetDatum(-1)); + else + { + ereport(ERROR, + (errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION), + errmsg("attribute \"%s\" of \"%s\" must be type TIMESTAMP or TIMESTAMPTZ", + args[0], relname))); + newdt = (Datum) 0; /* keep compiler quiet */ + } + newdtnull = false; + + /* Replace the attnum'th column with newdt */ + rettuple = heap_modify_tuple_by_cols(rettuple, tupdesc, + 1, &attnum, &newdt, &newdtnull); + + /* Clean up */ + pfree(relname); + + return PointerGetDatum(rettuple); +} diff --git a/contrib/spi/moddatetime.control b/contrib/spi/moddatetime.control new file mode 100644 index 0000000..93dfac5 --- /dev/null +++ b/contrib/spi/moddatetime.control @@ -0,0 +1,5 @@ +# moddatetime extension +comment = 'functions for tracking last modification time' +default_version = '1.0' +module_pathname = '$libdir/moddatetime' +relocatable = true diff --git a/contrib/spi/moddatetime.example b/contrib/spi/moddatetime.example new file mode 100644 index 0000000..65af388 --- /dev/null +++ b/contrib/spi/moddatetime.example @@ -0,0 +1,27 @@ +DROP TABLE mdt; + +CREATE TABLE mdt ( + id int4, + idesc text, + moddate timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL +); + +CREATE TRIGGER mdt_moddatetime + BEFORE UPDATE ON mdt + FOR EACH ROW + EXECUTE PROCEDURE moddatetime (moddate); + +INSERT INTO mdt VALUES (1, 'first'); +INSERT INTO mdt VALUES (2, 'second'); +INSERT INTO mdt VALUES (3, 'third'); + +SELECT * FROM mdt; + +UPDATE mdt SET id = 4 + WHERE id = 1; +UPDATE mdt SET id = 5 + WHERE id = 2; +UPDATE mdt SET id = 6 + WHERE id = 3; + +SELECT * FROM mdt; diff --git a/contrib/spi/refint--1.0.sql b/contrib/spi/refint--1.0.sql new file mode 100644 index 0000000..faf797c --- /dev/null +++ b/contrib/spi/refint--1.0.sql @@ -0,0 +1,14 @@ +/* contrib/spi/refint--1.0.sql */ + +-- complain if script is sourced in psql, rather than via CREATE EXTENSION +\echo Use "CREATE EXTENSION refint" to load this file. \quit + +CREATE FUNCTION check_primary_key() +RETURNS trigger +AS 'MODULE_PATHNAME' +LANGUAGE C; + +CREATE FUNCTION check_foreign_key() +RETURNS trigger +AS 'MODULE_PATHNAME' +LANGUAGE C; diff --git a/contrib/spi/refint.c b/contrib/spi/refint.c new file mode 100644 index 0000000..6fbfef2 --- /dev/null +++ b/contrib/spi/refint.c @@ -0,0 +1,656 @@ +/* + * contrib/spi/refint.c + * + * + * refint.c -- set of functions to define referential integrity + * constraints using general triggers. + */ +#include "postgres.h" + +#include <ctype.h> + +#include "commands/trigger.h" +#include "executor/spi.h" +#include "utils/builtins.h" +#include "utils/memutils.h" +#include "utils/rel.h" + +PG_MODULE_MAGIC; + +typedef struct +{ + char *ident; + int nplans; + SPIPlanPtr *splan; +} EPlan; + +static EPlan *FPlans = NULL; +static int nFPlans = 0; +static EPlan *PPlans = NULL; +static int nPPlans = 0; + +static EPlan *find_plan(char *ident, EPlan **eplan, int *nplans); + +/* + * check_primary_key () -- check that key in tuple being inserted/updated + * references existing tuple in "primary" table. + * Though it's called without args You have to specify referenced + * table/keys while creating trigger: key field names in triggered table, + * referenced table name, referenced key field names: + * EXECUTE PROCEDURE + * check_primary_key ('Fkey1', 'Fkey2', 'Ptable', 'Pkey1', 'Pkey2'). + */ + +PG_FUNCTION_INFO_V1(check_primary_key); + +Datum +check_primary_key(PG_FUNCTION_ARGS) +{ + TriggerData *trigdata = (TriggerData *) fcinfo->context; + Trigger *trigger; /* to get trigger name */ + int nargs; /* # of args specified in CREATE TRIGGER */ + char **args; /* arguments: column names and table name */ + int nkeys; /* # of key columns (= nargs / 2) */ + Datum *kvals; /* key values */ + char *relname; /* referenced relation name */ + Relation rel; /* triggered relation */ + HeapTuple tuple = NULL; /* tuple to return */ + TupleDesc tupdesc; /* tuple description */ + EPlan *plan; /* prepared plan */ + Oid *argtypes = NULL; /* key types to prepare execution plan */ + bool isnull; /* to know is some column NULL or not */ + char ident[2 * NAMEDATALEN]; /* to identify myself */ + int ret; + int i; + +#ifdef DEBUG_QUERY + elog(DEBUG4, "check_primary_key: Enter Function"); +#endif + + /* + * Some checks first... + */ + + /* Called by trigger manager ? */ + if (!CALLED_AS_TRIGGER(fcinfo)) + /* internal error */ + elog(ERROR, "check_primary_key: not fired by trigger manager"); + + /* Should be called for ROW trigger */ + if (!TRIGGER_FIRED_FOR_ROW(trigdata->tg_event)) + /* internal error */ + elog(ERROR, "check_primary_key: must be fired for row"); + + /* If INSERTion then must check Tuple to being inserted */ + if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event)) + tuple = trigdata->tg_trigtuple; + + /* Not should be called for DELETE */ + else if (TRIGGER_FIRED_BY_DELETE(trigdata->tg_event)) + /* internal error */ + elog(ERROR, "check_primary_key: cannot process DELETE events"); + + /* If UPDATE, then must check new Tuple, not old one */ + else + tuple = trigdata->tg_newtuple; + + trigger = trigdata->tg_trigger; + nargs = trigger->tgnargs; + args = trigger->tgargs; + + if (nargs % 2 != 1) /* odd number of arguments! */ + /* internal error */ + elog(ERROR, "check_primary_key: odd number of arguments should be specified"); + + nkeys = nargs / 2; + relname = args[nkeys]; + rel = trigdata->tg_relation; + tupdesc = rel->rd_att; + + /* Connect to SPI manager */ + if ((ret = SPI_connect()) < 0) + /* internal error */ + elog(ERROR, "check_primary_key: SPI_connect returned %d", ret); + + /* + * We use SPI plan preparation feature, so allocate space to place key + * values. + */ + kvals = (Datum *) palloc(nkeys * sizeof(Datum)); + + /* + * Construct ident string as TriggerName $ TriggeredRelationId and try to + * find prepared execution plan. + */ + snprintf(ident, sizeof(ident), "%s$%u", trigger->tgname, rel->rd_id); + plan = find_plan(ident, &PPlans, &nPPlans); + + /* if there is no plan then allocate argtypes for preparation */ + if (plan->nplans <= 0) + argtypes = (Oid *) palloc(nkeys * sizeof(Oid)); + + /* For each column in key ... */ + for (i = 0; i < nkeys; i++) + { + /* get index of column in tuple */ + int fnumber = SPI_fnumber(tupdesc, args[i]); + + /* Bad guys may give us un-existing column in CREATE TRIGGER */ + if (fnumber <= 0) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_COLUMN), + errmsg("there is no attribute \"%s\" in relation \"%s\"", + args[i], SPI_getrelname(rel)))); + + /* Well, get binary (in internal format) value of column */ + kvals[i] = SPI_getbinval(tuple, tupdesc, fnumber, &isnull); + + /* + * If it's NULL then nothing to do! DON'T FORGET call SPI_finish ()! + * DON'T FORGET return tuple! Executor inserts tuple you're returning! + * If you return NULL then nothing will be inserted! + */ + if (isnull) + { + SPI_finish(); + return PointerGetDatum(tuple); + } + + if (plan->nplans <= 0) /* Get typeId of column */ + argtypes[i] = SPI_gettypeid(tupdesc, fnumber); + } + + /* + * If we have to prepare plan ... + */ + if (plan->nplans <= 0) + { + SPIPlanPtr pplan; + char sql[8192]; + + /* + * Construct query: SELECT 1 FROM _referenced_relation_ WHERE Pkey1 = + * $1 [AND Pkey2 = $2 [...]] + */ + snprintf(sql, sizeof(sql), "select 1 from %s where ", relname); + for (i = 0; i < nkeys; i++) + { + snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "%s = $%d %s", + args[i + nkeys + 1], i + 1, (i < nkeys - 1) ? "and " : ""); + } + + /* Prepare plan for query */ + pplan = SPI_prepare(sql, nkeys, argtypes); + if (pplan == NULL) + /* internal error */ + elog(ERROR, "check_primary_key: SPI_prepare returned %s", SPI_result_code_string(SPI_result)); + + /* + * Remember that SPI_prepare places plan in current memory context - + * so, we have to save plan in TopMemoryContext for later use. + */ + if (SPI_keepplan(pplan)) + /* internal error */ + elog(ERROR, "check_primary_key: SPI_keepplan failed"); + plan->splan = (SPIPlanPtr *) MemoryContextAlloc(TopMemoryContext, + sizeof(SPIPlanPtr)); + *(plan->splan) = pplan; + plan->nplans = 1; + } + + /* + * Ok, execute prepared plan. + */ + ret = SPI_execp(*(plan->splan), kvals, NULL, 1); + /* we have no NULLs - so we pass ^^^^ here */ + + if (ret < 0) + /* internal error */ + elog(ERROR, "check_primary_key: SPI_execp returned %d", ret); + + /* + * If there are no tuples returned by SELECT then ... + */ + if (SPI_processed == 0) + ereport(ERROR, + (errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION), + errmsg("tuple references non-existent key"), + errdetail("Trigger \"%s\" found tuple referencing non-existent key in \"%s\".", trigger->tgname, relname))); + + SPI_finish(); + + return PointerGetDatum(tuple); +} + +/* + * check_foreign_key () -- check that key in tuple being deleted/updated + * is not referenced by tuples in "foreign" table(s). + * Though it's called without args You have to specify (while creating trigger): + * number of references, action to do if key referenced + * ('restrict' | 'setnull' | 'cascade'), key field names in triggered + * ("primary") table and referencing table(s)/keys: + * EXECUTE PROCEDURE + * check_foreign_key (2, 'restrict', 'Pkey1', 'Pkey2', + * 'Ftable1', 'Fkey11', 'Fkey12', 'Ftable2', 'Fkey21', 'Fkey22'). + */ + +PG_FUNCTION_INFO_V1(check_foreign_key); + +Datum +check_foreign_key(PG_FUNCTION_ARGS) +{ + TriggerData *trigdata = (TriggerData *) fcinfo->context; + Trigger *trigger; /* to get trigger name */ + int nargs; /* # of args specified in CREATE TRIGGER */ + char **args; /* arguments: as described above */ + char **args_temp; + int nrefs; /* number of references (== # of plans) */ + char action; /* 'R'estrict | 'S'etnull | 'C'ascade */ + int nkeys; /* # of key columns */ + Datum *kvals; /* key values */ + char *relname; /* referencing relation name */ + Relation rel; /* triggered relation */ + HeapTuple trigtuple = NULL; /* tuple to being changed */ + HeapTuple newtuple = NULL; /* tuple to return */ + TupleDesc tupdesc; /* tuple description */ + EPlan *plan; /* prepared plan(s) */ + Oid *argtypes = NULL; /* key types to prepare execution plan */ + bool isnull; /* to know is some column NULL or not */ + bool isequal = true; /* are keys in both tuples equal (in UPDATE) */ + char ident[2 * NAMEDATALEN]; /* to identify myself */ + int is_update = 0; + int ret; + int i, + r; + +#ifdef DEBUG_QUERY + elog(DEBUG4, "check_foreign_key: Enter Function"); +#endif + + /* + * Some checks first... + */ + + /* Called by trigger manager ? */ + if (!CALLED_AS_TRIGGER(fcinfo)) + /* internal error */ + elog(ERROR, "check_foreign_key: not fired by trigger manager"); + + /* Should be called for ROW trigger */ + if (!TRIGGER_FIRED_FOR_ROW(trigdata->tg_event)) + /* internal error */ + elog(ERROR, "check_foreign_key: must be fired for row"); + + /* Not should be called for INSERT */ + if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event)) + /* internal error */ + elog(ERROR, "check_foreign_key: cannot process INSERT events"); + + /* Have to check tg_trigtuple - tuple being deleted */ + trigtuple = trigdata->tg_trigtuple; + + /* + * But if this is UPDATE then we have to return tg_newtuple. Also, if key + * in tg_newtuple is the same as in tg_trigtuple then nothing to do. + */ + is_update = 0; + if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event)) + { + newtuple = trigdata->tg_newtuple; + is_update = 1; + } + trigger = trigdata->tg_trigger; + nargs = trigger->tgnargs; + args = trigger->tgargs; + + if (nargs < 5) /* nrefs, action, key, Relation, key - at + * least */ + /* internal error */ + elog(ERROR, "check_foreign_key: too short %d (< 5) list of arguments", nargs); + + nrefs = pg_strtoint32(args[0]); + if (nrefs < 1) + /* internal error */ + elog(ERROR, "check_foreign_key: %d (< 1) number of references specified", nrefs); + action = tolower((unsigned char) *(args[1])); + if (action != 'r' && action != 'c' && action != 's') + /* internal error */ + elog(ERROR, "check_foreign_key: invalid action %s", args[1]); + nargs -= 2; + args += 2; + nkeys = (nargs - nrefs) / (nrefs + 1); + if (nkeys <= 0 || nargs != (nrefs + nkeys * (nrefs + 1))) + /* internal error */ + elog(ERROR, "check_foreign_key: invalid number of arguments %d for %d references", + nargs + 2, nrefs); + + rel = trigdata->tg_relation; + tupdesc = rel->rd_att; + + /* Connect to SPI manager */ + if ((ret = SPI_connect()) < 0) + /* internal error */ + elog(ERROR, "check_foreign_key: SPI_connect returned %d", ret); + + /* + * We use SPI plan preparation feature, so allocate space to place key + * values. + */ + kvals = (Datum *) palloc(nkeys * sizeof(Datum)); + + /* + * Construct ident string as TriggerName $ TriggeredRelationId and try to + * find prepared execution plan(s). + */ + snprintf(ident, sizeof(ident), "%s$%u", trigger->tgname, rel->rd_id); + plan = find_plan(ident, &FPlans, &nFPlans); + + /* if there is no plan(s) then allocate argtypes for preparation */ + if (plan->nplans <= 0) + argtypes = (Oid *) palloc(nkeys * sizeof(Oid)); + + /* + * else - check that we have exactly nrefs plan(s) ready + */ + else if (plan->nplans != nrefs) + /* internal error */ + elog(ERROR, "%s: check_foreign_key: # of plans changed in meantime", + trigger->tgname); + + /* For each column in key ... */ + for (i = 0; i < nkeys; i++) + { + /* get index of column in tuple */ + int fnumber = SPI_fnumber(tupdesc, args[i]); + + /* Bad guys may give us un-existing column in CREATE TRIGGER */ + if (fnumber <= 0) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_COLUMN), + errmsg("there is no attribute \"%s\" in relation \"%s\"", + args[i], SPI_getrelname(rel)))); + + /* Well, get binary (in internal format) value of column */ + kvals[i] = SPI_getbinval(trigtuple, tupdesc, fnumber, &isnull); + + /* + * If it's NULL then nothing to do! DON'T FORGET call SPI_finish ()! + * DON'T FORGET return tuple! Executor inserts tuple you're returning! + * If you return NULL then nothing will be inserted! + */ + if (isnull) + { + SPI_finish(); + return PointerGetDatum((newtuple == NULL) ? trigtuple : newtuple); + } + + /* + * If UPDATE then get column value from new tuple being inserted and + * compare is this the same as old one. For the moment we use string + * presentation of values... + */ + if (newtuple != NULL) + { + char *oldval = SPI_getvalue(trigtuple, tupdesc, fnumber); + char *newval; + + /* this shouldn't happen! SPI_ERROR_NOOUTFUNC ? */ + if (oldval == NULL) + /* internal error */ + elog(ERROR, "check_foreign_key: SPI_getvalue returned %s", SPI_result_code_string(SPI_result)); + newval = SPI_getvalue(newtuple, tupdesc, fnumber); + if (newval == NULL || strcmp(oldval, newval) != 0) + isequal = false; + } + + if (plan->nplans <= 0) /* Get typeId of column */ + argtypes[i] = SPI_gettypeid(tupdesc, fnumber); + } + args_temp = args; + nargs -= nkeys; + args += nkeys; + + /* + * If we have to prepare plans ... + */ + if (plan->nplans <= 0) + { + SPIPlanPtr pplan; + char sql[8192]; + char **args2 = args; + + plan->splan = (SPIPlanPtr *) MemoryContextAlloc(TopMemoryContext, + nrefs * sizeof(SPIPlanPtr)); + + for (r = 0; r < nrefs; r++) + { + relname = args2[0]; + + /*--------- + * For 'R'estrict action we construct SELECT query: + * + * SELECT 1 + * FROM _referencing_relation_ + * WHERE Fkey1 = $1 [AND Fkey2 = $2 [...]] + * + * to check is tuple referenced or not. + *--------- + */ + if (action == 'r') + + snprintf(sql, sizeof(sql), "select 1 from %s where ", relname); + + /*--------- + * For 'C'ascade action we construct DELETE query + * + * DELETE + * FROM _referencing_relation_ + * WHERE Fkey1 = $1 [AND Fkey2 = $2 [...]] + * + * to delete all referencing tuples. + *--------- + */ + + /* + * Max : Cascade with UPDATE query i create update query that + * updates new key values in referenced tables + */ + + + else if (action == 'c') + { + if (is_update == 1) + { + int fn; + char *nv; + int k; + + snprintf(sql, sizeof(sql), "update %s set ", relname); + for (k = 1; k <= nkeys; k++) + { + int is_char_type = 0; + char *type; + + fn = SPI_fnumber(tupdesc, args_temp[k - 1]); + Assert(fn > 0); /* already checked above */ + nv = SPI_getvalue(newtuple, tupdesc, fn); + type = SPI_gettype(tupdesc, fn); + + if (strcmp(type, "text") == 0 || + strcmp(type, "varchar") == 0 || + strcmp(type, "char") == 0 || + strcmp(type, "bpchar") == 0 || + strcmp(type, "date") == 0 || + strcmp(type, "timestamp") == 0) + is_char_type = 1; +#ifdef DEBUG_QUERY + elog(DEBUG4, "check_foreign_key Debug value %s type %s %d", + nv, type, is_char_type); +#endif + + /* + * is_char_type =1 i set ' ' for define a new value + */ + snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), + " %s = %s%s%s %s ", + args2[k], (is_char_type > 0) ? "'" : "", + nv, (is_char_type > 0) ? "'" : "", (k < nkeys) ? ", " : ""); + } + strcat(sql, " where "); + + } + else + /* DELETE */ + snprintf(sql, sizeof(sql), "delete from %s where ", relname); + + } + + /* + * For 'S'etnull action we construct UPDATE query - UPDATE + * _referencing_relation_ SET Fkey1 null [, Fkey2 null [...]] + * WHERE Fkey1 = $1 [AND Fkey2 = $2 [...]] - to set key columns in + * all referencing tuples to NULL. + */ + else if (action == 's') + { + snprintf(sql, sizeof(sql), "update %s set ", relname); + for (i = 1; i <= nkeys; i++) + { + snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), + "%s = null%s", + args2[i], (i < nkeys) ? ", " : ""); + } + strcat(sql, " where "); + } + + /* Construct WHERE qual */ + for (i = 1; i <= nkeys; i++) + { + snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "%s = $%d %s", + args2[i], i, (i < nkeys) ? "and " : ""); + } + + /* Prepare plan for query */ + pplan = SPI_prepare(sql, nkeys, argtypes); + if (pplan == NULL) + /* internal error */ + elog(ERROR, "check_foreign_key: SPI_prepare returned %s", SPI_result_code_string(SPI_result)); + + /* + * Remember that SPI_prepare places plan in current memory context + * - so, we have to save plan in Top memory context for later use. + */ + if (SPI_keepplan(pplan)) + /* internal error */ + elog(ERROR, "check_foreign_key: SPI_keepplan failed"); + + plan->splan[r] = pplan; + + args2 += nkeys + 1; /* to the next relation */ + } + plan->nplans = nrefs; +#ifdef DEBUG_QUERY + elog(DEBUG4, "check_foreign_key Debug Query is : %s ", sql); +#endif + } + + /* + * If UPDATE and key is not changed ... + */ + if (newtuple != NULL && isequal) + { + SPI_finish(); + return PointerGetDatum(newtuple); + } + + /* + * Ok, execute prepared plan(s). + */ + for (r = 0; r < nrefs; r++) + { + /* + * For 'R'estrict we may to execute plan for one tuple only, for other + * actions - for all tuples. + */ + int tcount = (action == 'r') ? 1 : 0; + + relname = args[0]; + + snprintf(ident, sizeof(ident), "%s$%u", trigger->tgname, rel->rd_id); + plan = find_plan(ident, &FPlans, &nFPlans); + ret = SPI_execp(plan->splan[r], kvals, NULL, tcount); + /* we have no NULLs - so we pass ^^^^ here */ + + if (ret < 0) + ereport(ERROR, + (errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION), + errmsg("SPI_execp returned %d", ret))); + + /* If action is 'R'estrict ... */ + if (action == 'r') + { + /* If there is tuple returned by SELECT then ... */ + if (SPI_processed > 0) + ereport(ERROR, + (errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION), + errmsg("\"%s\": tuple is referenced in \"%s\"", + trigger->tgname, relname))); + } + else + { +#ifdef REFINT_VERBOSE + elog(NOTICE, "%s: " UINT64_FORMAT " tuple(s) of %s are %s", + trigger->tgname, SPI_processed, relname, + (action == 'c') ? "deleted" : "set to null"); +#endif + } + args += nkeys + 1; /* to the next relation */ + } + + SPI_finish(); + + return PointerGetDatum((newtuple == NULL) ? trigtuple : newtuple); +} + +static EPlan * +find_plan(char *ident, EPlan **eplan, int *nplans) +{ + EPlan *newp; + int i; + MemoryContext oldcontext; + + /* + * All allocations done for the plans need to happen in a session-safe + * context. + */ + oldcontext = MemoryContextSwitchTo(TopMemoryContext); + + if (*nplans > 0) + { + for (i = 0; i < *nplans; i++) + { + if (strcmp((*eplan)[i].ident, ident) == 0) + break; + } + if (i != *nplans) + { + MemoryContextSwitchTo(oldcontext); + return (*eplan + i); + } + *eplan = (EPlan *) repalloc(*eplan, (i + 1) * sizeof(EPlan)); + newp = *eplan + i; + } + else + { + newp = *eplan = (EPlan *) palloc(sizeof(EPlan)); + (*nplans) = i = 0; + } + + newp->ident = pstrdup(ident); + newp->nplans = 0; + newp->splan = NULL; + (*nplans)++; + + MemoryContextSwitchTo(oldcontext); + return newp; +} diff --git a/contrib/spi/refint.control b/contrib/spi/refint.control new file mode 100644 index 0000000..cbede45 --- /dev/null +++ b/contrib/spi/refint.control @@ -0,0 +1,5 @@ +# refint extension +comment = 'functions for implementing referential integrity (obsolete)' +default_version = '1.0' +module_pathname = '$libdir/refint' +relocatable = true diff --git a/contrib/spi/refint.example b/contrib/spi/refint.example new file mode 100644 index 0000000..299166d --- /dev/null +++ b/contrib/spi/refint.example @@ -0,0 +1,82 @@ +--Column ID of table A is primary key: + +CREATE TABLE A ( + ID int4 not null +); +CREATE UNIQUE INDEX AI ON A (ID); + +--Columns REFB of table B and REFC of C are foreign keys referencing ID of A: + +CREATE TABLE B ( + REFB int4 +); +CREATE INDEX BI ON B (REFB); + +CREATE TABLE C ( + REFC int4 +); +CREATE INDEX CI ON C (REFC); + +--Trigger for table A: + +CREATE TRIGGER AT BEFORE DELETE OR UPDATE ON A FOR EACH ROW +EXECUTE PROCEDURE +check_foreign_key (2, 'cascade', 'ID', 'B', 'REFB', 'C', 'REFC'); +/* +2 - means that check must be performed for foreign keys of 2 tables. +cascade - defines that corresponding keys must be deleted. +ID - name of primary key column in triggered table (A). You may + use as many columns as you need. +B - name of (first) table with foreign keys. +REFB - name of foreign key column in this table. You may use as many + columns as you need, but number of key columns in referenced + table (A) must be the same. +C - name of second table with foreign keys. +REFC - name of foreign key column in this table. +*/ + +--Trigger for table B: + +CREATE TRIGGER BT BEFORE INSERT OR UPDATE ON B FOR EACH ROW +EXECUTE PROCEDURE +check_primary_key ('REFB', 'A', 'ID'); + +/* +REFB - name of foreign key column in triggered (B) table. You may use as + many columns as you need, but number of key columns in referenced + table must be the same. +A - referenced table name. +ID - name of primary key column in referenced table. +*/ + +--Trigger for table C: + +CREATE TRIGGER CT BEFORE INSERT OR UPDATE ON C FOR EACH ROW +EXECUTE PROCEDURE +check_primary_key ('REFC', 'A', 'ID'); + +-- Now try + +INSERT INTO A VALUES (10); +INSERT INTO A VALUES (20); +INSERT INTO A VALUES (30); +INSERT INTO A VALUES (40); +INSERT INTO A VALUES (50); + +INSERT INTO B VALUES (1); -- invalid reference +INSERT INTO B VALUES (10); +INSERT INTO B VALUES (30); +INSERT INTO B VALUES (30); + +INSERT INTO C VALUES (11); -- invalid reference +INSERT INTO C VALUES (20); +INSERT INTO C VALUES (20); +INSERT INTO C VALUES (30); + +DELETE FROM A WHERE ID = 10; +DELETE FROM A WHERE ID = 20; +DELETE FROM A WHERE ID = 30; + +SELECT * FROM A; +SELECT * FROM B; +SELECT * FROM C; |