Обсуждение: insert rule doesn't see id field
Two seperate problems, really, but first the SQL: CREATE SEQUENCE person_id_seq; CREATE TABLE person ( name_last VARCHAR( 50 ) NOT NULL, name_first VARCHAR( 50 ) NOT NULL, id INTEGER DEFAULT nextval( 'person_id_seq' ) PRIMARY KEY ); CREATE INDEX person_name_idx ON person ( name_last, name_first ); CREATE TRIGGER person_id_noup BEFORE UPDATE ON person FOR EACH ROW EXECUTE PROCEDURE noup( 'id' ); CREATE RULE person_insert AS ON INSERT TO person DO INSERT INTO person_log ( name_last, name_first, mod_type, person_id ) VALUES ( new.name_last, new.name_first, 'I',new.id ); (Problem 1) My insert rule creates a record in person_log just fine. It inserts values for all of the fields except person_id. Why doesn't new.id contain a value? Corresponding update and delete rules work as expected. (Problem 2) I thought that the idea behind noup was to protect single columns from update. However, when I apply the noup trigger as above, I can't update /any/ column. Is this the intended behaviour? e.g. directory=# select * from person;name_last | name_first | id -----------+------------+----Peterson | Ronald | 1Humbert | Humbert | 2 (2 rows) directory=# update person set name_first='Ron' where name_first='Ronald'; NOTICE: id: update not allowed UPDATE 0 -- Ron Peterson -o) Network & Systems Manager /\\ Mount Holyoke College _\_v http://www.mtholyoke.edu/~rpeterso ----
BTW, PostgreSQL 7.2.1-2woody2 on Debian. -- Ron Peterson -o) Network & Systems Manager /\\ Mount Holyoke College _\_v http://www.mtholyoke.edu/~rpeterso ----
Ron Peterson <rpeterso@mtholyoke.edu> writes:
> CREATE RULE person_insert AS
> ON INSERT TO person
> DO
> INSERT INTO person_log ( name_last, name_first, mod_type, person_id )
> VALUES ( new.name_last, new.name_first, 'I', new.id );
> [where id is a serial column]
> My insert rule creates a record in person_log just fine. It inserts
> values for all of the fields except person_id. Why doesn't new.id
> contain a value?
This is a bug in 7.2.*. It's fixed in 7.3. However, your rule will
still not work the way you would like, because rules are macros: the
default expression for id will get evaluated once in the rule and once
in your original query, leading to two different sequence numbers
getting inserted.
The only way to make this example work is to issue the log insertion
from a trigger, not a rule.
> (Problem 2)
> I thought that the idea behind noup was to protect single columns from
> update. However, when I apply the noup trigger as above, I can't
> update /any/ column. Is this the intended behaviour?
Idly looking at the source code for contrib/noupdate/noup.c, I don't
believe that it has ever worked as advertised: it seems to reject any
non-null value for the target column, independently of whether the
value is the same as before (which is what I'd have thought it should
do).
Is anyone interested in fixing it? Or should we just remove it?
If it's been there since 6.4 and you're the first person to try to use
it, as seems to be the case, then I'd have to say that it's a waste of
space in the distribution.
regards, tom lane
On Tue, Jan 07, 2003 at 11:01:08AM -0500, Tom Lane wrote: > > I thought that the idea behind noup was to protect single columns from > > update. However, when I apply the noup trigger as above, I can't > > update /any/ column. Is this the intended behaviour? > > Idly looking at the source code for contrib/noupdate/noup.c, I don't > believe that it has ever worked as advertised: it seems to reject any > non-null value for the target column, independently of whether the > value is the same as before (which is what I'd have thought it should > do). > > Is anyone interested in fixing it? Or should we just remove it? > If it's been there since 6.4 and you're the first person to try to use > it, as seems to be the case, then I'd have to say that it's a waste of > space in the distribution. I'm going to see if I can create this function. The issue I face is that I'm allowing certain clients to access parts of a PostgreSQL database on MS Access via ODBC. This means I can't really control how people may try to edit the data. Well, I could, by using MS Access security features, but I'd rather do what I can on the back end. If someone changes an ID field, then as long as foreign key contraints on other related tables are set to cascade or whatever, that won't be a problem. But what if someone updates an ID field to something higher than my current sequence? Then when the sequence hits that ID, it will crap out. Maybe just try again, but what if that happened to a bunch of records? Could be a pain. So that's the problem I'd like to prevent, for which I think this function would be useful. So I'll hack at it and see what I come up with. Might not happen immediately, though.. -- Ron Peterson -o) Network & Systems Manager /\\ Mount Holyoke College _\_v http://www.mtholyoke.edu/~rpeterso ----
On Wed, Jan 08, 2003 at 01:13:03PM -0500, Ron Peterson wrote: > On Tue, Jan 07, 2003 at 11:01:08AM -0500, Tom Lane wrote: > > > > I thought that the idea behind noup was to protect single columns from > > > update. However, when I apply the noup trigger as above, I can't > > > update /any/ column. Is this the intended behaviour? > > > > Idly looking at the source code for contrib/noupdate/noup.c, I don't > > believe that it has ever worked as advertised: it seems to reject any > > non-null value for the target column, independently of whether the > > value is the same as before (which is what I'd have thought it should > > do). > > > > Is anyone interested in fixing it? Or should we just remove it? > > If it's been there since 6.4 and you're the first person to try to use > > it, as seems to be the case, then I'd have to say that it's a waste of > > space in the distribution. > > I'm going to see if I can create this function. Well, I think I've thunk something up. Of course I'm happy to submit my modification for distribution or ridicule, as the case may be. Where should I submit this? I made a function noupcols() which takes one or more column names as arguments. The function then creates a new tuple by getting the old values for those columns, and then doing an SPI_modifytuple on the new tuple using the old values for those columns. I'm kind of flying by the seat of my pants here, so if anyone would care to critically review my code, by all means... -- Ron Peterson -o) Network & Systems Manager /\\ Mount Holyoke College _\_v http://www.mtholyoke.edu/~rpeterso ----
> On Wed, Jan 08, 2003 at 01:13:03PM -0500, Ron Peterson wrote: >> On Tue, Jan 07, 2003 at 11:01:08AM -0500, Tom Lane wrote: >> >> > > I thought that the idea behind noup was to protect single columns >> from >> > > update. However, when I apply the noup trigger as above, I can't >> > > update /any/ column. Is this the intended behaviour? >> > >> I'm going to see if I can create this function. > > Well, I think I've thunk something up. Of course I'm happy to submit > my modification for distribution or ridicule, as the case may be. > Where should I submit this? > > I made a function noupcols() which takes one or more column names as > arguments. The function then creates a new tuple by getting the old > values for those columns, and then doing an SPI_modifytuple on the new > tuple using the old values for those columns. > > I'm kind of flying by the seat of my pants here, so if anyone would > care to critically review my code, by all means... 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) - Richard Huxton
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 columnNULL 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"); returnPointerGetDatum (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 ----
On Thu, Jan 09, 2003 at 04:50:56PM -0500, Ron Peterson wrote:
> colindices = (int *) malloc (ncols * sizeof (int));
Of course we should verify that malloc succeeded...
if (colindices == NULL) {elog (ERROR, "noupcol: malloc failed\n");SPI_finish();return PointerGetDatum (NULL);
}
--
Ron Peterson -o)
Network & Systems Manager /\\
Mount Holyoke College _\_v
http://www.mtholyoke.edu/~rpeterso ----
Ron Peterson <rpeterso@mtholyoke.edu> writes:
> On Thu, Jan 09, 2003 at 04:50:56PM -0500, Ron Peterson wrote:
>> colindices = (int *) malloc (ncols * sizeof (int));
> Of course we should verify that malloc succeeded...
Actually, the correct answer is "you should not be using malloc() in
backend functions". You should be using palloc, or possibly
MemoryContextAlloc, either of which will elog if it can't get space.
> if (colindices == NULL) {
> elog (ERROR, "noupcol: malloc failed\n");
> SPI_finish();
> return PointerGetDatum (NULL);
> }
This is even more pointless. Control does not return from elog(ERROR),
so the two following lines are dead code.
regards, tom lane
To everyone interested, check out Tom Lane's and Bruce's comments on pgsql-hackers: http://archives.postgresql.org/pgsql-hackers/2003-01/msg00446.php There seems to be some consensus towards removing $ from the list of allowed operator characters. Regards, ===== Radu-Adrian Popescu CSA, DBA, Developer Aldratech Ltd. ----- Original Message ----- From: "Ron Peterson" <rpeterso@mtholyoke.edu> To: <pgsql-sql@postgresql.org> Sent: Thursday, January 09, 2003 9:12 PM Subject: Re: [SQL] insert rule doesn't see id field On Wed, Jan 08, 2003 at 01:13:03PM -0500, Ron Peterson wrote: > On Tue, Jan 07, 2003 at 11:01:08AM -0500, Tom Lane wrote: > > > > I thought that the idea behind noup was to protect single columns from > > > update. However, when I apply the noup trigger as above, I can't > > > update /any/ column. Is this the intended behaviour? > > > > Idly looking at the source code for contrib/noupdate/noup.c, I don't > > believe that it has ever worked as advertised: it seems to reject any > > non-null value for the target column, independently of whether the > > value is the same as before (which is what I'd have thought it should > > do). > > > > Is anyone interested in fixing it? Or should we just remove it? > > If it's been there since 6.4 and you're the first person to try to use > > it, as seems to be the case, then I'd have to say that it's a waste of > > space in the distribution. > > I'm going to see if I can create this function. Well, I think I've thunk something up. Of course I'm happy to submit my modification for distribution or ridicule, as the case may be. Where should I submit this? I made a function noupcols() which takes one or more column names as arguments. The function then creates a new tuple by getting the old values for those columns, and then doing an SPI_modifytuple on the new tuple using the old values for those columns. I'm kind of flying by the seat of my pants here, so if anyone would care to critically review my code, by all means... -- 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
I'm extremely sorry about the post in this thread ! Had a brain cramp, my appologies. Should have been Re: [SQL] SQL function parse error. Terribly sorry again ! ===== Radu-Adrian Popescu CSA, DBA, Developer Aldratech Ltd. ----- Original Message ----- From: "Radu-Adrian Popescu" <radu.popescu@aldratech.com> To: "Ron Peterson" <rpeterso@mtholyoke.edu>; <pgsql-sql@postgresql.org> Sent: Friday, January 10, 2003 11:31 AM Subject: Re: [SQL] insert rule doesn't see id field To everyone interested, check out Tom Lane's and Bruce's comments on pgsql-hackers: http://archives.postgresql.org/pgsql-hackers/2003-01/msg00446.php There seems to be some consensus towards removing $ from the list of allowed operator characters. Regards, ===== Radu-Adrian Popescu CSA, DBA, Developer Aldratech Ltd. ----- Original Message ----- From: "Ron Peterson" <rpeterso@mtholyoke.edu> To: <pgsql-sql@postgresql.org> Sent: Thursday, January 09, 2003 9:12 PM Subject: Re: [SQL] insert rule doesn't see id field On Wed, Jan 08, 2003 at 01:13:03PM -0500, Ron Peterson wrote: > On Tue, Jan 07, 2003 at 11:01:08AM -0500, Tom Lane wrote: > > > > I thought that the idea behind noup was to protect single columns from > > > update. However, when I apply the noup trigger as above, I can't > > > update /any/ column. Is this the intended behaviour? > > > > Idly looking at the source code for contrib/noupdate/noup.c, I don't > > believe that it has ever worked as advertised: it seems to reject any > > non-null value for the target column, independently of whether the > > value is the same as before (which is what I'd have thought it should > > do). > > > > Is anyone interested in fixing it? Or should we just remove it? > > If it's been there since 6.4 and you're the first person to try to use > > it, as seems to be the case, then I'd have to say that it's a waste of > > space in the distribution. > > I'm going to see if I can create this function. Well, I think I've thunk something up. Of course I'm happy to submit my modification for distribution or ridicule, as the case may be. Where should I submit this? I made a function noupcols() which takes one or more column names as arguments. The function then creates a new tuple by getting the old values for those columns, and then doing an SPI_modifytuple on the new tuple using the old values for those columns. I'm kind of flying by the seat of my pants here, so if anyone would care to critically review my code, by all means... -- 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 ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
On Thu, Jan 09, 2003 at 11:53:42PM -0500, Tom Lane wrote:
> Ron Peterson <rpeterso@mtholyoke.edu> writes:
> > On Thu, Jan 09, 2003 at 04:50:56PM -0500, Ron Peterson wrote:
> >> colindices = (int *) malloc (ncols * sizeof (int));
>
> > Of course we should verify that malloc succeeded...
>
> Actually, the correct answer is "you should not be using malloc() in
> backend functions". You should be using palloc, or possibly
> MemoryContextAlloc, either of which will elog if it can't get space.
>
> > if (colindices == NULL) {
> > elog (ERROR, "noupcol: malloc failed\n");
> > SPI_finish();
> > return PointerGetDatum (NULL);
> > }
>
> This is even more pointless. Control does not return from elog(ERROR),
> so the two following lines are dead code.
Thanks. Didn't know that.
Not that you're obligated to review my code in any way (i.e. ignore
this question if you have better things to do), but does the rest of
my code look o.k.? I was pretty pleased with myself that I figured
out how to modify a tuple w/ SPI, and might like to do more of the
same. I'd rather not develop bad habits, though...
--
Ron Peterson -o)
Network & Systems Manager /\\
Mount Holyoke College _\_v
http://www.mtholyoke.edu/~rpeterso ----
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
toreturn, after restoring newtuple's protected columns to their old values */ TupleDesc tupdesc; /*
tupledescription */ 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 ----
On Tue, Jan 07, 2003 at 11:01:08AM -0500, Tom Lane wrote:
> Ron Peterson <rpeterso@mtholyoke.edu> writes:
> > CREATE RULE person_insert AS
> > ON INSERT TO person
> > DO
> > INSERT INTO person_log ( name_last, name_first, mod_type, person_id )
> > VALUES ( new.name_last, new.name_first, 'I', new.id );
> > [where id is a serial column]
>
> > My insert rule creates a record in person_log just fine. It inserts
> > values for all of the fields except person_id. Why doesn't new.id
> > contain a value?
>
> This is a bug in 7.2.*. It's fixed in 7.3. However, your rule will
> still not work the way you would like, because rules are macros: the
> default expression for id will get evaluated once in the rule and once
> in your original query, leading to two different sequence numbers
> getting inserted.
I just installed 7.3.1. It works now, as you say, but it breaks if the
id field being updated by an insert rule references the id field it's
logging. And like you say, the rule also updates the sequence - not a
killer, but not so great.
I started writing a trigger. Meanwhile I'm just going to log updates
and deletes. After all, if a record has never been updated or deleted,
what's to audit?
One thing's tripping me up a bit while writing a trigger (in C - I like
C). I'd like the trigger function arguments to specify an attribute
mapping from the table being logged to the log table - e.g. logfun
('logtable', 'oldatt1', 'newatt1', 'oldatt2', 'newatt2', etc.). I
thought I'd be good and check that the types and field lengths match
before attempting to insert the log record. I can find out this info
for the relation pulling the trigger easy enought, but how would I go
about getting this info when all I have is the table/field name? I
could create and execute a SQL query something like the following, but
is that really the way to get at this info in C code?
select relname, attname, typname, typlen
from pg_class c, pg_attribute a, pg_type t
where c.oid=a.attrelid and a.atttypid=t.oid and get just the relevant record(s);
--
Ron Peterson -o)
Network & Systems Manager /\\
Mount Holyoke College _\_v
http://www.mtholyoke.edu/~rpeterso ----
Ron Peterson <rpeterso@mtholyoke.edu> writes:
> I can find out this info
> for the relation pulling the trigger easy enought, but how would I go
> about getting this info when all I have is the table/field name? I
> could create and execute a SQL query something like the following, but
> is that really the way to get at this info in C code?
Open the target relation and examine its tuple descriptor ...
regards, tom lane