From: Ron Peterson <rpeterso@mtholyoke.edu>
Date: Fri, 10 Jan 2003 13:46:31 -0500
To: pgsql-sql@postgresql.org
Subject: Re: noupcol code cleanup
Message-ID: <20030110184631.GA14535@mtholyoke.edu>
Mail-Followup-To: pgsql-sql@postgresql.org
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>
	<20030109215056.GA7798@mtholyoke.edu>
	<20030110020707.GA9048@mtholyoke.edu>
	<27958.1042174422@sss.pgh.pa.us>
	<20030110162737.GA13191@mtholyoke.edu>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20030110162737.GA13191@mtholyoke.edu>
User-Agent: Mutt/1.3.28i
Organization: Mount Holyoke College

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   ----