From: Ron Peterson <rpeterso@mtholyoke.edu>
To: dev@archonet.com
Cc: pgsql-sql@postgresql.org
Subject: Re: insert rule doesn't see id field
Date: Thu, 9 Jan 2003 16:50:56 -0500
Message-ID: <20030109215056.GA7798@mtholyoke.edu> (raw)
In-Reply-To: <2930.192.168.1.16.1042142044.squirrel@mainbox.archonet.com>
References: <20030107145422.GE12245@mtholyoke.edu>
<13558.1041955268@sss.pgh.pa.us>
<20030108181303.GA23456@mtholyoke.edu>
<20030109191258.GA6739@mtholyoke.edu>
<2930.192.168.1.16.1042142044.squirrel@mainbox.archonet.com>
On Thu, Jan 09, 2003 at 07:54:04PM -0000, dev@archonet.com wrote:
> Sounds similar to the plpgsql example at:
>
> http://www.archonet.com/pgdocs/lock-field.html
>
> which silently discards changes. It's trivial (apart from quoting issues)
> to write a trigger generator to customise the above in plpgsql (see the
> Cookbook on techdocs.postgresql.org for examples)
Ah - I wish I saw that earlier. Thanks for pointer. OTOH, I learned
something...
FWIW (probably just a good laugh):
#include "executor/spi.h" /* this is what you need to work with SPI */
#include "commands/trigger.h" /* and triggers */
#include <ctype.h> /* tolower () */
#include <stdlib.h>
extern Datum noupcols (PG_FUNCTION_ARGS);
/*
noupcols () -- revoke permission on column(s)
e.g.
CREATE FUNCTION noupcols ()
RETURNS opaque
AS '/usr/lib/postgresql/lib/noupcols.so'
LANGUAGE 'C';
CREATE TRIGGER person_noupcols
BEFORE UPDATE ON person
FOR EACH ROW
EXECUTE PROCEDURE noupcols( 'name_last', 'id' );
Based on code from contrib/noup.c
The approach adopted here is to set the values of all of the columns
specified by noupcols to their old values.
*/
PG_FUNCTION_INFO_V1 (noupcols);
Datum
noupcols (PG_FUNCTION_ARGS)
{
TriggerData *trigdata = (TriggerData *) fcinfo->context;
Trigger *trigger; /* to get trigger name */
Relation rel; /* triggered relation */
char **args; /* arguments: column names */
int ncols; /* # of args specified in CREATE TRIGGER */
int *colindices; /* array of column indices to modify */
Datum *oldcolvals; /* old column values */
HeapTuple oldtuple = NULL; /* tuple before being modified */
HeapTuple newtuple = NULL; /* new tuple after user-specified update */
HeapTuple newnewtuple = NULL; /* tuple to return, after restoring newtuple's protected columns to their old values */
TupleDesc tupdesc; /* tuple description */
bool isnull; /* to know is some column NULL or not */
int ret;
int i;
if (!CALLED_AS_TRIGGER (fcinfo))
elog(ERROR, "noup: not fired by trigger manager");
if (TRIGGER_FIRED_FOR_STATEMENT (trigdata->tg_event))
elog (ERROR, "noup: can't process STATEMENT events");
if (TRIGGER_FIRED_BY_INSERT (trigdata->tg_event))
elog (ERROR, "noup: can't process INSERT events");
else if (TRIGGER_FIRED_BY_DELETE(trigdata->tg_event))
elog (ERROR, "noup: can't process DELETE events");
oldtuple = trigdata->tg_trigtuple;
newtuple = trigdata->tg_newtuple;
trigger = trigdata->tg_trigger;
rel = trigdata->tg_relation;
tupdesc = rel->rd_att;
ncols = trigger->tgnargs;
args = trigger->tgargs;
colindices = (int *) malloc (ncols * sizeof (int));
/* Connect to SPI manager */
if ((ret = SPI_connect()) < 0)
elog (ERROR, "noupcol: SPI_connect returned %d", ret);
/* Allocate space to place column values */
oldcolvals = (Datum*) palloc (ncols * sizeof (Datum));
/* For each column ... */
for (i = 0; i < ncols; i++)
{
/* get index of column in tuple */
colindices[i] = SPI_fnumber (tupdesc, args[i]);
/* Bad guys may give us un-existing column in CREATE TRIGGER */
if (colindices < 0)
{
elog (ERROR, "noupcols: there is no attribute %s in relation %s",
args[i],
SPI_getrelname (rel));
pfree (oldcolvals);
free (colindices);
SPI_finish ();
return PointerGetDatum (NULL);
}
/* Get previous value of column */
oldcolvals[i] = SPI_getbinval (oldtuple, tupdesc, colindices[i], &isnull);
}
/* Restore protected columns to their old values */
newnewtuple = SPI_modifytuple (rel, newtuple, ncols, colindices, oldcolvals, NULL);
pfree (oldcolvals);
free (colindices);
SPI_finish ();
if (SPI_result == SPI_ERROR_ARGUMENT) {
elog (ERROR, "noupcols: bad argument to SPI_modifytuple\n");
return PointerGetDatum (NULL);
}
if (SPI_result == SPI_ERROR_NOATTRIBUTE) {
elog (ERROR, "noupcols: bad attribute value passed to SPI_modifytuple\n");
return PointerGetDatum (NULL);
}
return PointerGetDatum (newnewtuple);
}
--
Ron Peterson -o)
Network & Systems Manager /\\
Mount Holyoke College _\_v
http://www.mtholyoke.edu/~rpeterso ----
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: rpeterso@mtholyoke.edu, dev@archonet.com
Subject: Re: insert rule doesn't see id field
In-Reply-To: <20030109215056.GA7798@mtholyoke.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox