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

Commit2f4d0d6

Browse files
committed
Extend plsample example to include a trigger handler.
Mark Wong and Konstantina Skovola, reviewed by Chapman FlackDiscussion:https://postgr.es/m/Yd8Cz22eHi80XS30@workstation-mark-wong
1 parent9f8a050 commit2f4d0d6

File tree

3 files changed

+274
-3
lines changed

3 files changed

+274
-3
lines changed

‎src/test/modules/plsample/expected/plsample.out

Lines changed: 81 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -34,3 +34,84 @@ NOTICE: argument: 0; name: a1; value: {foo,bar,hoge}
3434

3535
(1 row)
3636

37+
CREATE FUNCTION my_trigger_func() RETURNS trigger AS $$
38+
if TD_event == "INSERT"
39+
return TD_NEW
40+
elseif TD_event == "UPDATE"
41+
return TD_NEW
42+
else
43+
return "OK"
44+
end
45+
$$ language plsample;
46+
CREATE TABLE my_table (num integer, description text);
47+
CREATE TRIGGER my_trigger_func BEFORE INSERT OR UPDATE ON my_table
48+
FOR EACH ROW EXECUTE FUNCTION my_trigger_func();
49+
CREATE TRIGGER my_trigger_func2 AFTER INSERT OR UPDATE ON my_table
50+
FOR EACH ROW EXECUTE FUNCTION my_trigger_func(8);
51+
INSERT INTO my_table (num, description)
52+
VALUES (1, 'first');
53+
NOTICE: source text of function "my_trigger_func":
54+
if TD_event == "INSERT"
55+
return TD_NEW
56+
elseif TD_event == "UPDATE"
57+
return TD_NEW
58+
else
59+
return "OK"
60+
end
61+
62+
NOTICE: trigger name: my_trigger_func
63+
NOTICE: trigger relation: my_table
64+
NOTICE: trigger relation schema: public
65+
NOTICE: triggered by INSERT
66+
NOTICE: triggered BEFORE
67+
NOTICE: triggered per row
68+
NOTICE: source text of function "my_trigger_func":
69+
if TD_event == "INSERT"
70+
return TD_NEW
71+
elseif TD_event == "UPDATE"
72+
return TD_NEW
73+
else
74+
return "OK"
75+
end
76+
77+
NOTICE: trigger name: my_trigger_func2
78+
NOTICE: trigger relation: my_table
79+
NOTICE: trigger relation schema: public
80+
NOTICE: triggered by INSERT
81+
NOTICE: triggered AFTER
82+
NOTICE: triggered per row
83+
NOTICE: trigger arg[0]: 8
84+
UPDATE my_table
85+
SET description = 'first, modified once'
86+
WHERE num = 1;
87+
NOTICE: source text of function "my_trigger_func":
88+
if TD_event == "INSERT"
89+
return TD_NEW
90+
elseif TD_event == "UPDATE"
91+
return TD_NEW
92+
else
93+
return "OK"
94+
end
95+
96+
NOTICE: trigger name: my_trigger_func
97+
NOTICE: trigger relation: my_table
98+
NOTICE: trigger relation schema: public
99+
NOTICE: triggered by UPDATE
100+
NOTICE: triggered BEFORE
101+
NOTICE: triggered per row
102+
NOTICE: source text of function "my_trigger_func":
103+
if TD_event == "INSERT"
104+
return TD_NEW
105+
elseif TD_event == "UPDATE"
106+
return TD_NEW
107+
else
108+
return "OK"
109+
end
110+
111+
NOTICE: trigger name: my_trigger_func2
112+
NOTICE: trigger relation: my_table
113+
NOTICE: trigger relation schema: public
114+
NOTICE: triggered by UPDATE
115+
NOTICE: triggered AFTER
116+
NOTICE: triggered per row
117+
NOTICE: trigger arg[0]: 8

‎src/test/modules/plsample/plsample.c

Lines changed: 170 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -19,6 +19,7 @@
1919
#include"catalog/pg_type.h"
2020
#include"commands/event_trigger.h"
2121
#include"commands/trigger.h"
22+
#include"executor/spi.h"
2223
#include"funcapi.h"
2324
#include"utils/builtins.h"
2425
#include"utils/lsyscache.h"
@@ -29,6 +30,7 @@ PG_MODULE_MAGIC;
2930
PG_FUNCTION_INFO_V1(plsample_call_handler);
3031

3132
staticDatumplsample_func_handler(PG_FUNCTION_ARGS);
33+
staticHeapTupleplsample_trigger_handler(PG_FUNCTION_ARGS);
3234

3335
/*
3436
* Handle function, procedure, and trigger calls.
@@ -38,6 +40,11 @@ plsample_call_handler(PG_FUNCTION_ARGS)
3840
{
3941
Datumretval= (Datum)0;
4042

43+
/*
44+
* Many languages will require cleanup that happens even in the event of
45+
* an error. That can happen in the PG_FINALLY block. If none is needed,
46+
* this PG_TRY construct can be omitted.
47+
*/
4148
PG_TRY();
4249
{
4350
/*
@@ -51,13 +58,16 @@ plsample_call_handler(PG_FUNCTION_ARGS)
5158
* (TriggerData *) fcinfo->context includes the information of the
5259
* context.
5360
*/
61+
retval=PointerGetDatum(plsample_trigger_handler(fcinfo));
5462
}
5563
elseif (CALLED_AS_EVENT_TRIGGER(fcinfo))
5664
{
5765
/*
5866
* This function is called as an event trigger function, where
5967
* (EventTriggerData *) fcinfo->context includes the information
6068
* of the context.
69+
*
70+
* TODO: provide an example handler.
6171
*/
6272
}
6373
else
@@ -101,9 +111,9 @@ plsample_func_handler(PG_FUNCTION_ARGS)
101111
FmgrInforesult_in_func;
102112
intnumargs;
103113

104-
/* Fetch thesource text of the function. */
105-
pl_tuple=SearchSysCache(PROCOID,
106-
ObjectIdGetDatum(fcinfo->flinfo->fn_oid),0,0,0);
114+
/* Fetch thefunction's pg_proc entry. */
115+
pl_tuple=SearchSysCache1(PROCOID,
116+
ObjectIdGetDatum(fcinfo->flinfo->fn_oid));
107117
if (!HeapTupleIsValid(pl_tuple))
108118
elog(ERROR,"cache lookup failed for function %u",
109119
fcinfo->flinfo->fn_oid);
@@ -185,3 +195,160 @@ plsample_func_handler(PG_FUNCTION_ARGS)
185195
ret=InputFunctionCall(&result_in_func,source,result_typioparam,-1);
186196
PG_RETURN_DATUM(ret);
187197
}
198+
199+
/*
200+
* plsample_trigger_handler
201+
*
202+
* Function called by the call handler for trigger execution.
203+
*/
204+
staticHeapTuple
205+
plsample_trigger_handler(PG_FUNCTION_ARGS)
206+
{
207+
TriggerData*trigdata= (TriggerData*)fcinfo->context;
208+
char*string;
209+
volatileHeapTuplerettup;
210+
HeapTuplepl_tuple;
211+
Datumret;
212+
char*source;
213+
boolisnull;
214+
Form_pg_procpl_struct;
215+
char*proname;
216+
intrcPG_USED_FOR_ASSERTS_ONLY;
217+
218+
/* Make sure this is being called from a trigger. */
219+
if (!CALLED_AS_TRIGGER(fcinfo))
220+
elog(ERROR,"not called by trigger manager");
221+
222+
/* Connect to the SPI manager */
223+
if (SPI_connect()!=SPI_OK_CONNECT)
224+
elog(ERROR,"could not connect to SPI manager");
225+
226+
rc=SPI_register_trigger_data(trigdata);
227+
Assert(rc >=0);
228+
229+
/* Fetch the function's pg_proc entry. */
230+
pl_tuple=SearchSysCache1(PROCOID,
231+
ObjectIdGetDatum(fcinfo->flinfo->fn_oid));
232+
if (!HeapTupleIsValid(pl_tuple))
233+
elog(ERROR,"cache lookup failed for function %u",
234+
fcinfo->flinfo->fn_oid);
235+
236+
/*
237+
* Code Retrieval
238+
*
239+
* Extract and print the source text of the function. This can be used as
240+
* a base for the function validation and execution.
241+
*/
242+
pl_struct= (Form_pg_proc)GETSTRUCT(pl_tuple);
243+
proname=pstrdup(NameStr(pl_struct->proname));
244+
ret=SysCacheGetAttr(PROCOID,pl_tuple,Anum_pg_proc_prosrc,&isnull);
245+
if (isnull)
246+
elog(ERROR,"could not find source text of function \"%s\"",
247+
proname);
248+
source=DatumGetCString(DirectFunctionCall1(textout,ret));
249+
ereport(NOTICE,
250+
(errmsg("source text of function \"%s\": %s",
251+
proname,source)));
252+
253+
/*
254+
* We're done with the pg_proc tuple, so release it. (Note that the
255+
* "proname" and "source" strings are now standalone copies.)
256+
*/
257+
ReleaseSysCache(pl_tuple);
258+
259+
/*
260+
* Code Augmentation
261+
*
262+
* The source text may be augmented here, such as by wrapping it as the
263+
* body of a function in the target language, prefixing a parameter list
264+
* with names like TD_name, TD_relid, TD_table_name, TD_table_schema,
265+
* TD_event, TD_when, TD_level, TD_NEW, TD_OLD, and args, using whatever
266+
* types in the target language are convenient. The augmented text can be
267+
* cached in a longer-lived memory context, or, if the target language
268+
* uses a compilation step, that can be done here, caching the result of
269+
* the compilation.
270+
*/
271+
272+
/*
273+
* Code Execution
274+
*
275+
* Here the function (the possibly-augmented source text, or the result of
276+
* compilation if the target language uses such a step) should be
277+
* executed, after binding values from the TriggerData struct to the
278+
* appropriate parameters.
279+
*
280+
* In this example we just print a lot of info via ereport.
281+
*/
282+
283+
PG_TRY();
284+
{
285+
ereport(NOTICE,
286+
(errmsg("trigger name: %s",trigdata->tg_trigger->tgname)));
287+
string=SPI_getrelname(trigdata->tg_relation);
288+
ereport(NOTICE, (errmsg("trigger relation: %s",string)));
289+
290+
string=SPI_getnspname(trigdata->tg_relation);
291+
ereport(NOTICE, (errmsg("trigger relation schema: %s",string)));
292+
293+
/* Example handling of different trigger aspects. */
294+
295+
if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event))
296+
{
297+
ereport(NOTICE, (errmsg("triggered by INSERT")));
298+
rettup=trigdata->tg_trigtuple;
299+
}
300+
elseif (TRIGGER_FIRED_BY_DELETE(trigdata->tg_event))
301+
{
302+
ereport(NOTICE, (errmsg("triggered by DELETE")));
303+
rettup=trigdata->tg_trigtuple;
304+
}
305+
elseif (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
306+
{
307+
ereport(NOTICE, (errmsg("triggered by UPDATE")));
308+
rettup=trigdata->tg_trigtuple;
309+
}
310+
elseif (TRIGGER_FIRED_BY_TRUNCATE(trigdata->tg_event))
311+
{
312+
ereport(NOTICE, (errmsg("triggered by TRUNCATE")));
313+
rettup=trigdata->tg_trigtuple;
314+
}
315+
else
316+
elog(ERROR,"unrecognized event: %u",trigdata->tg_event);
317+
318+
if (TRIGGER_FIRED_BEFORE(trigdata->tg_event))
319+
ereport(NOTICE, (errmsg("triggered BEFORE")));
320+
elseif (TRIGGER_FIRED_AFTER(trigdata->tg_event))
321+
ereport(NOTICE, (errmsg("triggered AFTER")));
322+
elseif (TRIGGER_FIRED_INSTEAD(trigdata->tg_event))
323+
ereport(NOTICE, (errmsg("triggered INSTEAD OF")));
324+
else
325+
elog(ERROR,"unrecognized when: %u",trigdata->tg_event);
326+
327+
if (TRIGGER_FIRED_FOR_ROW(trigdata->tg_event))
328+
ereport(NOTICE, (errmsg("triggered per row")));
329+
elseif (TRIGGER_FIRED_FOR_STATEMENT(trigdata->tg_event))
330+
ereport(NOTICE, (errmsg("triggered per statement")));
331+
else
332+
elog(ERROR,"unrecognized level: %u",trigdata->tg_event);
333+
334+
/*
335+
* Iterate through all of the trigger arguments, printing each input
336+
* value.
337+
*/
338+
for (inti=0;i<trigdata->tg_trigger->tgnargs;i++)
339+
ereport(NOTICE,
340+
(errmsg("trigger arg[%i]: %s",i,
341+
trigdata->tg_trigger->tgargs[i])));
342+
}
343+
PG_CATCH();
344+
{
345+
/* Error cleanup code would go here */
346+
PG_RE_THROW();
347+
}
348+
PG_END_TRY();
349+
350+
if (SPI_finish()!=SPI_OK_FINISH)
351+
elog(ERROR,"SPI_finish() failed");
352+
353+
returnrettup;
354+
}

‎src/test/modules/plsample/sql/plsample.sql

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -13,3 +13,26 @@ AS $$
1313
Example of source with void result.
1414
$$ LANGUAGE plsample;
1515
SELECT plsample_result_void('{foo, bar, hoge}');
16+
17+
CREATEFUNCTIONmy_trigger_func() RETURNS triggerAS $$
18+
if TD_event=="INSERT"
19+
return TD_NEW
20+
elseif TD_event=="UPDATE"
21+
return TD_NEW
22+
else
23+
return"OK"
24+
end
25+
$$ language plsample;
26+
27+
CREATETABLEmy_table (numinteger, descriptiontext);
28+
CREATETRIGGERmy_trigger_func BEFORE INSERTORUPDATEON my_table
29+
FOR EACH ROW EXECUTE FUNCTION my_trigger_func();
30+
CREATETRIGGERmy_trigger_func2 AFTER INSERTORUPDATEON my_table
31+
FOR EACH ROW EXECUTE FUNCTION my_trigger_func(8);
32+
33+
INSERT INTO my_table (num, description)
34+
VALUES (1,'first');
35+
36+
UPDATE my_table
37+
SET description='first, modified once'
38+
WHERE num=1;

0 commit comments

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