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

Commitba247bd

Browse files
committed
Tests for spi/triggers
1 parentb09114b commitba247bd

File tree

7 files changed

+428
-2
lines changed

7 files changed

+428
-2
lines changed

‎src/test/regress/GNUmakefile

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
#
88
#
99
# IDENTIFICATION
10-
# $Header: /cvsroot/pgsql/src/test/regress/GNUmakefile,v 1.9 1997/07/29 14:09:11 momjian Exp $
10+
# $Header: /cvsroot/pgsql/src/test/regress/GNUmakefile,v 1.10 1997/09/1109:13:27 vadim Exp $
1111
#
1212
#-------------------------------------------------------------------------
1313

@@ -43,6 +43,7 @@ endif
4343
all:$(INFILES)
4444
cd input;$(MAKE) all;cd ..
4545
cd output;$(MAKE) all;cd ..
46+
$(MAKE) -C ../../../contrib/spi REFINT_VERBOSE=1
4647

4748
#
4849
# run the test
Lines changed: 161 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,161 @@
1+
QUERY: create table pkeys (pkey1 int4 not null, pkey2 text not null);
2+
QUERY: create table fkeys (fkey1 int4, fkey2 text, fkey3 int);
3+
QUERY: create table fkeys2 (fkey21 int4, fkey22 text, pkey23 int not null);
4+
QUERY: create index fkeys_i on fkeys (fkey1, fkey2);
5+
QUERY: create index fkeys2_i on fkeys2 (fkey21, fkey22);
6+
QUERY: create index fkeys2p_i on fkeys2 (pkey23);
7+
QUERY: insert into pkeys values (10, '1');
8+
QUERY: insert into pkeys values (20, '2');
9+
QUERY: insert into pkeys values (30, '3');
10+
QUERY: insert into pkeys values (40, '4');
11+
QUERY: insert into pkeys values (50, '5');
12+
QUERY: insert into pkeys values (60, '6');
13+
QUERY: create unique index pkeys_i on pkeys (pkey1, pkey2);
14+
QUERY: create trigger check_fkeys_pkey_exist
15+
before insert or update on fkeys
16+
for each row
17+
execute procedure
18+
check_primary_key ('fkey1', 'fkey2', 'pkeys', 'pkey1', 'pkey2');
19+
QUERY: create trigger check_fkeys_pkey2_exist
20+
before insert or update on fkeys
21+
for each row
22+
execute procedure check_primary_key ('fkey3', 'fkeys2', 'pkey23');
23+
QUERY: create trigger check_fkeys2_pkey_exist
24+
before insert or update on fkeys2
25+
for each row
26+
execute procedure
27+
check_primary_key ('fkey21', 'fkey22', 'pkeys', 'pkey1', 'pkey2');
28+
QUERY: create trigger check_pkeys_fkey_cascade
29+
before delete or update on pkeys
30+
for each row
31+
execute procedure
32+
check_foreign_key (2, 'cascade', 'pkey1', 'pkey2',
33+
'fkeys', 'fkey1', 'fkey2', 'fkeys2', 'fkey21', 'fkey22');
34+
QUERY: create trigger check_fkeys2_fkey_restrict
35+
before delete or update on fkeys2
36+
for each row
37+
execute procedure check_foreign_key (1, 'restrict', 'pkey23', 'fkeys', 'fkey3');
38+
QUERY: insert into fkeys2 values (10, '1', 1);
39+
QUERY: insert into fkeys2 values (30, '3', 2);
40+
QUERY: insert into fkeys2 values (40, '4', 5);
41+
QUERY: insert into fkeys2 values (50, '5', 3);
42+
QUERY: insert into fkeys2 values (70, '5', 3);
43+
WARN:check_fkeys2_pkey_exist: tuple references non-existing key in pkeys
44+
QUERY: insert into fkeys values (10, '1', 2);
45+
QUERY: insert into fkeys values (30, '3', 3);
46+
QUERY: insert into fkeys values (40, '4', 2);
47+
QUERY: insert into fkeys values (50, '5', 2);
48+
QUERY: insert into fkeys values (70, '5', 1);
49+
WARN:check_fkeys_pkey_exist: tuple references non-existing key in pkeys
50+
QUERY: insert into fkeys values (60, '6', 4);
51+
WARN:check_fkeys_pkey2_exist: tuple references non-existing key in fkeys2
52+
QUERY: delete from pkeys where pkey1 = 30 and pkey2 = '3';
53+
NOTICE:check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted
54+
WARN:check_fkeys2_fkey_restrict: tuple referenced in fkeys
55+
QUERY: delete from pkeys where pkey1 = 40 and pkey2 = '4';
56+
NOTICE:check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted
57+
NOTICE:check_pkeys_fkey_cascade: 1 tuple(s) of fkeys2 are deleted
58+
QUERY: update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 50 and pkey2 = '5';
59+
NOTICE:check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted
60+
WARN:check_fkeys2_fkey_restrict: tuple referenced in fkeys
61+
QUERY: update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 10 and pkey2 = '1';
62+
NOTICE:check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted
63+
NOTICE:check_pkeys_fkey_cascade: 1 tuple(s) of fkeys2 are deleted
64+
QUERY: DROP TABLE pkeys;
65+
QUERY: DROP TABLE fkeys;
66+
QUERY: DROP TABLE fkeys2;
67+
QUERY: create table dup17 (x int4);
68+
QUERY: create trigger dup17_before
69+
before insert on dup17
70+
for each row
71+
execute procedure
72+
funny_dup17 ()
73+
;
74+
QUERY: insert into dup17 values (17);
75+
NOTICE:funny_dup17 (fired BEFORE) on level 1: 0/0 tuples inserted/selected
76+
QUERY: select count(*) from dup17;
77+
count
78+
-----
79+
1
80+
(1 row)
81+
82+
QUERY: insert into dup17 values (17);
83+
NOTICE:funny_dup17 (fired BEFORE) on level 17: 1/2 tuples inserted/selected
84+
NOTICE:funny_dup17 (fired BEFORE) on level 16: 1/3 tuples inserted/selected
85+
NOTICE:funny_dup17 (fired BEFORE) on level 15: 1/4 tuples inserted/selected
86+
NOTICE:funny_dup17 (fired BEFORE) on level 14: 1/5 tuples inserted/selected
87+
NOTICE:funny_dup17 (fired BEFORE) on level 13: 1/6 tuples inserted/selected
88+
NOTICE:funny_dup17 (fired BEFORE) on level 12: 1/7 tuples inserted/selected
89+
NOTICE:funny_dup17 (fired BEFORE) on level 11: 1/8 tuples inserted/selected
90+
NOTICE:funny_dup17 (fired BEFORE) on level 10: 1/9 tuples inserted/selected
91+
NOTICE:funny_dup17 (fired BEFORE) on level 9: 1/10 tuples inserted/selected
92+
NOTICE:funny_dup17 (fired BEFORE) on level 8: 1/11 tuples inserted/selected
93+
NOTICE:funny_dup17 (fired BEFORE) on level 7: 1/12 tuples inserted/selected
94+
NOTICE:funny_dup17 (fired BEFORE) on level 6: 1/13 tuples inserted/selected
95+
NOTICE:funny_dup17 (fired BEFORE) on level 5: 1/14 tuples inserted/selected
96+
NOTICE:funny_dup17 (fired BEFORE) on level 4: 1/15 tuples inserted/selected
97+
NOTICE:funny_dup17 (fired BEFORE) on level 3: 1/16 tuples inserted/selected
98+
NOTICE:funny_dup17 (fired BEFORE) on level 2: 1/17 tuples inserted/selected
99+
NOTICE:funny_dup17 (fired BEFORE) on level 1: 1/18 tuples inserted/selected
100+
QUERY: select count(*) from dup17;
101+
count
102+
-----
103+
19
104+
(1 row)
105+
106+
QUERY: drop trigger dup17_before on dup17;
107+
QUERY: create trigger dup17_after
108+
after insert on dup17
109+
for each row
110+
execute procedure
111+
funny_dup17 ()
112+
;
113+
QUERY: insert into dup17 values (13);
114+
NOTICE:funny_dup17 (fired AFTER ) on level 17: 17/34 tuples inserted/selected
115+
NOTICE:funny_dup17 (fired AFTER ) on level 16: 16/49 tuples inserted/selected
116+
NOTICE:funny_dup17 (fired AFTER ) on level 15: 15/63 tuples inserted/selected
117+
NOTICE:funny_dup17 (fired AFTER ) on level 14: 14/76 tuples inserted/selected
118+
NOTICE:funny_dup17 (fired AFTER ) on level 13: 13/88 tuples inserted/selected
119+
NOTICE:funny_dup17 (fired AFTER ) on level 12: 12/99 tuples inserted/selected
120+
NOTICE:funny_dup17 (fired AFTER ) on level 11: 11/109 tuples inserted/selected
121+
NOTICE:funny_dup17 (fired AFTER ) on level 10: 10/118 tuples inserted/selected
122+
NOTICE:funny_dup17 (fired AFTER ) on level 9: 9/126 tuples inserted/selected
123+
NOTICE:funny_dup17 (fired AFTER ) on level 8: 8/133 tuples inserted/selected
124+
NOTICE:funny_dup17 (fired AFTER ) on level 7: 7/139 tuples inserted/selected
125+
NOTICE:funny_dup17 (fired AFTER ) on level 6: 6/144 tuples inserted/selected
126+
NOTICE:funny_dup17 (fired AFTER ) on level 5: 5/148 tuples inserted/selected
127+
NOTICE:funny_dup17 (fired AFTER ) on level 4: 4/151 tuples inserted/selected
128+
NOTICE:funny_dup17 (fired AFTER ) on level 3: 3/153 tuples inserted/selected
129+
NOTICE:funny_dup17 (fired AFTER ) on level 2: 2/154 tuples inserted/selected
130+
NOTICE:funny_dup17 (fired AFTER ) on level 1: 1/154 tuples inserted/selected
131+
QUERY: select count(*) from dup17 where x = 13;
132+
count
133+
-----
134+
154
135+
(1 row)
136+
137+
QUERY: insert into dup17 values (13);
138+
NOTICE:funny_dup17 (fired AFTER ) on level 17: 171/342 tuples inserted/selected
139+
NOTICE:funny_dup17 (fired AFTER ) on level 16: 170/511 tuples inserted/selected
140+
NOTICE:funny_dup17 (fired AFTER ) on level 15: 169/679 tuples inserted/selected
141+
NOTICE:funny_dup17 (fired AFTER ) on level 14: 168/846 tuples inserted/selected
142+
NOTICE:funny_dup17 (fired AFTER ) on level 13: 167/1012 tuples inserted/selected
143+
NOTICE:funny_dup17 (fired AFTER ) on level 12: 166/1177 tuples inserted/selected
144+
NOTICE:funny_dup17 (fired AFTER ) on level 11: 165/1341 tuples inserted/selected
145+
NOTICE:funny_dup17 (fired AFTER ) on level 10: 164/1504 tuples inserted/selected
146+
NOTICE:funny_dup17 (fired AFTER ) on level 9: 163/1666 tuples inserted/selected
147+
NOTICE:funny_dup17 (fired AFTER ) on level 8: 162/1827 tuples inserted/selected
148+
NOTICE:funny_dup17 (fired AFTER ) on level 7: 161/1987 tuples inserted/selected
149+
NOTICE:funny_dup17 (fired AFTER ) on level 6: 160/2146 tuples inserted/selected
150+
NOTICE:funny_dup17 (fired AFTER ) on level 5: 159/2304 tuples inserted/selected
151+
NOTICE:funny_dup17 (fired AFTER ) on level 4: 158/2461 tuples inserted/selected
152+
NOTICE:funny_dup17 (fired AFTER ) on level 3: 157/2617 tuples inserted/selected
153+
NOTICE:funny_dup17 (fired AFTER ) on level 2: 156/2772 tuples inserted/selected
154+
NOTICE:funny_dup17 (fired AFTER ) on level 1: 155/2926 tuples inserted/selected
155+
QUERY: select count(*) from dup17 where x = 13;
156+
count
157+
-----
158+
2926
159+
(1 row)
160+
161+
QUERY: DROP TABLE dup17;

‎src/test/regress/input/create_function_1.source

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -13,3 +13,20 @@ CREATE FUNCTION widget_out(opaque)
1313
AS '_OBJWD_/regress_DLSUFFIX_'
1414
LANGUAGE 'c';
1515

16+
CREATE FUNCTION check_primary_key ()
17+
RETURNS opaque
18+
AS '_OBJWD_/../../../contrib/spi/refint_DLSUFFIX_'
19+
LANGUAGE 'c'
20+
;
21+
22+
CREATE FUNCTION check_foreign_key ()
23+
RETURNS opaque
24+
AS '_OBJWD_/../../../contrib/spi/refint_DLSUFFIX_'
25+
LANGUAGE 'c'
26+
;
27+
28+
CREATE FUNCTION funny_dup17 ()
29+
RETURNS opaque
30+
AS '_OBJWD_/regress_DLSUFFIX_'
31+
LANGUAGE 'c'
32+
;

‎src/test/regress/output/create_function_1.source

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -7,3 +7,18 @@ QUERY: CREATE FUNCTION widget_out(opaque)
77
RETURNS opaque
88
AS '_OBJWD_/regress_DLSUFFIX_'
99
LANGUAGE 'c';
10+
QUERY: CREATE FUNCTION check_primary_key ()
11+
RETURNS opaque
12+
AS '_OBJWD_/../../../contrib/spi/refint.so'
13+
LANGUAGE 'c'
14+
;
15+
QUERY: CREATE FUNCTION check_foreign_key ()
16+
RETURNS opaque
17+
AS '_OBJWD_/../../../contrib/spi/refint.so'
18+
LANGUAGE 'c'
19+
;
20+
QUERY: CREATE FUNCTION funny_dup17 ()
21+
RETURNS opaque
22+
AS '_OBJWD_/regress.so'
23+
LANGUAGE 'c'
24+
;

‎src/test/regress/regress.c

Lines changed: 114 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
/*
2-
* $Header: /cvsroot/pgsql/src/test/regress/regress.c,v 1.13 1997/09/08 21:56:01 momjian Exp $
2+
* $Header: /cvsroot/pgsql/src/test/regress/regress.c,v 1.14 1997/09/11 09:13:28 vadim Exp $
33
*/
44

55
#include<float.h>/* faked on sunos */
@@ -298,3 +298,116 @@ char *string;
298298
new_string[len-i]=string[i];
299299
return (new_string);
300300
}
301+
302+
#include"executor/spi.h"/* this is what you need to work with SPI */
303+
#include"commands/trigger.h"/* -"- and triggers */
304+
305+
staticTransactionIdfd17b_xid=InvalidTransactionId;
306+
staticTransactionIdfd17a_xid=InvalidTransactionId;
307+
staticintfd17b_level=0;
308+
staticintfd17a_level=0;
309+
staticboolfd17b_recursion= true;
310+
staticboolfd17a_recursion= true;
311+
HeapTuplefunny_dup17(void);
312+
313+
HeapTuple/* have to return HeapTuple to Executor */
314+
funny_dup17()
315+
{
316+
TransactionId*xid;
317+
int*level;
318+
bool*recursion;
319+
Relationrel;
320+
TupleDesctupdesc;
321+
HeapTupletuple;
322+
charsql[8192];
323+
char*when;
324+
intinserted;
325+
intselected=0;
326+
intret;
327+
328+
tuple=CurrentTriggerData->tg_trigtuple;
329+
rel=CurrentTriggerData->tg_relation;
330+
tupdesc=rel->rd_att;
331+
if (TRIGGER_FIRED_BEFORE(CurrentTriggerData->tg_event))
332+
{
333+
xid=&fd17b_xid;
334+
level=&fd17b_level;
335+
recursion=&fd17b_recursion;
336+
when="BEFORE";
337+
}
338+
else
339+
{
340+
xid=&fd17a_xid;
341+
level=&fd17a_level;
342+
recursion=&fd17a_recursion;
343+
when="AFTER ";
344+
}
345+
346+
CurrentTriggerData=NULL;
347+
348+
if (!TransactionIdIsCurrentTransactionId(*xid))
349+
{
350+
*xid=GetCurrentTransactionId();
351+
*level=0;
352+
*recursion= true;
353+
}
354+
355+
if (*level==17)
356+
{
357+
*recursion= false;
358+
return (tuple);
359+
}
360+
361+
if (!(*recursion))
362+
return (tuple);
363+
364+
(*level)++;
365+
366+
SPI_connect();
367+
368+
sprintf(sql,"insert into %s select * from %s where %s = '%s'::%s",
369+
SPI_getrelname(rel),SPI_getrelname(rel),
370+
SPI_fname(tupdesc,1),
371+
SPI_getvalue(tuple,tupdesc,1),
372+
SPI_gettype(tupdesc,1));
373+
374+
if ((ret=SPI_exec(sql,0))<0)
375+
elog(WARN,"funny_dup17 (fired %s) on level %3d: SPI_exec (insert ...) returned %d",
376+
when,*level,ret);
377+
378+
inserted=SPI_processed;
379+
380+
sprintf(sql,"select count (*) from %s where %s = '%s'::%s",
381+
SPI_getrelname(rel),
382+
SPI_fname(tupdesc,1),
383+
SPI_getvalue(tuple,tupdesc,1),
384+
SPI_gettype(tupdesc,1));
385+
386+
if ((ret=SPI_exec(sql,0))<0)
387+
elog(WARN,"funny_dup17 (fired %s) on level %3d: SPI_exec (select ...) returned %d",
388+
when,*level,ret);
389+
390+
if (SPI_processed>0)
391+
{
392+
selected=
393+
int4in(
394+
SPI_getvalue(
395+
SPI_tuptable->vals[0],
396+
SPI_tuptable->tupdesc,
397+
1
398+
)
399+
);
400+
}
401+
402+
elog(NOTICE,"funny_dup17 (fired %s) on level %3d: %d/%d tuples inserted/selected",
403+
when,*level,inserted,selected);
404+
405+
SPI_finish();
406+
407+
(*level)--;
408+
409+
if (*level==0)
410+
*xid=InvalidTransactionId;
411+
412+
return (tuple);
413+
}

‎src/test/regress/sql/tests

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -32,6 +32,7 @@ create_type
3232
create_table
3333
create_function_2
3434
constraints
35+
triggers
3536
copy
3637
create_misc
3738
create_aggregate

0 commit comments

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