Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit14f9dde

Browse files
committed
New moddatetime contrib from Terry Mackintosh.
1 parent0d5a08f commit14f9dde

File tree

4 files changed

+140
-1
lines changed

4 files changed

+140
-1
lines changed

‎contrib/spi/Makefile

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -12,7 +12,7 @@ CFLAGS+= -DREFINT_VERBOSE
1212
endif
1313

1414
TARGETS= refint$(DLSUFFIX) refint.sql timetravel$(DLSUFFIX) timetravel.sql\
15-
autoinc$(DLSUFFIX) autoinc.sql\
15+
autoinc$(DLSUFFIX) autoinc.sqlmoddatetime$(DLSUFFIX) moddatetime.sql\
1616
insert_username$(DLSUFFIX) insert_username.sql
1717

1818
CLEANFILES+=$(TARGETS)

‎contrib/spi/moddatetime.c

Lines changed: 106 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,106 @@
1+
/*
2+
moddatetime.c
3+
4+
What is this?
5+
It is a function to be called from a trigger for the perpose of updating
6+
a modification datetime stamp in a record when that record is UPDATEd.
7+
8+
Credits
9+
This is 95%+ based on autoinc.c, which I used as a starting point as I do
10+
not really know what I am doing. I also had help from
11+
Jan Wieck <jwieck@debis.com> who told me about the datetime_in("now") function.
12+
OH, me, I'm Terry Mackintosh <terry@terrym.com>
13+
*/
14+
15+
#include"executor/spi.h"/* this is what you need to work with SPI */
16+
#include"commands/trigger.h"/* -"- and triggers */
17+
18+
HeapTuplemoddatetime(void);
19+
20+
HeapTuplemoddatetime()
21+
{
22+
Trigger*trigger;/* to get trigger name */
23+
intnargs;/* # of arguments */
24+
intattnum;/* positional number of field to change */
25+
Datumnewdt;/* The current datetime. */
26+
char**args;/* arguments */
27+
char*relname;/* triggered relation name */
28+
Relationrel;/* triggered relation */
29+
HeapTuplerettuple=NULL;
30+
TupleDesctupdesc;/* tuple description */
31+
32+
if (!CurrentTriggerData)
33+
elog(ERROR,"moddatetime: triggers are not initialized.");
34+
35+
if (TRIGGER_FIRED_FOR_STATEMENT(CurrentTriggerData->tg_event))
36+
elog(ERROR,"moddatetime: can't process STATEMENT events.");
37+
38+
if (TRIGGER_FIRED_AFTER(CurrentTriggerData->tg_event))
39+
elog(ERROR,"moddatetime: must be fired before event.");
40+
41+
if (TRIGGER_FIRED_BY_INSERT(CurrentTriggerData->tg_event))
42+
elog(ERROR,"moddatetime: must be fired before event.");
43+
elseif (TRIGGER_FIRED_BY_UPDATE(CurrentTriggerData->tg_event))
44+
rettuple=CurrentTriggerData->tg_newtuple;
45+
else
46+
elog(ERROR,"moddatetime: can't process DELETE events.");
47+
48+
rel=CurrentTriggerData->tg_relation;
49+
relname=SPI_getrelname(rel);
50+
51+
trigger=CurrentTriggerData->tg_trigger;
52+
53+
nargs=trigger->tgnargs;
54+
55+
if (nargs!=1)
56+
elog(ERROR,"moddatetime (%s): A single argument was expected.",relname);
57+
58+
args=trigger->tgargs;
59+
/* must be the field layout? */
60+
tupdesc=rel->rd_att;
61+
62+
/* Why do this? */
63+
CurrentTriggerData=NULL;
64+
65+
/* Get the current datetime. */
66+
newdt=datetime_in("now");
67+
68+
/* This gets the position in the turple of the field we want.
69+
args[0] being the name of the field to update, as passed in
70+
from the trigger.
71+
*/
72+
attnum=SPI_fnumber(tupdesc,args[0]);
73+
74+
/* This is were we check to see if the feild we are suppost to update even
75+
exits. The above function must return -1 if name not found?
76+
*/
77+
if (attnum<0)
78+
elog(ERROR,"moddatetime (%s): there is no attribute %s",relname,
79+
args[0]);
80+
81+
/* OK, this is where we make sure the datetime field that we are
82+
modifying is really a datetime field.
83+
Hay, error checking, what a novel idea !-)
84+
*/
85+
if (SPI_gettypeid(tupdesc,attnum)!=DATETIMEOID )
86+
elog(ERROR,"moddatetime (%s): attribute %s must be of DATETIME type",
87+
relname,args[0]);
88+
89+
/* 1 is the number of items in the arrays attnum and newdt.
90+
attnum is the positional number of the field to be updated.
91+
newdt is the new datetime stamp.
92+
NOTE that attnum and newdt are not arrays, but then a 1 ellement array
93+
is not an array any more then they are. Thus, they can be considered a
94+
one element array.
95+
*/
96+
rettuple=SPI_modifytuple(rel,rettuple,1,&attnum,&newdt,NULL);
97+
98+
if (rettuple==NULL)
99+
elog(ERROR,"moddatetime (%s): %d returned by SPI_modifytuple",
100+
relname,SPI_result);
101+
102+
/* Clean up */
103+
pfree(relname);
104+
105+
return (rettuple);
106+
}

‎contrib/spi/moddatetime.example

Lines changed: 27 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,27 @@
1+
DROP TABLE mdt;
2+
3+
CREATE TABLE mdt (
4+
idint4,
5+
idesctext,
6+
moddate datetime DEFAULT datetime(CURRENT_TIMESTAMP) NOT NULL
7+
);
8+
9+
CREATE TRIGGER mdt_moddatetime
10+
BEFORE UPDATE ON mdt
11+
FOR EACH ROW
12+
EXECUTE PROCEDURE moddatetime (moddate);
13+
14+
INSERT INTO mdt VALUES (1, 'first');
15+
INSERT INTO mdt VALUES (2, 'second');
16+
INSERT INTO mdt VALUES (3, 'third');
17+
18+
SELECT * FROM mdt;
19+
20+
UPDATE mdt SET id = 4
21+
WHERE id = 1;
22+
UPDATE mdt SET id = 5
23+
WHERE id = 2;
24+
UPDATE mdt SET id = 6
25+
WHERE id = 3;
26+
27+
SELECT * FROM mdt;

‎contrib/spi/moddatetime.source

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

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp