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

Commitc13454e

Browse files
committed
Timetravel tests.
1 parent7627154 commitc13454e

File tree

4 files changed

+209
-3
lines changed

4 files changed

+209
-3
lines changed

‎src/test/regress/expected/triggers.out

Lines changed: 119 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -159,3 +159,122 @@ count
159159
(1 row)
160160

161161
QUERY: DROP TABLE dup17;
162+
QUERY: create sequence ttdummy_seq increment 10 start 0 minvalue 0;
163+
QUERY: create table tttest (
164+
price_idint4,
165+
price_valint4,
166+
price_onint4 default nextval('ttdummy_seq'),
167+
price_offint4 default 999999
168+
);
169+
QUERY: insert into tttest values (1, 1, null, null);
170+
QUERY: insert into tttest values (2, 2, null, null);
171+
QUERY: insert into tttest values (3, 3, null, null);
172+
QUERY: create trigger ttdummy
173+
before delete or update on tttest
174+
for each row
175+
execute procedure
176+
ttdummy (price_on, price_off);
177+
QUERY: select * from tttest;
178+
price_id|price_val|price_on|price_off
179+
--------+---------+--------+---------
180+
1| 1| 0| 999999
181+
2| 2| 10| 999999
182+
3| 3| 20| 999999
183+
(3 rows)
184+
185+
QUERY: delete from tttest where price_id = 2;
186+
QUERY: select * from tttest;
187+
price_id|price_val|price_on|price_off
188+
--------+---------+--------+---------
189+
1| 1| 0| 999999
190+
3| 3| 20| 999999
191+
2| 2| 10| 30
192+
(3 rows)
193+
194+
QUERY: select * from tttest where price_off = 999999;
195+
price_id|price_val|price_on|price_off
196+
--------+---------+--------+---------
197+
1| 1| 0| 999999
198+
3| 3| 20| 999999
199+
(2 rows)
200+
201+
QUERY: update tttest set price_val = 30 where price_id = 3;
202+
QUERY: select * from tttest;
203+
price_id|price_val|price_on|price_off
204+
--------+---------+--------+---------
205+
1| 1| 0| 999999
206+
2| 2| 10| 30
207+
3| 30| 40| 999999
208+
3| 3| 20| 40
209+
(4 rows)
210+
211+
QUERY: update tttest set price_id = 5 where price_id = 3;
212+
QUERY: select * from tttest;
213+
price_id|price_val|price_on|price_off
214+
--------+---------+--------+---------
215+
1| 1| 0| 999999
216+
2| 2| 10| 30
217+
3| 3| 20| 40
218+
5| 30| 50| 999999
219+
3| 30| 40| 50
220+
(5 rows)
221+
222+
QUERY: select set_ttdummy(0);
223+
set_ttdummy
224+
-----------
225+
1
226+
(1 row)
227+
228+
QUERY: delete from tttest where price_id = 5;
229+
QUERY: update tttest set price_off = 999999 where price_val = 30;
230+
QUERY: select * from tttest;
231+
price_id|price_val|price_on|price_off
232+
--------+---------+--------+---------
233+
1| 1| 0| 999999
234+
2| 2| 10| 30
235+
3| 3| 20| 40
236+
3| 30| 40| 999999
237+
(4 rows)
238+
239+
QUERY: update tttest set price_id = 5 where price_id = 3;
240+
QUERY: select * from tttest;
241+
price_id|price_val|price_on|price_off
242+
--------+---------+--------+---------
243+
1| 1| 0| 999999
244+
2| 2| 10| 30
245+
5| 3| 20| 40
246+
5| 30| 40| 999999
247+
(4 rows)
248+
249+
QUERY: select set_ttdummy(1);
250+
set_ttdummy
251+
-----------
252+
0
253+
(1 row)
254+
255+
QUERY: update tttest set price_on = -1 where price_id = 1;
256+
WARN:ttdummy (tttest): you can't change price_on and/or price_off columns (use set_ttdummy)
257+
QUERY: select set_ttdummy(0);
258+
set_ttdummy
259+
-----------
260+
1
261+
(1 row)
262+
263+
QUERY: update tttest set price_on = -1 where price_id = 1;
264+
QUERY: select * from tttest;
265+
price_id|price_val|price_on|price_off
266+
--------+---------+--------+---------
267+
2| 2| 10| 30
268+
5| 3| 20| 40
269+
5| 30| 40| 999999
270+
1| 1| -1| 999999
271+
(4 rows)
272+
273+
QUERY: select * from tttest where price_on <= 25 and price_off > 25 and price_id = 5;
274+
price_id|price_val|price_on|price_off
275+
--------+---------+--------+---------
276+
5| 3| 20| 40
277+
(1 row)
278+
279+
QUERY: drop table tttest;
280+
QUERY: drop sequence ttdummy_seq;

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

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -30,3 +30,15 @@ CREATE FUNCTION funny_dup17 ()
3030
AS '_OBJWD_/regress_DLSUFFIX_'
3131
LANGUAGE 'c'
3232
;
33+
34+
CREATE FUNCTION ttdummy ()
35+
RETURNS opaque
36+
AS '_OBJWD_/regress_DLSUFFIX_'
37+
LANGUAGE 'c'
38+
;
39+
40+
CREATE FUNCTION set_ttdummy (int4)
41+
RETURNS int4
42+
AS '_OBJWD_/regress_DLSUFFIX_'
43+
LANGUAGE 'c'
44+
;

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

Lines changed: 13 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -9,16 +9,26 @@ QUERY: CREATE FUNCTION widget_out(opaque)
99
LANGUAGE 'c';
1010
QUERY: CREATE FUNCTION check_primary_key ()
1111
RETURNS opaque
12-
AS '_OBJWD_/../../../contrib/spi/refint.so'
12+
AS '_OBJWD_/../../../contrib/spi/refint_DLSUFFIX_'
1313
LANGUAGE 'c'
1414
;
1515
QUERY: CREATE FUNCTION check_foreign_key ()
1616
RETURNS opaque
17-
AS '_OBJWD_/../../../contrib/spi/refint.so'
17+
AS '_OBJWD_/../../../contrib/spi/refint_DLSUFFIX_'
1818
LANGUAGE 'c'
1919
;
2020
QUERY: CREATE FUNCTION funny_dup17 ()
2121
RETURNS opaque
22-
AS '_OBJWD_/regress.so'
22+
AS '_OBJWD_/regress_DLSUFFIX_'
23+
LANGUAGE 'c'
24+
;
25+
QUERY: CREATE FUNCTION ttdummy ()
26+
RETURNS opaque
27+
AS '_OBJWD_/regress_DLSUFFIX_'
28+
LANGUAGE 'c'
29+
;
30+
QUERY: CREATE FUNCTION set_ttdummy (int4)
31+
RETURNS int4
32+
AS '_OBJWD_/regress_DLSUFFIX_'
2333
LANGUAGE 'c'
2434
;

‎src/test/regress/sql/triggers.sql

Lines changed: 65 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -116,3 +116,68 @@ insert into dup17 values (13);
116116
selectcount(*)from dup17where x=13;
117117

118118
DROPTABLE dup17;
119+
120+
createsequencettdummy_seq increment10 start0 minvalue0;
121+
122+
createtabletttest (
123+
price_idint4,
124+
price_valint4,
125+
price_onint4 default nextval('ttdummy_seq'),
126+
price_offint4 default999999
127+
);
128+
129+
insert into tttestvalues (1,1,null,null);
130+
insert into tttestvalues (2,2,null,null);
131+
insert into tttestvalues (3,3,null,null);
132+
133+
createtriggerttdummy
134+
beforedeleteorupdateon tttest
135+
for each row
136+
execute procedure
137+
ttdummy (price_on, price_off);
138+
139+
select*from tttest;
140+
deletefrom tttestwhere price_id=2;
141+
select*from tttest;
142+
-- what do we see ?
143+
144+
-- get current prices
145+
select*from tttestwhere price_off=999999;
146+
147+
-- change price for price_id == 3
148+
update tttestset price_val=30where price_id=3;
149+
select*from tttest;
150+
151+
-- now we want to change pric_id in ALL tuples
152+
-- this gets us not what we need
153+
update tttestset price_id=5where price_id=3;
154+
select*from tttest;
155+
156+
-- restore data as before last update:
157+
select set_ttdummy(0);
158+
deletefrom tttestwhere price_id=5;
159+
update tttestset price_off=999999where price_val=30;
160+
select*from tttest;
161+
162+
-- and try change price_id now!
163+
update tttestset price_id=5where price_id=3;
164+
select*from tttest;
165+
-- isn't it what we need ?
166+
167+
select set_ttdummy(1);
168+
169+
-- we want to correct some "date"
170+
update tttestset price_on=-1where price_id=1;
171+
-- but this doesn't work
172+
173+
-- try in this way
174+
select set_ttdummy(0);
175+
update tttestset price_on=-1where price_id=1;
176+
select*from tttest;
177+
-- isn't it what we need ?
178+
179+
-- get price for price_id == 5 as it was @ "date" 25
180+
select*from tttestwhere price_on<=25and price_off>25and price_id=5;
181+
182+
droptable tttest;
183+
dropsequence ttdummy_seq;

0 commit comments

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