Re: [SQL] noupcol code cleanup - Mailing list pgsql-patches

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