pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: noupcol code cleanup
2+ messages / 2 participants
[nested] [flat]
* Re: noupcol code cleanup
@ 2003-01-10 18:46 Ron Peterson <rpeterso@mtholyoke.edu>
0 siblings, 1 reply; 2+ messages in thread
From: Ron Peterson @ 2003-01-10 18:46 UTC (permalink / raw)
To: pgsql-sql
Well, I went through this again myself, and fixed a lot of stuff. I'm
going to drop this thread, but didn't want the last chunk of code I
posted to be so crappy. This is what I have come up with, FWIW:
#include "executor/spi.h" /* this is what you need to work with SPI */
#include "commands/trigger.h" /* and triggers */
#include "utils/lsyscache.h" /* for get_typlenbyval */
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 */
Datum *newcolval; /* new column value */
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 */
Oid oid; /* is Datum of type ByVal? */
bool typByVal; /* is Datum of type ByVal? */
int16 typLen; /* Datum size */
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 *) palloc (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));
newcolval = (Datum*) palloc (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[i] == SPI_ERROR_NOATTRIBUTE) {
elog (ERROR, "noupcols: there is no attribute '%s' in relation '%s'",
args[i],
SPI_getrelname (rel));
}
/* Get previous value of column */
oldcolvals[i] = SPI_getbinval (oldtuple, tupdesc, colindices[i], &isnull);
*newcolval = SPI_getbinval (newtuple, tupdesc, colindices[i], &isnull);
/* need this for datumIsEqual, below */
oid = SPI_gettypeid (tupdesc, colindices[i]);
get_typlenbyval (oid, &typLen, &typByVal );
/* if an update is attempted on a locked column, post a notification that it isn't allowed */
if (! datumIsEqual (oldcolvals[i], *newcolval, typByVal, typLen)) {
elog (NOTICE, "noupcols: attribute '%s' in relation '%s' is locked",
args[i],
SPI_getrelname (rel));
}
}
/* Restore protected columns to their old values */
newnewtuple = SPI_modifytuple (rel, newtuple, ncols, colindices, oldcolvals, NULL);
if (SPI_result == SPI_ERROR_ARGUMENT) {
elog (ERROR, "noupcols: bad argument to SPI_modifytuple\n");
}
if (SPI_result == SPI_ERROR_NOATTRIBUTE) {
elog (ERROR, "noupcols: bad attribute value passed to SPI_modifytuple\n");
}
pfree (oldcolvals);
pfree (newcolval);
pfree (colindices);
SPI_finish ();
return PointerGetDatum (newnewtuple);
}
--
Ron Peterson -o)
Network & Systems Manager /\\
Mount Holyoke College _\_v
http://www.mtholyoke.edu/~rpeterso ----
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: [SQL] noupcol code cleanup
@ 2003-05-24 04:45 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Ron Peterson <rpeterso@mtholyoke.edu>
0 siblings, 0 replies; 2+ messages in thread
From: Bruce Momjian @ 2003-05-24 04:45 UTC (permalink / raw)
To: Ron Peterson <rpeterso@mtholyoke.edu>; +Cc: PostgreSQL-patches <pgsql-patches@postgresql.org>
Ron, what did you want done with this? How is it different from
/contrib/noup?
---------------------------------------------------------------------------
Ron Peterson wrote:
> Well, I went through this again myself, and fixed a lot of stuff. I'm
> going to drop this thread, but didn't want the last chunk of code I
> posted to be so crappy. This is what I have come up with, FWIW:
>
> #include "executor/spi.h" /* this is what you need to work with SPI */
> #include "commands/trigger.h" /* and triggers */
> #include "utils/lsyscache.h" /* for get_typlenbyval */
>
> 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 */
> Datum *newcolval; /* new column value */
> 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 */
> Oid oid; /* is Datum of type ByVal? */
> bool typByVal; /* is Datum of type ByVal? */
> int16 typLen; /* Datum size */
> 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 *) palloc (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));
> newcolval = (Datum*) palloc (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[i] == SPI_ERROR_NOATTRIBUTE) {
> elog (ERROR, "noupcols: there is no attribute '%s' in relation '%s'",
> args[i],
> SPI_getrelname (rel));
> }
>
> /* Get previous value of column */
> oldcolvals[i] = SPI_getbinval (oldtuple, tupdesc, colindices[i], &isnull);
> *newcolval = SPI_getbinval (newtuple, tupdesc, colindices[i], &isnull);
>
> /* need this for datumIsEqual, below */
> oid = SPI_gettypeid (tupdesc, colindices[i]);
> get_typlenbyval (oid, &typLen, &typByVal );
>
> /* if an update is attempted on a locked column, post a notification that it isn't allowed */
> if (! datumIsEqual (oldcolvals[i], *newcolval, typByVal, typLen)) {
> elog (NOTICE, "noupcols: attribute '%s' in relation '%s' is locked",
> args[i],
> SPI_getrelname (rel));
> }
> }
>
> /* Restore protected columns to their old values */
> newnewtuple = SPI_modifytuple (rel, newtuple, ncols, colindices, oldcolvals, NULL);
>
> if (SPI_result == SPI_ERROR_ARGUMENT) {
> elog (ERROR, "noupcols: bad argument to SPI_modifytuple\n");
> }
>
> if (SPI_result == SPI_ERROR_NOATTRIBUTE) {
> elog (ERROR, "noupcols: bad attribute value passed to SPI_modifytuple\n");
> }
>
> pfree (oldcolvals);
> pfree (newcolval);
> pfree (colindices);
> SPI_finish ();
>
> return PointerGetDatum (newnewtuple);
> }
>
>
> --
> Ron Peterson -o)
> Network & Systems Manager /\\
> Mount Holyoke College _\_v
> http://www.mtholyoke.edu/~rpeterso ----
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2003-05-24 04:45 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-01-10 18:46 Re: noupcol code cleanup Ron Peterson <rpeterso@mtholyoke.edu>
2003-05-24 04:45 ` Bruce Momjian <pgman@candle.pha.pa.us>
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