Andrew Dunstan wrote:
Tom Lane wrote:
Magnus Hagander <[EMAIL PROTECTED]> writes:
In that case, why not put the trigger in core so people can use it
easily?
One advantage of making it a contrib module is that discussing how/when
to use it would fit more easily into the structure of the
documentation. There is no place in our docs that a "standard trigger"
would fit without seeming like a wart; but a contrib module can document
itself pretty much however it wants.
I was thinking a new section on 'trigger functions' of the functions
and operators chapter, linked from the 'create trigger' page. That
doesn't seem like too much of a wart.
There seems to be a preponderance of opinion for doing this as a
builtin. Here is a patch that does it that way, along with docs and
regression test.
cheers
andrew
Index: doc/src/sgml/func.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/func.sgml,v
retrieving revision 1.450
diff -c -r1.450 func.sgml
*** doc/src/sgml/func.sgml 14 Oct 2008 17:12:32 -0000 1.450
--- doc/src/sgml/func.sgml 22 Oct 2008 18:35:51 -0000
***************
*** 12817,12820 ****
--- 12817,12845 ----
</sect1>
+ <sect1 id="functions-trigger">
+ <title>Trigger Functions</title>
+
+ <para>
+ Currently <productname>PostgreSQL</> provides one built in trigger
+ function, <function>min_update_trigger</>, which will prevent any update
+ that does not actually change the data in the row from taking place, in
+ contrast to the normal behaviour which always performs the update
+ regardless of whether or not the data has changed.
+ </para>
+
+ <para>
+ The <function>min_update_trigger</> function can be added to a table
+ like this:
+ <programlisting>
+ CREATE TRIGGER _min_update
+ BEFORE UPDATE ON tablename
+ FOR EACH ROW EXECUTE PROCEDURE min_update_trigger();
+ </programlisting>
+ </para>
+ <para>
+ For mare information about creating triggers, see
+ <xref linkend="SQL-CREATETRIGGER">.
+ </para>
+ </sect1>
</chapter>
Index: src/backend/utils/adt/Makefile
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/Makefile,v
retrieving revision 1.69
diff -c -r1.69 Makefile
*** src/backend/utils/adt/Makefile 19 Feb 2008 10:30:08 -0000 1.69
--- src/backend/utils/adt/Makefile 22 Oct 2008 18:35:51 -0000
***************
*** 25,31 ****
tid.o timestamp.o varbit.o varchar.o varlena.o version.o xid.o \
network.o mac.o inet_net_ntop.o inet_net_pton.o \
ri_triggers.o pg_lzcompress.o pg_locale.o formatting.o \
! ascii.o quote.o pgstatfuncs.o encode.o dbsize.o genfile.o \
tsginidx.o tsgistidx.o tsquery.o tsquery_cleanup.o tsquery_gist.o \
tsquery_op.o tsquery_rewrite.o tsquery_util.o tsrank.o \
tsvector.o tsvector_op.o tsvector_parser.o \
--- 25,31 ----
tid.o timestamp.o varbit.o varchar.o varlena.o version.o xid.o \
network.o mac.o inet_net_ntop.o inet_net_pton.o \
ri_triggers.o pg_lzcompress.o pg_locale.o formatting.o \
! ascii.o quote.o pgstatfuncs.o encode.o dbsize.o genfile.o trigfuncs.o \
tsginidx.o tsgistidx.o tsquery.o tsquery_cleanup.o tsquery_gist.o \
tsquery_op.o tsquery_rewrite.o tsquery_util.o tsrank.o \
tsvector.o tsvector_op.o tsvector_parser.o \
Index: src/backend/utils/adt/trigfuncs.c
===================================================================
RCS file: src/backend/utils/adt/trigfuncs.c
diff -N src/backend/utils/adt/trigfuncs.c
*** /dev/null 1 Jan 1970 00:00:00 -0000
--- src/backend/utils/adt/trigfuncs.c 22 Oct 2008 18:35:51 -0000
***************
*** 0 ****
--- 1,73 ----
+ /*-------------------------------------------------------------------------
+ *
+ * trigfuncs.c
+ * Builtin functions for useful trigger support.
+ *
+ *
+ * Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * $PostgreSQL:$
+ *
+ *-------------------------------------------------------------------------
+ */
+
+
+
+ #include "postgres.h"
+ #include "commands/trigger.h"
+ #include "access/htup.h"
+
+ /*
+ * min_update_trigger
+ *
+ * This trigger function will inhibit an update from being done
+ * if the OLD and NEW records are identical.
+ *
+ */
+
+ Datum
+ min_update_trigger(PG_FUNCTION_ARGS)
+ {
+ TriggerData *trigdata = (TriggerData *) fcinfo->context;
+ HeapTuple newtuple, oldtuple, rettuple;
+ HeapTupleHeader newheader, oldheader;
+
+ /* make sure it's called as a trigger */
+ if (!CALLED_AS_TRIGGER(fcinfo))
+ elog(ERROR, "min_update_trigger: not called by trigger manager");
+
+ /* and that it's called on update */
+ if (! TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
+ elog(ERROR, "min_update_trigger: not called on update");
+
+ /* and that it's called before update */
+ if (! TRIGGER_FIRED_BEFORE(trigdata->tg_event))
+ elog(ERROR, "min_update_trigger: not called before update");
+
+ /* and that it's called for each row */
+ if (! TRIGGER_FIRED_FOR_ROW(trigdata->tg_event))
+ elog(ERROR, "min_update_trigger: not called for each row");
+
+ /* get tuple data, set default return */
+ rettuple = newtuple = trigdata->tg_newtuple;
+ oldtuple = trigdata->tg_trigtuple;
+
+ newheader = newtuple->t_data;
+ oldheader = oldtuple->t_data;
+
+ if (newtuple->t_len == oldtuple->t_len &&
+ newheader->t_hoff == oldheader->t_hoff &&
+ (HeapTupleHeaderGetNatts(newheader) ==
+ HeapTupleHeaderGetNatts(oldheader) ) &&
+ ((newheader->t_infomask & ~HEAP_XACT_MASK) ==
+ (oldheader->t_infomask & ~HEAP_XACT_MASK) )&&
+ memcmp(((char *)newheader) + offsetof(HeapTupleHeaderData, t_bits),
+ ((char *)oldheader) + offsetof(HeapTupleHeaderData, t_bits),
+ newtuple->t_len - offsetof(HeapTupleHeaderData, t_bits)) == 0)
+ {
+ rettuple = NULL;
+ }
+
+ return PointerGetDatum(rettuple);
+ }
Index: src/include/catalog/pg_proc.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/catalog/pg_proc.h,v
retrieving revision 1.520
diff -c -r1.520 pg_proc.h
*** src/include/catalog/pg_proc.h 14 Oct 2008 17:12:33 -0000 1.520
--- src/include/catalog/pg_proc.h 22 Oct 2008 18:35:52 -0000
***************
*** 2290,2295 ****
--- 2290,2298 ----
DATA(insert OID = 1686 ( pg_get_keywords PGNSP PGUID 12 10 400 0 f f t t s 0 2249 "" "{25,18,25}" "{o,o,o}" "{word,catcode,catdesc}" pg_get_keywords _null_ _null_ _null_ ));
DESCR("list of SQL keywords");
+ /* utility minimal update trigger */
+ DATA(insert OID = 1619 ( min_update_trigger PGNSP PGUID 12 1 0 0 f f t f v 0 2279 "" _null_ _null_ _null_ min_update_trigger _null_ _null_ _null_ ));
+ DESCR("minimal update trigger function");
/* Generic referential integrity constraint triggers */
DATA(insert OID = 1644 ( RI_FKey_check_ins PGNSP PGUID 12 1 0 0 f f t f v 0 2279 "" _null_ _null_ _null_ RI_FKey_check_ins _null_ _null_ _null_ ));
Index: src/include/utils/builtins.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/utils/builtins.h,v
retrieving revision 1.324
diff -c -r1.324 builtins.h
*** src/include/utils/builtins.h 13 Oct 2008 16:25:20 -0000 1.324
--- src/include/utils/builtins.h 22 Oct 2008 18:35:52 -0000
***************
*** 899,904 ****
--- 899,907 ----
extern Datum RI_FKey_setdefault_del(PG_FUNCTION_ARGS);
extern Datum RI_FKey_setdefault_upd(PG_FUNCTION_ARGS);
+ /* trigfuncs.c */
+ extern Datum min_update_trigger(PG_FUNCTION_ARGS);
+
/* encoding support functions */
extern Datum getdatabaseencoding(PG_FUNCTION_ARGS);
extern Datum database_character_set(PG_FUNCTION_ARGS);
Index: src/test/regress/expected/triggers.out
===================================================================
RCS file: /cvsroot/pgsql/src/test/regress/expected/triggers.out,v
retrieving revision 1.24
diff -c -r1.24 triggers.out
*** src/test/regress/expected/triggers.out 1 Feb 2007 19:10:30 -0000 1.24
--- src/test/regress/expected/triggers.out 22 Oct 2008 18:35:52 -0000
***************
*** 537,539 ****
--- 537,564 ----
NOTICE: row 2 not changed
DROP TABLE trigger_test;
DROP FUNCTION mytrigger();
+ -- minimal update trigger
+ CREATE TABLE min_update_test (
+ f1 text,
+ f2 int,
+ f3 int);
+ INSERT INTO min_update_test VALUES ('a',1,2),('b','2',null);
+ CREATE TRIGGER _min_update
+ BEFORE UPDATE ON min_update_test
+ FOR EACH ROW EXECUTE PROCEDURE min_update_trigger();
+ \set QUIET false
+ UPDATE min_update_test SET f1 = f1;
+ UPDATE 0
+ UPDATE min_update_test SET f2 = f2 + 1;
+ UPDATE 2
+ UPDATE min_update_test SET f3 = 2 WHERE f3 is null;
+ UPDATE 1
+ \set QUIET true
+ SELECT * FROM min_update_test;
+ f1 | f2 | f3
+ ----+----+----
+ a | 2 | 2
+ b | 3 | 2
+ (2 rows)
+
+ DROP TABLE min_update_test;
Index: src/test/regress/sql/triggers.sql
===================================================================
RCS file: /cvsroot/pgsql/src/test/regress/sql/triggers.sql,v
retrieving revision 1.13
diff -c -r1.13 triggers.sql
*** src/test/regress/sql/triggers.sql 26 Jun 2006 17:24:41 -0000 1.13
--- src/test/regress/sql/triggers.sql 22 Oct 2008 18:35:52 -0000
***************
*** 415,417 ****
--- 415,446 ----
DROP TABLE trigger_test;
DROP FUNCTION mytrigger();
+
+
+ -- minimal update trigger
+
+ CREATE TABLE min_update_test (
+ f1 text,
+ f2 int,
+ f3 int);
+
+ INSERT INTO min_update_test VALUES ('a',1,2),('b','2',null);
+
+ CREATE TRIGGER _min_update
+ BEFORE UPDATE ON min_update_test
+ FOR EACH ROW EXECUTE PROCEDURE min_update_trigger();
+
+ \set QUIET false
+
+ UPDATE min_update_test SET f1 = f1;
+
+ UPDATE min_update_test SET f2 = f2 + 1;
+
+ UPDATE min_update_test SET f3 = 2 WHERE f3 is null;
+
+ \set QUIET true
+
+ SELECT * FROM min_update_test;
+
+ DROP TABLE min_update_test;
+
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
