Skip to content

Commit 629e895

Browse files
vadim4ovadim4o
committed
Trigger function for inserting user names.
Install compiled functions into $(LIBDIR)/contrib. (Thanks to Brook Milligan <brook@trillium.NMSU.Edu>)
1 parent 962c8bd commit 629e895

File tree

5 files changed

+129
-2
lines changed

5 files changed

+129
-2
lines changed

‎contrib/spi/Makefile

Lines changed: 12 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -3,23 +3,33 @@ SRCDIR= ../../src
33

44
include $(SRCDIR)/Makefile.global
55

6+
CONTRIBDIR=$(LIBDIR)/contrib
7+
68
CFLAGS+= $(CFLAGS_SL) -I$(SRCDIR)/include
79

810
ifdef REFINT_VERBOSE
911
CFLAGS+= -DREFINT_VERBOSE
1012
endif
1113

1214
TARGETS= refint$(DLSUFFIX) refint.sql timetravel$(DLSUFFIX) timetravel.sql \
13-
autoinc$(DLSUFFIX) autoinc.sql
15+
autoinc$(DLSUFFIX) autoinc.sql \
16+
insert_username$(DLSUFFIX) insert_username.sql
1417

1518
CLEANFILES+= $(TARGETS)
1619

1720
all:: $(TARGETS)
1821

22+
install:: all $(CONTRIBDIR)
23+
$(INSTALL) -c README $(CONTRIBDIR)/README.spi
24+
for f in *.example *.sql *$(DLSUFFIX); do $(INSTALL) -c $$f $(CONTRIBDIR)/$$f; done
25+
26+
$(CONTRIBDIR):
27+
mkdir -p $(CONTRIBDIR)
28+
1929
%.sql: %.source
2030
rm -f $@; \
2131
C=`pwd`; \
22-
sed -e "s:_OBJWD_:$$C:g" \
32+
sed -e "s:_OBJWD_:$(CONTRIBDIR):g" \
2333
-e "s:_DLSUFFIX_:$(DLSUFFIX):g" < $< > $@
2434

2535
clean:

‎contrib/spi/README

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -122,3 +122,16 @@ as many column/sequence pairs as you need).
122122
autoinc.source).
123123

124124

125+
4. insert_username.c - function for inserting user names.
126+
127+
You have to create BEFORE INSERT OR UPDATE trigger using the function
128+
insert_username(). You have to specify as a function argument: the column
129+
name (of text type) in which user names will be inserted. Note that user
130+
names will be inserted irregardless of the initial value of the field, so
131+
that users cannot bypass this functionality by simply defining the field to
132+
be NOT NULL.
133+
134+
There is an example in insert_username.example.
135+
136+
To CREATE FUNCTION use insert_username.sql (will be made by gmake from
137+
insert_username.source).

‎contrib/spi/insert_username.c

Lines changed: 77 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,77 @@
1+
/*
2+
* insert_username.c
3+
* $Modified: Thu Oct 16 08:13:42 1997 by brook $
4+
*
5+
* insert user name in response to a trigger
6+
* usage: insert_username (column_name)
7+
*/
8+
9+
#include "executor/spi.h" /* this is what you need to work with SPI */
10+
#include "commands/trigger.h" /* -"- and triggers */
11+
#include "miscadmin.h" /* for GetPgUserName() */
12+
13+
HeapTuple insert_username (void);
14+
15+
HeapTuple
16+
insert_username ()
17+
{
18+
Trigger *trigger; /* to get trigger name */
19+
int nargs; /* # of arguments */
20+
Datum newval; /* new value of column */
21+
char **args; /* arguments */
22+
char *relname; /* triggered relation name */
23+
Relation rel; /* triggered relation */
24+
HeapTuple rettuple = NULL;
25+
TupleDesc tupdesc; /* tuple description */
26+
int attnum;
27+
28+
/* sanity checks from autoinc.c */
29+
if (!CurrentTriggerData)
30+
elog(WARN, "insert_username: triggers are not initialized");
31+
if (TRIGGER_FIRED_FOR_STATEMENT(CurrentTriggerData->tg_event))
32+
elog(WARN, "insert_username: can't process STATEMENT events");
33+
if (TRIGGER_FIRED_AFTER(CurrentTriggerData->tg_event))
34+
elog(WARN, "insert_username: must be fired before event");
35+
36+
if (TRIGGER_FIRED_BY_INSERT(CurrentTriggerData->tg_event))
37+
rettuple = CurrentTriggerData->tg_trigtuple;
38+
else if (TRIGGER_FIRED_BY_UPDATE(CurrentTriggerData->tg_event))
39+
rettuple = CurrentTriggerData->tg_newtuple;
40+
else
41+
elog(WARN, "insert_username: can't process DELETE events");
42+
43+
rel = CurrentTriggerData->tg_relation;
44+
relname = SPI_getrelname(rel);
45+
46+
trigger = CurrentTriggerData->tg_trigger;
47+
48+
nargs = trigger->tgnargs;
49+
if (nargs != 1)
50+
elog(WARN, "insert_username (%s): one argument was expected", relname);
51+
52+
args = trigger->tgargs;
53+
tupdesc = rel->rd_att;
54+
55+
CurrentTriggerData = NULL;
56+
57+
attnum = SPI_fnumber (tupdesc, args[0]);
58+
59+
if ( attnum < 0 )
60+
elog(WARN, "insert_username (%s): there is no attribute %s", relname, args[0]);
61+
if (SPI_gettypeid (tupdesc, attnum) != TEXTOID)
62+
elog(WARN, "insert_username (%s): attribute %s must be of TEXT type",
63+
relname, args[0]);
64+
65+
/* create fields containing name */
66+
newval = PointerGetDatum (textin (GetPgUserName ()));
67+
68+
/* construct new tuple */
69+
rettuple = SPI_modifytuple (rel, rettuple, 1, &attnum, &newval, NULL);
70+
if ( rettuple == NULL )
71+
elog (WARN, "insert_username (%s): %d returned by SPI_modifytuple",
72+
relname, SPI_result);
73+
74+
pfree (relname);
75+
76+
return (rettuple);
77+
}

‎contrib/spi/insert_username.example

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,21 @@
1+
DROP TABLE username_test;
2+
3+
CREATE TABLE username_test (
4+
name text,
5+
username text not null
6+
);
7+
8+
CREATE TRIGGER insert_usernames
9+
BEFORE INSERT OR UPDATE ON username_test
10+
FOR EACH ROW
11+
EXECUTE PROCEDURE insert_username (username);
12+
13+
INSERT INTO username_test VALUES ('nothing');
14+
INSERT INTO username_test VALUES ('null', null);
15+
INSERT INTO username_test VALUES ('empty string', '');
16+
INSERT INTO username_test VALUES ('space', ' ');
17+
INSERT INTO username_test VALUES ('tab', ' ');
18+
INSERT INTO username_test VALUES ('name', 'name');
19+
20+
SELECT * FROM username_test;
21+

‎contrib/spi/insert_username.source

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,6 @@
1+
DROP FUNCTION insert_username();
2+
3+
CREATE FUNCTION insert_username()
4+
RETURNS opaque
5+
AS '_OBJWD_/insert_username_DLSUFFIX_'
6+
LANGUAGE 'c';

0 commit comments

Comments
 (0)