From fcfb4d1df688ecf6003e96581c519f08a0dba596 Mon Sep 17 00:00:00 2001 From: "Vadim B. Mikheev" Date: Fri, 17 Oct 1997 09:59:09 +0000 Subject: [PATCH] Changes due to fixed DEFAULT behaviour. --- src/test/regress/GNUmakefile | 5 +- src/test/regress/data/constrf.data | 3 +- src/test/regress/data/constro.data | 5 +- src/test/regress/expected/triggers.out | 73 ++++----- src/test/regress/input/constraints.source | 46 +++--- .../regress/input/create_function_1.source | 6 + src/test/regress/output/constraints.source | 140 +++++++----------- .../regress/output/create_function_1.source | 5 + src/test/regress/sql/triggers.sql | 20 ++- 9 files changed, 148 insertions(+), 155 deletions(-) diff --git a/src/test/regress/GNUmakefile b/src/test/regress/GNUmakefile index 8dbcdba58b..08714cc450 100644 --- a/src/test/regress/GNUmakefile +++ b/src/test/regress/GNUmakefile @@ -7,7 +7,7 @@ # # # IDENTIFICATION -# $Header: /cvsroot/pgsql/src/test/regress/GNUmakefile,v 1.11 1997/09/24 08:35:07 vadim Exp $ +# $Header: /cvsroot/pgsql/src/test/regress/GNUmakefile,v 1.12 1997/10/17 09:57:49 vadim Exp $ # #------------------------------------------------------------------------- @@ -43,7 +43,8 @@ endif all: $(INFILES) cd input; $(MAKE) all; cd .. cd output; $(MAKE) all; cd .. - $(MAKE) -C ../../../contrib/spi REFINT_VERBOSE=1 refint$(DLSUFFIX) + $(MAKE) -C ../../../contrib/spi REFINT_VERBOSE=1 refint$(DLSUFFIX) \ + autoinc$(DLSUFFIX) # # run the test diff --git a/src/test/regress/data/constrf.data b/src/test/regress/data/constrf.data index fdf3cea38f..6ae26eb482 100644 --- a/src/test/regress/data/constrf.data +++ b/src/test/regress/data/constrf.data @@ -1 +1,2 @@ -\N \N \N +5 !check failed 6 +7 check failed 6 diff --git a/src/test/regress/data/constro.data b/src/test/regress/data/constro.data index f93f457803..00f8af4a30 100644 --- a/src/test/regress/data/constro.data +++ b/src/test/regress/data/constro.data @@ -1,3 +1,2 @@ -\N \N \N -\N \N \N -\N \N \N +4 !check failed 5 +6 OK 4 diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out index a52c48fdca..9cc97a4268 100644 --- a/src/test/regress/expected/triggers.out +++ b/src/test/regress/expected/triggers.out @@ -163,60 +163,65 @@ QUERY: create sequence ttdummy_seq increment 10 start 0 minvalue 0; QUERY: create table tttest ( price_id int4, price_val int4, - price_on int4 default nextval('ttdummy_seq'), + price_on int4, price_off int4 default 999999 ); -QUERY: insert into tttest values (1, 1, null, null); -QUERY: insert into tttest values (2, 2, null, null); -QUERY: insert into tttest values (3, 3, null, null); QUERY: create trigger ttdummy before delete or update on tttest for each row execute procedure ttdummy (price_on, price_off); +QUERY: create trigger ttserial + before insert or update on tttest + for each row + execute procedure + autoinc (price_on, ttdummy_seq); +QUERY: insert into tttest values (1, 1, null); +QUERY: insert into tttest values (2, 2, null); +QUERY: insert into tttest values (3, 3, 0); QUERY: select * from tttest; price_id|price_val|price_on|price_off --------+---------+--------+--------- - 1| 1| 0| 999999 - 2| 2| 10| 999999 - 3| 3| 20| 999999 + 1| 1| 10| 999999 + 2| 2| 20| 999999 + 3| 3| 30| 999999 (3 rows) QUERY: delete from tttest where price_id = 2; QUERY: select * from tttest; price_id|price_val|price_on|price_off --------+---------+--------+--------- - 1| 1| 0| 999999 - 3| 3| 20| 999999 - 2| 2| 10| 30 + 1| 1| 10| 999999 + 3| 3| 30| 999999 + 2| 2| 20| 40 (3 rows) QUERY: select * from tttest where price_off = 999999; price_id|price_val|price_on|price_off --------+---------+--------+--------- - 1| 1| 0| 999999 - 3| 3| 20| 999999 + 1| 1| 10| 999999 + 3| 3| 30| 999999 (2 rows) QUERY: update tttest set price_val = 30 where price_id = 3; QUERY: select * from tttest; price_id|price_val|price_on|price_off --------+---------+--------+--------- - 1| 1| 0| 999999 - 2| 2| 10| 30 - 3| 30| 40| 999999 - 3| 3| 20| 40 + 1| 1| 10| 999999 + 2| 2| 20| 40 + 3| 30| 50| 999999 + 3| 3| 30| 50 (4 rows) QUERY: update tttest set price_id = 5 where price_id = 3; QUERY: select * from tttest; price_id|price_val|price_on|price_off --------+---------+--------+--------- - 1| 1| 0| 999999 - 2| 2| 10| 30 - 3| 3| 20| 40 - 5| 30| 50| 999999 - 3| 30| 40| 50 + 1| 1| 10| 999999 + 2| 2| 20| 40 + 3| 3| 30| 50 + 5| 30| 60| 999999 + 3| 30| 50| 60 (5 rows) QUERY: select set_ttdummy(0); @@ -230,20 +235,20 @@ QUERY: update tttest set price_off = 999999 where price_val = 30; QUERY: select * from tttest; price_id|price_val|price_on|price_off --------+---------+--------+--------- - 1| 1| 0| 999999 - 2| 2| 10| 30 - 3| 3| 20| 40 - 3| 30| 40| 999999 + 1| 1| 10| 999999 + 2| 2| 20| 40 + 3| 3| 30| 50 + 3| 30| 50| 999999 (4 rows) QUERY: update tttest set price_id = 5 where price_id = 3; QUERY: select * from tttest; price_id|price_val|price_on|price_off --------+---------+--------+--------- - 1| 1| 0| 999999 - 2| 2| 10| 30 - 5| 3| 20| 40 - 5| 30| 40| 999999 + 1| 1| 10| 999999 + 2| 2| 20| 40 + 5| 3| 30| 50 + 5| 30| 50| 999999 (4 rows) QUERY: select set_ttdummy(1); @@ -264,16 +269,16 @@ QUERY: update tttest set price_on = -1 where price_id = 1; QUERY: select * from tttest; price_id|price_val|price_on|price_off --------+---------+--------+--------- - 2| 2| 10| 30 - 5| 3| 20| 40 - 5| 30| 40| 999999 + 2| 2| 20| 40 + 5| 3| 30| 50 + 5| 30| 50| 999999 1| 1| -1| 999999 (4 rows) -QUERY: select * from tttest where price_on <= 25 and price_off > 25 and price_id = 5; +QUERY: select * from tttest where price_on <= 35 and price_off > 35 and price_id = 5; price_id|price_val|price_on|price_off --------+---------+--------+--------- - 5| 3| 20| 40 + 5| 3| 30| 50 (1 row) QUERY: drop table tttest; diff --git a/src/test/regress/input/constraints.source b/src/test/regress/input/constraints.source index b2fea4ca43..88f6c26337 100644 --- a/src/test/regress/input/constraints.source +++ b/src/test/regress/input/constraints.source @@ -14,6 +14,7 @@ INSERT INTO DEFAULT_TBL VALUES (1, 'thomas', 57.0613); INSERT INTO DEFAULT_TBL VALUES (1, 'bruce'); INSERT INTO DEFAULT_TBL (i, f) VALUES (2, 987.654); INSERT INTO DEFAULT_TBL (x) VALUES ('tgl'); +INSERT INTO DEFAULT_TBL VALUES (3, null, 1.0); SELECT '' AS four, * FROM DEFAULT_TBL; @@ -66,28 +67,30 @@ CREATE SEQUENCE INSERT_SEQ; CREATE TABLE INSERT_TBL (x INT DEFAULT nextval('insert_seq'), y TEXT DEFAULT '-NULL-', z INT DEFAULT -1 * currval('insert_seq') ) -CONSTRAINT INSERT_CON CHECK (x > 3 AND y <> 'check failed' AND x < 8), +CONSTRAINT INSERT_CON CHECK (x >= 3 AND y <> 'check failed' AND x < 8), CHECK x + z = 0; INSERT INTO INSERT_TBL VALUES (null, null, null); -INSERT INTO INSERT_TBL VALUES (null, null, -2); +INSERT INTO INSERT_TBL(x,z) VALUES (2, -2); SELECT * FROM INSERT_TBL; SELECT nextval('insert_seq'); -INSERT INTO INSERT_TBL VALUES (null, null, null); -INSERT INTO INSERT_TBL VALUES (1, null, -2); -INSERT INTO INSERT_TBL VALUES (7, null, -7); +INSERT INTO INSERT_TBL(y) VALUES ('Y'); +INSERT INTO INSERT_TBL(y) VALUES ('Y'); +INSERT INTO INSERT_TBL(x,z) VALUES (1, -2); +INSERT INTO INSERT_TBL(z,x) VALUES (-7, 7); INSERT INTO INSERT_TBL VALUES (5, 'check failed', -5); INSERT INTO INSERT_TBL VALUES (7, '!check failed', -7); -INSERT INTO INSERT_TBL VALUES (null, null, null); +INSERT INTO INSERT_TBL(y) VALUES ('-!NULL-'); SELECT * FROM INSERT_TBL; -INSERT INTO INSERT_TBL VALUES (null, 'check failed', 5); -INSERT INTO INSERT_TBL VALUES (5, 'check failed', null); -INSERT INTO INSERT_TBL VALUES (5, '!check failed', null); -INSERT INTO INSERT_TBL VALUES (null, null, null); +INSERT INTO INSERT_TBL(y,z) VALUES ('check failed', 4); +INSERT INTO INSERT_TBL(x,y) VALUES (5, 'check failed'); +INSERT INTO INSERT_TBL(x,y) VALUES (5, '!check failed'); +INSERT INTO INSERT_TBL(y) VALUES ('-!NULL-'); SELECT * FROM INSERT_TBL; -INSERT INTO INSERT_TBL VALUES (null, null, null); +SELECT nextval('insert_seq'); +INSERT INTO INSERT_TBL(y) VALUES ('Y'); SELECT currval('insert_seq'); -- @@ -99,13 +102,14 @@ DROP SEQUENCE INSERT_SEQ; CREATE SEQUENCE INSERT_SEQ START 4; CREATE TABLE TEMP (xd INT, yd TEXT, zd INT); -SELECT nextval('insert_seq'); -INSERT INTO TEMP VALUES (null, null, null); +INSERT INTO TEMP VALUES (null, 'Y', null); INSERT INTO TEMP VALUES (5, '!check failed', null); INSERT INTO TEMP VALUES (null, 'try again', null); -INSERT INTO INSERT_TBL SELECT * FROM TEMP; +INSERT INTO INSERT_TBL(y) select yd from TEMP; SELECT * FROM INSERT_TBL; INSERT INTO INSERT_TBL SELECT * FROM TEMP WHERE yd = 'try again'; +INSERT INTO INSERT_TBL(y,z) SELECT yd, -7 FROM TEMP WHERE yd = 'try again'; +INSERT INTO INSERT_TBL(y,z) SELECT yd, -8 FROM TEMP WHERE yd = 'try again'; SELECT * FROM INSERT_TBL; DROP TABLE TEMP; @@ -115,28 +119,20 @@ DROP TABLE TEMP; -- UPDATE INSERT_TBL SET x = NULL WHERE x = 6; -SELECT currval('insert_seq'); -SELECT * FROM INSERT_TBL; - UPDATE INSERT_TBL SET x = 6 WHERE x = 6; -SELECT * FROM INSERT_TBL; - UPDATE INSERT_TBL SET x = -z, z = -x; +UPDATE INSERT_TBL SET x = z, z = x; SELECT * FROM INSERT_TBL; -- -- Check constraints on COPY FROM -- -CREATE SEQUENCE COPY_SEQ START 4; -CREATE TABLE COPY_TBL (x INT DEFAULT nextval('copy_seq'), - y TEXT DEFAULT '-NULL-', z INT DEFAULT -1 * currval('copy_seq') ) - CONSTRAINT COPY_CON CHECK (x > 3 AND y <> 'check failed' AND x < 7 ), - CHECK x + z = 0; +CREATE TABLE COPY_TBL (x INT, y TEXT, z INT) + CONSTRAINT COPY_CON CHECK (x > 3 AND y <> 'check failed' AND x < 7 ); COPY COPY_TBL FROM '_OBJWD_/data/constro.data'; SELECT * FROM COPY_TBL; COPY COPY_TBL FROM '_OBJWD_/data/constrf.data'; SELECT * FROM COPY_TBL; -select nextval('copy_seq') - 1 as currval; diff --git a/src/test/regress/input/create_function_1.source b/src/test/regress/input/create_function_1.source index 70c8676d90..13533c7874 100644 --- a/src/test/regress/input/create_function_1.source +++ b/src/test/regress/input/create_function_1.source @@ -25,6 +25,12 @@ CREATE FUNCTION check_foreign_key () LANGUAGE 'c' ; +CREATE FUNCTION autoinc () + RETURNS opaque + AS '_OBJWD_/../../../contrib/spi/autoinc_DLSUFFIX_' + LANGUAGE 'c' +; + CREATE FUNCTION funny_dup17 () RETURNS opaque AS '_OBJWD_/regress_DLSUFFIX_' diff --git a/src/test/regress/output/constraints.source b/src/test/regress/output/constraints.source index 173bd89b94..7d5a751ccd 100644 --- a/src/test/regress/output/constraints.source +++ b/src/test/regress/output/constraints.source @@ -4,6 +4,7 @@ QUERY: INSERT INTO DEFAULT_TBL VALUES (1, 'thomas', 57.0613); QUERY: INSERT INTO DEFAULT_TBL VALUES (1, 'bruce'); QUERY: INSERT INTO DEFAULT_TBL (i, f) VALUES (2, 987.654); QUERY: INSERT INTO DEFAULT_TBL (x) VALUES ('tgl'); +QUERY: INSERT INTO DEFAULT_TBL VALUES (3, null, 1.0); QUERY: SELECT '' AS four, * FROM DEFAULT_TBL; four| i|x | f ----+---+------+------- @@ -11,7 +12,8 @@ four| i|x | f | 1|bruce |123.456 | 2|vadim |987.654 |100|tgl |123.456 -(4 rows) + | 3| | 1 +(5 rows) QUERY: CREATE SEQUENCE DEFAULT_SEQ; QUERY: CREATE TABLE DEFAULTEXPR_TBL (i1 int DEFAULT 100 + (200-199) * 2, @@ -26,7 +28,7 @@ four| i1|i2 | -1|-2 | -3| 1 |102|-4 - |102| 2 + |102| (4 rows) QUERY: CREATE TABLE error_tbl (i int DEFAULT (100, )); @@ -59,11 +61,11 @@ QUERY: INSERT INTO CHECK2_TBL VALUES (7, 'check ok', 7); QUERY: CREATE SEQUENCE INSERT_SEQ; QUERY: CREATE TABLE INSERT_TBL (x INT DEFAULT nextval('insert_seq'), y TEXT DEFAULT '-NULL-', z INT DEFAULT -1 * currval('insert_seq') ) -CONSTRAINT INSERT_CON CHECK (x > 3 AND y <> 'check failed' AND x < 8), +CONSTRAINT INSERT_CON CHECK (x >= 3 AND y <> 'check failed' AND x < 8), CHECK x + z = 0; QUERY: INSERT INTO INSERT_TBL VALUES (null, null, null); -WARN:ExecAppend: rejected due to CHECK constraint insert_con -QUERY: INSERT INTO INSERT_TBL VALUES (null, null, -2); +WARN:ExecAppend: rejected due to CHECK constraint $2 +QUERY: INSERT INTO INSERT_TBL(x,z) VALUES (2, -2); WARN:ExecAppend: rejected due to CHECK constraint insert_con QUERY: SELECT * FROM INSERT_TBL; x|y|z @@ -73,44 +75,52 @@ x|y|z QUERY: SELECT nextval('insert_seq'); nextval ------- - 3 + 1 (1 row) -QUERY: INSERT INTO INSERT_TBL VALUES (null, null, null); -QUERY: INSERT INTO INSERT_TBL VALUES (1, null, -2); +QUERY: INSERT INTO INSERT_TBL(y) VALUES ('Y'); +WARN:ExecAppend: rejected due to CHECK constraint insert_con +QUERY: INSERT INTO INSERT_TBL(y) VALUES ('Y'); +QUERY: INSERT INTO INSERT_TBL(x,z) VALUES (1, -2); WARN:ExecAppend: rejected due to CHECK constraint $2 -QUERY: INSERT INTO INSERT_TBL VALUES (7, null, -7); +QUERY: INSERT INTO INSERT_TBL(z,x) VALUES (-7, 7); QUERY: INSERT INTO INSERT_TBL VALUES (5, 'check failed', -5); WARN:ExecAppend: rejected due to CHECK constraint insert_con QUERY: INSERT INTO INSERT_TBL VALUES (7, '!check failed', -7); -QUERY: INSERT INTO INSERT_TBL VALUES (null, null, null); +QUERY: INSERT INTO INSERT_TBL(y) VALUES ('-!NULL-'); QUERY: SELECT * FROM INSERT_TBL; x|y | z -+-------------+-- -4|-NULL- |-4 +3|Y |-3 7|-NULL- |-7 7|!check failed|-7 -5|-NULL- |-5 +4|-!NULL- |-4 (4 rows) -QUERY: INSERT INTO INSERT_TBL VALUES (null, 'check failed', 5); -WARN:ExecAppend: rejected due to CHECK constraint $2 -QUERY: INSERT INTO INSERT_TBL VALUES (5, 'check failed', null); +QUERY: INSERT INTO INSERT_TBL(y,z) VALUES ('check failed', 4); WARN:ExecAppend: rejected due to CHECK constraint $2 -QUERY: INSERT INTO INSERT_TBL VALUES (5, '!check failed', null); -WARN:ExecAppend: rejected due to CHECK constraint $2 -QUERY: INSERT INTO INSERT_TBL VALUES (null, null, null); +QUERY: INSERT INTO INSERT_TBL(x,y) VALUES (5, 'check failed'); +WARN:ExecAppend: rejected due to CHECK constraint insert_con +QUERY: INSERT INTO INSERT_TBL(x,y) VALUES (5, '!check failed'); +QUERY: INSERT INTO INSERT_TBL(y) VALUES ('-!NULL-'); QUERY: SELECT * FROM INSERT_TBL; x|y | z -+-------------+-- -4|-NULL- |-4 +3|Y |-3 7|-NULL- |-7 7|!check failed|-7 -5|-NULL- |-5 -7|-NULL- |-7 -(5 rows) +4|-!NULL- |-4 +5|!check failed|-5 +6|-!NULL- |-6 +(6 rows) -QUERY: INSERT INTO INSERT_TBL VALUES (null, null, null); +QUERY: SELECT nextval('insert_seq'); +nextval +------- + 7 +(1 row) + +QUERY: INSERT INTO INSERT_TBL(y) VALUES ('Y'); WARN:ExecAppend: rejected due to CHECK constraint insert_con QUERY: SELECT currval('insert_seq'); currval @@ -122,31 +132,28 @@ QUERY: DELETE FROM INSERT_TBL; QUERY: DROP SEQUENCE INSERT_SEQ; QUERY: CREATE SEQUENCE INSERT_SEQ START 4; QUERY: CREATE TABLE TEMP (xd INT, yd TEXT, zd INT); -QUERY: SELECT nextval('insert_seq'); -NOTICE:insert_seq.nextval: sequence was re-created - -nextval -------- - 4 -(1 row) - -QUERY: INSERT INTO TEMP VALUES (null, null, null); +QUERY: INSERT INTO TEMP VALUES (null, 'Y', null); QUERY: INSERT INTO TEMP VALUES (5, '!check failed', null); QUERY: INSERT INTO TEMP VALUES (null, 'try again', null); -QUERY: INSERT INTO INSERT_TBL SELECT * FROM TEMP; +QUERY: INSERT INTO INSERT_TBL(y) select yd from TEMP; +NOTICE:insert_seq.nextval: sequence was re-created QUERY: SELECT * FROM INSERT_TBL; x|y | z -+-------------+-- -5|-NULL- |-5 +4|Y |-4 5|!check failed|-5 6|try again |-6 (3 rows) QUERY: INSERT INTO INSERT_TBL SELECT * FROM TEMP WHERE yd = 'try again'; +WARN:ExecAppend: rejected due to CHECK constraint $2 +QUERY: INSERT INTO INSERT_TBL(y,z) SELECT yd, -7 FROM TEMP WHERE yd = 'try again'; +QUERY: INSERT INTO INSERT_TBL(y,z) SELECT yd, -8 FROM TEMP WHERE yd = 'try again'; +WARN:ExecAppend: rejected due to CHECK constraint insert_con QUERY: SELECT * FROM INSERT_TBL; x|y | z -+-------------+-- -5|-NULL- |-5 +4|Y |-4 5|!check failed|-5 6|try again |-6 7|try again |-7 @@ -155,68 +162,35 @@ x|y | z QUERY: DROP TABLE TEMP; QUERY: UPDATE INSERT_TBL SET x = NULL WHERE x = 6; WARN:ExecReplace: rejected due to CHECK constraint $2 -QUERY: SELECT currval('insert_seq'); -currval -------- - 8 -(1 row) - -QUERY: SELECT * FROM INSERT_TBL; -x|y | z --+-------------+-- -5|-NULL- |-5 -5|!check failed|-5 -6|try again |-6 -7|try again |-7 -(4 rows) - QUERY: UPDATE INSERT_TBL SET x = 6 WHERE x = 6; -QUERY: SELECT * FROM INSERT_TBL; -x|y | z --+-------------+-- -5|-NULL- |-5 -5|!check failed|-5 -7|try again |-7 -6|try again |-6 -(4 rows) - QUERY: UPDATE INSERT_TBL SET x = -z, z = -x; +QUERY: UPDATE INSERT_TBL SET x = z, z = x; +WARN:ExecReplace: rejected due to CHECK constraint insert_con QUERY: SELECT * FROM INSERT_TBL; x|y | z -+-------------+-- -5|-NULL- |-5 +4|Y |-4 5|!check failed|-5 7|try again |-7 6|try again |-6 (4 rows) -QUERY: CREATE SEQUENCE COPY_SEQ START 4; -QUERY: CREATE TABLE COPY_TBL (x INT DEFAULT nextval('copy_seq'), - y TEXT DEFAULT '-NULL-', z INT DEFAULT -1 * currval('copy_seq') ) - CONSTRAINT COPY_CON CHECK (x > 3 AND y <> 'check failed' AND x < 7 ), - CHECK x + z = 0; +QUERY: CREATE TABLE COPY_TBL (x INT, y TEXT, z INT) + CONSTRAINT COPY_CON CHECK (x > 3 AND y <> 'check failed' AND x < 7 ); QUERY: COPY COPY_TBL FROM '_OBJWD_/data/constro.data'; QUERY: SELECT * FROM COPY_TBL; -x|y | z --+------+-- -4|-NULL-|-4 -5|-NULL-|-5 -6|-NULL-|-6 -(3 rows) +x|y |z +-+-------------+- +4|!check failed|5 +6|OK |4 +(2 rows) QUERY: COPY COPY_TBL FROM '_OBJWD_/data/constrf.data'; WARN:CopyFrom: rejected due to CHECK constraint copy_con QUERY: SELECT * FROM COPY_TBL; -x|y | z --+------+-- -4|-NULL-|-4 -5|-NULL-|-5 -6|-NULL-|-6 -(3 rows) - -QUERY: select nextval('copy_seq') - 1 as currval; -currval -------- - 7 -(1 row) +x|y |z +-+-------------+- +4|!check failed|5 +6|OK |4 +(2 rows) diff --git a/src/test/regress/output/create_function_1.source b/src/test/regress/output/create_function_1.source index f32132936c..56b065f219 100644 --- a/src/test/regress/output/create_function_1.source +++ b/src/test/regress/output/create_function_1.source @@ -17,6 +17,11 @@ QUERY: CREATE FUNCTION check_foreign_key () AS '_OBJWD_/../../../contrib/spi/refint_DLSUFFIX_' LANGUAGE 'c' ; +QUERY: CREATE FUNCTION autoinc () + RETURNS opaque + AS '_OBJWD_/../../../contrib/spi/autoinc_DLSUFFIX_' + LANGUAGE 'c' +; QUERY: CREATE FUNCTION funny_dup17 () RETURNS opaque AS '_OBJWD_/regress_DLSUFFIX_' diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql index 07449ffa6e..f7d2c23a5a 100644 --- a/src/test/regress/sql/triggers.sql +++ b/src/test/regress/sql/triggers.sql @@ -122,20 +122,26 @@ create sequence ttdummy_seq increment 10 start 0 minvalue 0; create table tttest ( price_id int4, price_val int4, - price_on int4 default nextval('ttdummy_seq'), + price_on int4, price_off int4 default 999999 ); -insert into tttest values (1, 1, null, null); -insert into tttest values (2, 2, null, null); -insert into tttest values (3, 3, null, null); - create trigger ttdummy before delete or update on tttest for each row execute procedure ttdummy (price_on, price_off); +create trigger ttserial + before insert or update on tttest + for each row + execute procedure + autoinc (price_on, ttdummy_seq); + +insert into tttest values (1, 1, null); +insert into tttest values (2, 2, null); +insert into tttest values (3, 3, 0); + select * from tttest; delete from tttest where price_id = 2; select * from tttest; @@ -176,8 +182,8 @@ update tttest set price_on = -1 where price_id = 1; select * from tttest; -- isn't it what we need ? --- get price for price_id == 5 as it was @ "date" 25 -select * from tttest where price_on <= 25 and price_off > 25 and price_id = 5; +-- get price for price_id == 5 as it was @ "date" 35 +select * from tttest where price_on <= 35 and price_off > 35 and price_id = 5; drop table tttest; drop sequence ttdummy_seq; -- 2.40.0