From fd262dac8c7eae2672949fe4ae344e932bf73ac3 Mon Sep 17 00:00:00 2001
From: Bruce Momjian <bruce@momjian.us>
Date: Tue, 16 Jun 1998 17:53:44 +0000
Subject: [PATCH] > Sure send it in, we can put it in contrib.  > > > > > I
 wrote a real small function to revoke update on a column. The function > >
 doesn't do anything > > fancy like checking user ids.  > > > > I copied most
 of it from the refint.c in the contrib directory. > > > > Should I post this
 somewhere? It really isn't very big. > >

Here it is...

--
		| Email - rick@rpacorp.com
Rick Poleshuck  | Voice - (908) 653-1070      Fax - (908) 653-0265
		| Mail  - RPA Corporation |       - 308 Elizabeth
		Avenue, Cranford, New Jersey  07016
---
 contrib/noupdate/noup.c       | 111 ++++++++++++++++++++++++++++++++++
 contrib/noupdate/noup.example |   9 +++
 contrib/noupdate/noup.source  |   7 +++
 3 files changed, 127 insertions(+)
 create mode 100644 contrib/noupdate/noup.c
 create mode 100644 contrib/noupdate/noup.example
 create mode 100644 contrib/noupdate/noup.source

diff --git a/contrib/noupdate/noup.c b/contrib/noupdate/noup.c
new file mode 100644
index 0000000000..efbe031239
--- /dev/null
+++ b/contrib/noupdate/noup.c
@@ -0,0 +1,111 @@
+/*
+ * noup.c --	functions to remove update permission from a column
+ */
+
+#include "executor/spi.h"		/* this is what you need to work with SPI */
+#include "commands/trigger.h"	/* -"- and triggers */
+#include <ctype.h>				/* tolower () */
+
+HeapTuple	noup(void);
+
+/*
+ * noup () -- revoke permission on column
+ *
+ * Though it's called without args You have to specify referenced
+ * table/column while creating trigger:  
+ * EXECUTE PROCEDURE noup ('col').
+ */
+
+HeapTuple						/* have to return HeapTuple to Executor */
+noup()
+{
+	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 */
+	Relation	rel;			/* triggered relation */
+	HeapTuple	tuple = NULL;	/* tuple to return */
+	TupleDesc	tupdesc;		/* tuple description */
+	bool		isnull;			/* to know is some column NULL or not */
+	int			ret;
+	int			i;
+
+	/*
+	 * Some checks first...
+	 */
+
+	/* Called by trigger manager ? */
+	if (!CurrentTriggerData)
+		elog(WARN, "noup: triggers are not initialized");
+
+	/* Should be called for ROW trigger */
+	if (TRIGGER_FIRED_FOR_STATEMENT(CurrentTriggerData->tg_event))
+		elog(WARN, "noup: can't process STATEMENT events");
+
+	/* Not should be called for INSERT */
+	if (TRIGGER_FIRED_BY_INSERT(CurrentTriggerData->tg_event))
+		elog(WARN, "noup: can't process INSERT events");
+
+	/* Not should be called for DELETE */
+	else if (TRIGGER_FIRED_BY_DELETE(CurrentTriggerData->tg_event))
+		elog(WARN, "noup: can't process DELETE events");
+
+	/* check new Tuple */
+	tuple = CurrentTriggerData->tg_newtuple;
+
+	trigger = CurrentTriggerData->tg_trigger;
+	nargs = trigger->tgnargs;
+	args = trigger->tgargs;
+
+	nkeys = nargs;
+	rel = CurrentTriggerData->tg_relation;
+	tupdesc = rel->rd_att;
+
+	/*
+	 * Setting CurrentTriggerData to NULL prevents direct calls to trigger
+	 * functions in queries. Normally, trigger functions have to be called
+	 * by trigger manager code only.
+	 */
+	CurrentTriggerData = NULL;
+
+	/* Connect to SPI manager */
+	if ((ret = SPI_connect()) < 0)
+		elog(WARN, "noup: SPI_connect returned %d", ret);
+
+	/*
+	 * We use SPI plan preparation feature, so allocate space to place key
+	 * values.
+	 */
+	kvals = (Datum *) palloc(nkeys * sizeof(Datum));
+
+	/* 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)
+			elog(WARN, "noup: 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 NOT NULL then cancel update
+		 */
+		if (!isnull)
+		{
+	
+			elog(WARN, "%s: update not allowed", args[i] );
+			SPI_finish();
+			return NULL;
+		}
+
+	}
+
+	SPI_finish();
+	return (tuple);
+}
diff --git a/contrib/noupdate/noup.example b/contrib/noupdate/noup.example
new file mode 100644
index 0000000000..62db1ee0cc
--- /dev/null
+++ b/contrib/noupdate/noup.example
@@ -0,0 +1,9 @@
+CREATE TABLE TEST ( COL1 INT, COL2 INT, COL3 INT );
+
+CREATE TRIGGER BT BEFORE UPDATE ON TEST FOR EACH ROW
+	EXECUTE PROCEDURE 
+	noup ('COL1');
+
+-- Now Try
+INSERT INTO TEST VALUES (10,20,30);
+UPDATE TEST SET COL1 = 5;
diff --git a/contrib/noupdate/noup.source b/contrib/noupdate/noup.source
new file mode 100644
index 0000000000..0aa875d51e
--- /dev/null
+++ b/contrib/noupdate/noup.source
@@ -0,0 +1,7 @@
+DROP FUNCTION noup ();
+
+CREATE FUNCTION noup ()
+	RETURNS opaque
+	AS '_OBJWD_/noup_DLSUFFIX_'
+	LANGUAGE 'c'
+;
-- 
2.40.0