From 14f9dde3795fab85a907426265a6e0f793be1482 Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Sat, 12 Dec 1998 20:20:49 +0000 Subject: [PATCH] New moddatetime contrib from Terry Mackintosh. --- contrib/spi/Makefile | 2 +- contrib/spi/moddatetime.c | 106 ++++++++++++++++++++++++++++++++ contrib/spi/moddatetime.example | 27 ++++++++ contrib/spi/moddatetime.source | 6 ++ 4 files changed, 140 insertions(+), 1 deletion(-) create mode 100644 contrib/spi/moddatetime.c create mode 100644 contrib/spi/moddatetime.example create mode 100644 contrib/spi/moddatetime.source diff --git a/contrib/spi/Makefile b/contrib/spi/Makefile index 6bb634aa77..c9c9ff8d48 100644 --- a/contrib/spi/Makefile +++ b/contrib/spi/Makefile @@ -12,7 +12,7 @@ CFLAGS+= -DREFINT_VERBOSE endif TARGETS= refint$(DLSUFFIX) refint.sql timetravel$(DLSUFFIX) timetravel.sql \ - autoinc$(DLSUFFIX) autoinc.sql \ + autoinc$(DLSUFFIX) autoinc.sql moddatetime$(DLSUFFIX) moddatetime.sql \ insert_username$(DLSUFFIX) insert_username.sql CLEANFILES+= $(TARGETS) diff --git a/contrib/spi/moddatetime.c b/contrib/spi/moddatetime.c new file mode 100644 index 0000000000..a6ef3b2795 --- /dev/null +++ b/contrib/spi/moddatetime.c @@ -0,0 +1,106 @@ +/* +moddatetime.c + +What is this? +It is a function to be called from a trigger for the perpose 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 who told me about the datetime_in("now") function. +OH, me, I'm Terry Mackintosh +*/ + +#include "executor/spi.h" /* this is what you need to work with SPI */ +#include "commands/trigger.h" /* -"- and triggers */ + +HeapTuple moddatetime(void); + +HeapTuple moddatetime() +{ + Trigger *trigger; /* to get trigger name */ + int nargs; /* # of arguments */ + int attnum; /* positional number of field to change */ + Datum newdt; /* The current datetime. */ + char **args; /* arguments */ + char *relname; /* triggered relation name */ + Relation rel; /* triggered relation */ + HeapTuple rettuple = NULL; + TupleDesc tupdesc; /* tuple description */ + + if (!CurrentTriggerData) + elog(ERROR, "moddatetime: triggers are not initialized."); + + if (TRIGGER_FIRED_FOR_STATEMENT(CurrentTriggerData->tg_event)) + elog(ERROR, "moddatetime: can't process STATEMENT events."); + + if (TRIGGER_FIRED_AFTER(CurrentTriggerData->tg_event)) + elog(ERROR, "moddatetime: must be fired before event."); + + if (TRIGGER_FIRED_BY_INSERT(CurrentTriggerData->tg_event)) + elog(ERROR, "moddatetime: must be fired before event."); + else if (TRIGGER_FIRED_BY_UPDATE(CurrentTriggerData->tg_event)) + rettuple = CurrentTriggerData->tg_newtuple; + else + elog(ERROR, "moddatetime: can't process DELETE events."); + + rel = CurrentTriggerData->tg_relation; + relname = SPI_getrelname(rel); + + trigger = CurrentTriggerData->tg_trigger; + + nargs = trigger->tgnargs; + + if (nargs != 1) + elog(ERROR, "moddatetime (%s): A single argument was expected.", relname); + + args = trigger->tgargs; + /* must be the field layout? */ + tupdesc = rel->rd_att; + + /* Why do this? */ + CurrentTriggerData = NULL; + + /* Get the current datetime. */ + newdt = datetime_in("now"); + + /* This gets the position in the turple 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 were we check to see if the feild we are suppost to update even + exits. The above function must return -1 if name not found? + */ + if (attnum < 0) + elog(ERROR, "moddatetime (%s): there is no attribute %s", relname, + args[0]); + + /* OK, this is where we make sure the datetime field that we are + modifying is really a datetime field. + Hay, error checking, what a novel idea !-) + */ + if (SPI_gettypeid(tupdesc, attnum) != DATETIMEOID ) + elog(ERROR, "moddatetime (%s): attribute %s must be of DATETIME type", + relname, args[0]); + +/* 1 is the number of items in the arrays attnum and newdt. + attnum is the positional number of the field to be updated. + newdt is the new datetime stamp. + NOTE that attnum and newdt are not arrays, but then a 1 ellement array + is not an array any more then they are. Thus, they can be considered a + one element array. +*/ + rettuple = SPI_modifytuple(rel, rettuple, 1, &attnum, &newdt, NULL); + + if (rettuple == NULL) + elog(ERROR, "moddatetime (%s): %d returned by SPI_modifytuple", + relname, SPI_result); + +/* Clean up */ + pfree(relname); + + return (rettuple); +} diff --git a/contrib/spi/moddatetime.example b/contrib/spi/moddatetime.example new file mode 100644 index 0000000000..25a54e3a33 --- /dev/null +++ b/contrib/spi/moddatetime.example @@ -0,0 +1,27 @@ +DROP TABLE mdt; + +CREATE TABLE mdt ( + id int4, + idesc text, + moddate datetime DEFAULT datetime(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/moddatetime.source b/contrib/spi/moddatetime.source new file mode 100644 index 0000000000..26906a3f33 --- /dev/null +++ b/contrib/spi/moddatetime.source @@ -0,0 +1,6 @@ +DROP FUNCTION moddatetime(); + +CREATE FUNCTION moddatetime() + RETURNS opaque + AS '_OBJWD_/moddatetime_DLSUFFIX_' + LANGUAGE 'c'; -- 2.40.0