*** /home/kgrittn/postgresql-8.4.0/src/test/regress/expected/create_function_1.out 2009-07-17 17:16:51.000000000 -0500 --- /home/kgrittn/postgresql-8.4.0/src/test/regress/results/create_function_1.out 2009-07-17 17:17:08.000000000 -0500 *************** *** 27,40 **** --- 27,45 ---- RETURNS trigger AS '/home/kgrittn/postgresql-8.4.0/src/test/regress/refint.so' LANGUAGE C; + ERROR: incompatible library "/home/kgrittn/postgresql-8.4.0/src/test/regress/refint.so": version mismatch + DETAIL: Server is version 8.4, library is version 8.3. CREATE FUNCTION check_foreign_key () RETURNS trigger AS '/home/kgrittn/postgresql-8.4.0/src/test/regress/refint.so' LANGUAGE C; + ERROR: incompatible library "/home/kgrittn/postgresql-8.4.0/src/test/regress/refint.so": version mismatch + DETAIL: Server is version 8.4, library is version 8.3. CREATE FUNCTION autoinc () RETURNS trigger AS '/home/kgrittn/postgresql-8.4.0/src/test/regress/autoinc.so' LANGUAGE C; + ERROR: could not load library "/home/kgrittn/postgresql-8.4.0/src/test/regress/autoinc.so": /home/kgrittn/postgresql-8.4.0/src/test/regress/autoinc.so: undefined symbol: CStringGetTextDatum CREATE FUNCTION funny_dup17 () RETURNS trigger AS '/home/kgrittn/postgresql-8.4.0/src/test/regress/regress.so' ====================================================================== *** /home/kgrittn/postgresql-8.4.0/src/test/regress/expected/triggers.out 2008-11-05 12:49:28.000000000 -0600 --- /home/kgrittn/postgresql-8.4.0/src/test/regress/results/triggers.out 2009-07-17 17:17:09.000000000 -0500 *************** *** 24,33 **** --- 24,35 ---- for each row execute procedure check_primary_key ('fkey1', 'fkey2', 'pkeys', 'pkey1', 'pkey2'); + ERROR: function check_primary_key() does not exist create trigger check_fkeys_pkey2_exist before insert or update on fkeys for each row execute procedure check_primary_key ('fkey3', 'fkeys2', 'pkey23'); + ERROR: function check_primary_key() does not exist -- -- For fkeys2: -- (fkey21, fkey22) --> pkeys (pkey1, pkey2) *************** *** 37,47 **** --- 39,52 ---- for each row execute procedure check_primary_key ('fkey21', 'fkey22', 'pkeys', 'pkey1', 'pkey2'); + ERROR: function check_primary_key() does not exist -- Test comments COMMENT ON TRIGGER check_fkeys2_pkey_bad ON fkeys2 IS 'wrong'; ERROR: trigger "check_fkeys2_pkey_bad" for table "fkeys2" does not exist COMMENT ON TRIGGER check_fkeys2_pkey_exist ON fkeys2 IS 'right'; + ERROR: trigger "check_fkeys2_pkey_exist" for table "fkeys2" does not exist COMMENT ON TRIGGER check_fkeys2_pkey_exist ON fkeys2 IS NULL; + ERROR: trigger "check_fkeys2_pkey_exist" for table "fkeys2" does not exist -- -- For pkeys: -- ON DELETE/UPDATE (pkey1, pkey2) CASCADE: *************** *** 53,58 **** --- 58,64 ---- execute procedure check_foreign_key (2, 'cascade', 'pkey1', 'pkey2', 'fkeys', 'fkey1', 'fkey2', 'fkeys2', 'fkey21', 'fkey22'); + ERROR: function check_foreign_key() does not exist -- -- For fkeys2: -- ON DELETE/UPDATE (pkey23) RESTRICT: *************** *** 62,101 **** before delete or update on fkeys2 for each row execute procedure check_foreign_key (1, 'restrict', 'pkey23', 'fkeys', 'fkey3'); insert into fkeys2 values (10, '1', 1); insert into fkeys2 values (30, '3', 2); insert into fkeys2 values (40, '4', 5); insert into fkeys2 values (50, '5', 3); -- no key in pkeys insert into fkeys2 values (70, '5', 3); - ERROR: tuple references non-existent key - DETAIL: Trigger "check_fkeys2_pkey_exist" found tuple referencing non-existent key in "pkeys". insert into fkeys values (10, '1', 2); insert into fkeys values (30, '3', 3); insert into fkeys values (40, '4', 2); insert into fkeys values (50, '5', 2); -- no key in pkeys insert into fkeys values (70, '5', 1); - ERROR: tuple references non-existent key - DETAIL: Trigger "check_fkeys_pkey_exist" found tuple referencing non-existent key in "pkeys". -- no key in fkeys2 insert into fkeys values (60, '6', 4); - ERROR: tuple references non-existent key - DETAIL: Trigger "check_fkeys_pkey2_exist" found tuple referencing non-existent key in "fkeys2". delete from pkeys where pkey1 = 30 and pkey2 = '3'; - NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted - ERROR: "check_fkeys2_fkey_restrict": tuple is referenced in "fkeys" - CONTEXT: SQL statement "delete from fkeys2 where fkey21 = $1 and fkey22 = $2 " delete from pkeys where pkey1 = 40 and pkey2 = '4'; - NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted - NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys2 are deleted update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 50 and pkey2 = '5'; - NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted - ERROR: "check_fkeys2_fkey_restrict": tuple is referenced in "fkeys" - CONTEXT: SQL statement "delete from fkeys2 where fkey21 = $1 and fkey22 = $2 " update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 10 and pkey2 = '1'; ! NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted ! NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys2 are deleted DROP TABLE pkeys; DROP TABLE fkeys; DROP TABLE fkeys2; --- 68,93 ---- before delete or update on fkeys2 for each row execute procedure check_foreign_key (1, 'restrict', 'pkey23', 'fkeys', 'fkey3'); + ERROR: function check_foreign_key() does not exist insert into fkeys2 values (10, '1', 1); insert into fkeys2 values (30, '3', 2); insert into fkeys2 values (40, '4', 5); insert into fkeys2 values (50, '5', 3); -- no key in pkeys insert into fkeys2 values (70, '5', 3); insert into fkeys values (10, '1', 2); insert into fkeys values (30, '3', 3); insert into fkeys values (40, '4', 2); insert into fkeys values (50, '5', 2); -- no key in pkeys insert into fkeys values (70, '5', 1); -- no key in fkeys2 insert into fkeys values (60, '6', 4); delete from pkeys where pkey1 = 30 and pkey2 = '3'; delete from pkeys where pkey1 = 40 and pkey2 = '4'; update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 50 and pkey2 = '5'; update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 10 and pkey2 = '1'; ! ERROR: duplicate key value violates unique constraint "pkeys_i" DROP TABLE pkeys; DROP TABLE fkeys; DROP TABLE fkeys2; *************** *** 150,173 **** 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; price_id | price_val | price_on | price_off ----------+-----------+----------+----------- ! 1 | 1 | 10 | 999999 ! 2 | 2 | 20 | 999999 ! 3 | 3 | 30 | 999999 (3 rows) delete from tttest where price_id = 2; select * from tttest; price_id | price_val | price_on | price_off ----------+-----------+----------+----------- ! 1 | 1 | 10 | 999999 ! 3 | 3 | 30 | 999999 ! 2 | 2 | 20 | 40 (3 rows) -- what do we see ? --- 142,167 ---- for each row execute procedure autoinc (price_on, ttdummy_seq); + ERROR: function autoinc() does not exist insert into tttest values (1, 1, null); insert into tttest values (2, 2, null); insert into tttest values (3, 3, 0); select * from tttest; price_id | price_val | price_on | price_off ----------+-----------+----------+----------- ! 1 | 1 | | 999999 ! 2 | 2 | | 999999 ! 3 | 3 | 0 | 999999 (3 rows) delete from tttest where price_id = 2; + ERROR: ttdummy (tttest): price_on must be NOT NULL select * from tttest; price_id | price_val | price_on | price_off ----------+-----------+----------+----------- ! 1 | 1 | | 999999 ! 2 | 2 | | 999999 ! 3 | 3 | 0 | 999999 (3 rows) -- what do we see ? *************** *** 175,193 **** select * from tttest where price_off = 999999; price_id | price_val | price_on | price_off ----------+-----------+----------+----------- ! 1 | 1 | 10 | 999999 ! 3 | 3 | 30 | 999999 ! (2 rows) -- change price for price_id == 3 update tttest set price_val = 30 where price_id = 3; select * from tttest; price_id | price_val | price_on | price_off ----------+-----------+----------+----------- ! 1 | 1 | 10 | 999999 ! 2 | 2 | 20 | 40 ! 3 | 30 | 50 | 999999 ! 3 | 3 | 30 | 50 (4 rows) -- now we want to change pric_id in ALL tuples --- 169,188 ---- select * from tttest where price_off = 999999; price_id | price_val | price_on | price_off ----------+-----------+----------+----------- ! 1 | 1 | | 999999 ! 2 | 2 | | 999999 ! 3 | 3 | 0 | 999999 ! (3 rows) -- change price for price_id == 3 update tttest set price_val = 30 where price_id = 3; select * from tttest; price_id | price_val | price_on | price_off ----------+-----------+----------+----------- ! 1 | 1 | | 999999 ! 2 | 2 | | 999999 ! 3 | 30 | 0 | 999999 ! 3 | 3 | 0 | 0 (4 rows) -- now we want to change pric_id in ALL tuples *************** *** 196,206 **** select * from tttest; price_id | price_val | price_on | price_off ----------+-----------+----------+----------- ! 1 | 1 | 10 | 999999 ! 2 | 2 | 20 | 40 ! 3 | 3 | 30 | 50 ! 5 | 30 | 60 | 999999 ! 3 | 30 | 50 | 60 (5 rows) -- restore data as before last update: --- 191,201 ---- select * from tttest; price_id | price_val | price_on | price_off ----------+-----------+----------+----------- ! 1 | 1 | | 999999 ! 2 | 2 | | 999999 ! 3 | 3 | 0 | 0 ! 5 | 30 | 10 | 999999 ! 3 | 30 | 0 | 10 (5 rows) -- restore data as before last update: *************** *** 215,224 **** select * from tttest; price_id | price_val | price_on | price_off ----------+-----------+----------+----------- ! 1 | 1 | 10 | 999999 ! 2 | 2 | 20 | 40 ! 3 | 3 | 30 | 50 ! 3 | 30 | 50 | 999999 (4 rows) -- and try change price_id now! --- 210,219 ---- select * from tttest; price_id | price_val | price_on | price_off ----------+-----------+----------+----------- ! 1 | 1 | | 999999 ! 2 | 2 | | 999999 ! 3 | 3 | 0 | 0 ! 3 | 30 | 0 | 999999 (4 rows) -- and try change price_id now! *************** *** 226,235 **** select * from tttest; price_id | price_val | price_on | price_off ----------+-----------+----------+----------- ! 1 | 1 | 10 | 999999 ! 2 | 2 | 20 | 40 ! 5 | 3 | 30 | 50 ! 5 | 30 | 50 | 999999 (4 rows) -- isn't it what we need ? --- 221,230 ---- select * from tttest; price_id | price_val | price_on | price_off ----------+-----------+----------+----------- ! 1 | 1 | | 999999 ! 2 | 2 | | 999999 ! 5 | 3 | 0 | 0 ! 5 | 30 | 0 | 999999 (4 rows) -- isn't it what we need ? *************** *** 241,247 **** -- we want to correct some "date" update tttest set price_on = -1 where price_id = 1; ! ERROR: ttdummy (tttest): you cannot change price_on and/or price_off columns (use set_ttdummy) -- but this doesn't work -- try in this way select set_ttdummy(0); --- 236,242 ---- -- we want to correct some "date" update tttest set price_on = -1 where price_id = 1; ! ERROR: ttdummy (tttest): price_on must be NOT NULL -- but this doesn't work -- try in this way select set_ttdummy(0); *************** *** 254,262 **** select * from tttest; price_id | price_val | price_on | price_off ----------+-----------+----------+----------- ! 2 | 2 | 20 | 40 ! 5 | 3 | 30 | 50 ! 5 | 30 | 50 | 999999 1 | 1 | -1 | 999999 (4 rows) --- 249,257 ---- select * from tttest; price_id | price_val | price_on | price_off ----------+-----------+----------+----------- ! 2 | 2 | | 999999 ! 5 | 3 | 0 | 0 ! 5 | 30 | 0 | 999999 1 | 1 | -1 | 999999 (4 rows) *************** *** 265,271 **** 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 | 30 | 50 (1 row) drop table tttest; --- 260,266 ---- select * from tttest where price_on <= 35 and price_off > 35 and price_id = 5; price_id | price_val | price_on | price_off ----------+-----------+----------+----------- ! 5 | 30 | 0 | 999999 (1 row) drop table tttest; ======================================================================