Am 02.11.2010 23:09, schrieb Dimitri Fontaine: > So I guess that you need to modify very little code to get the trigger > to work for both types.
Please find the patch attached. It's against 8.4.5.
Bye...
Dirk
diff -u spi.old/moddatetime.c spi/moddatetime.c
--- spi.old/moddatetime.c 2010-10-01 15:35:31.000000000 +0200
+++ spi/moddatetime.c 2010-11-03 20:08:38.030772830 +0100
@@ -22,6 +22,7 @@
PG_MODULE_MAGIC;
extern Datum moddatetime(PG_FUNCTION_ARGS);
+extern Datum moddatetimetz(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(moddatetime);
@@ -111,7 +112,7 @@
/* 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
+ NOTE that attnum and newdt are not arrays, but then a 1 element array
is not an array any more then they are. Thus, they can be considered a
one element array.
*/
@@ -123,6 +124,111 @@
relname, SPI_result);
/* Clean up */
+ pfree(relname);
+
+ return PointerGetDatum(rettuple);
+}
+
+PG_FUNCTION_INFO_V1(moddatetimetz);
+
+Datum
+moddatetimetz(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 */
+ 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 (!CALLED_AS_TRIGGER(fcinfo))
+ /* internal error */
+ elog(ERROR, "moddatetimetz: not fired by trigger manager");
+
+ if (TRIGGER_FIRED_FOR_STATEMENT(trigdata->tg_event))
+ /* internal error */
+ elog(ERROR, "moddatetimetz: cannot process STATEMENT events");
+
+ if (TRIGGER_FIRED_AFTER(trigdata->tg_event))
+ /* internal error */
+ elog(ERROR, "moddatetimetz: must be fired before event");
+
+ if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event))
+ /* internal error */
+ elog(ERROR, "moddatetimetz: must be fired before event");
+ else if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
+ rettuple = trigdata->tg_newtuple;
+ else
+ /* internal error */
+ elog(ERROR, "moddatetimetz: 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, "moddatetimetz (%s): A single argument was expected", relname);
+
+ args = trigger->tgargs;
+ /* must be the field layout? */
+ tupdesc = rel->rd_att;
+
+ /* Get the current datetime. */
+ newdt = DirectFunctionCall3(timestamptz_in,
+ CStringGetDatum("now"),
+ ObjectIdGetDatum(InvalidOid),
+ Int32GetDatum(-1));
+
+ /*
+ * 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 were we check to see if the field we are supposed to update
+ * even exits. The above function must return -1 if name not found?
+ */
+ if (attnum < 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION),
+ errmsg("\"%s\" has no attribute \"%s\"",
+ relname, args[0])));
+
+ /*
+ * OK, this is where we make sure the timestamp field that we are
+ * modifying is really a timestamptz field. Hay, error checking, what a
+ * novel idea !-)
+ */
+ if (SPI_gettypeid(tupdesc, attnum) != TIMESTAMPTZOID)
+ ereport(ERROR,
+ (errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION),
+ errmsg("attribute \"%s\" of \"%s\" must be type TIMESTAMP WITH TIME ZONE",
+ args[0], relname)));
+
+/* 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 element 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)
+ /* internal error */
+ elog(ERROR, "moddatetimetz (%s): %d returned by SPI_modifytuple",
+ relname, SPI_result);
+
+/* Clean up */
pfree(relname);
return PointerGetDatum(rettuple);
diff -u spi.old/moddatetime.sql.in spi/moddatetime.sql.in
--- spi.old/moddatetime.sql.in 2010-10-01 15:35:31.000000000 +0200
+++ spi/moddatetime.sql.in 2010-11-03 19:41:51.806037512 +0100
@@ -7,3 +7,8 @@
RETURNS trigger
AS 'MODULE_PATHNAME'
LANGUAGE C;
+
+CREATE OR REPLACE FUNCTION moddatetimetz()
+RETURNS trigger
+AS 'MODULE_PATHNAME'
+LANGUAGE C;
signature.asc
Description: OpenPGP digital signature
