granicus.if.org Git - postgresql/blob - src/test/regress/expected/triggers.out

   1 --
   2 -- TRIGGERS
   3 --
   4 create table pkeys (pkey1 int4 not null, pkey2 text not null);
   5 create table fkeys (fkey1 int4, fkey2 text, fkey3 int);
   6 create table fkeys2 (fkey21 int4, fkey22 text, pkey23 int not null);
   7 create index fkeys_i on fkeys (fkey1, fkey2);
   8 create index fkeys2_i on fkeys2 (fkey21, fkey22);
   9 create index fkeys2p_i on fkeys2 (pkey23);
  10 insert into pkeys values (10, '1');
  11 insert into pkeys values (20, '2');
  12 insert into pkeys values (30, '3');
  13 insert into pkeys values (40, '4');
  14 insert into pkeys values (50, '5');
  15 insert into pkeys values (60, '6');
  16 create unique index pkeys_i on pkeys (pkey1, pkey2);
  17 --
  18 -- For fkeys:
  19 --      (fkey1, fkey2)  --> pkeys (pkey1, pkey2)
  20 --      (fkey3)         --> fkeys2 (pkey23)
  21 --
  22 create trigger check_fkeys_pkey_exist
  23         before insert or update on fkeys
  24         for each row
  25         execute procedure
  26         check_primary_key ('fkey1', 'fkey2', 'pkeys', 'pkey1', 'pkey2');
  27 create trigger check_fkeys_pkey2_exist
  28         before insert or update on fkeys
  29         for each row
  30         execute procedure check_primary_key ('fkey3', 'fkeys2', 'pkey23');
  31 --
  32 -- For fkeys2:
  33 --      (fkey21, fkey22)        --> pkeys (pkey1, pkey2)
  34 --
  35 create trigger check_fkeys2_pkey_exist
  36         before insert or update on fkeys2
  37         for each row
  38         execute procedure
  39         check_primary_key ('fkey21', 'fkey22', 'pkeys', 'pkey1', 'pkey2');
  40 -- Test comments
  41 COMMENT ON TRIGGER check_fkeys2_pkey_bad ON fkeys2 IS 'wrong';
  42 ERROR:  trigger "check_fkeys2_pkey_bad" for table "fkeys2" does not exist
  43 COMMENT ON TRIGGER check_fkeys2_pkey_exist ON fkeys2 IS 'right';
  44 COMMENT ON TRIGGER check_fkeys2_pkey_exist ON fkeys2 IS NULL;
  45 --
  46 -- For pkeys:
  47 --      ON DELETE/UPDATE (pkey1, pkey2) CASCADE:
  48 --              fkeys (fkey1, fkey2) and fkeys2 (fkey21, fkey22)
  49 --
  50 create trigger check_pkeys_fkey_cascade
  51         before delete or update on pkeys
  52         for each row
  53         execute procedure
  54         check_foreign_key (2, 'cascade', 'pkey1', 'pkey2',
  55         'fkeys', 'fkey1', 'fkey2', 'fkeys2', 'fkey21', 'fkey22');
  56 --
  57 -- For fkeys2:
  58 --      ON DELETE/UPDATE (pkey23) RESTRICT:
  59 --              fkeys (fkey3)
  60 --
  61 create trigger check_fkeys2_fkey_restrict
  62         before delete or update on fkeys2
  63         for each row
  64         execute procedure check_foreign_key (1, 'restrict', 'pkey23', 'fkeys', 'fkey3');
  65 insert into fkeys2 values (10, '1', 1);
  66 insert into fkeys2 values (30, '3', 2);
  67 insert into fkeys2 values (40, '4', 5);
  68 insert into fkeys2 values (50, '5', 3);
  69 -- no key in pkeys
  70 insert into fkeys2 values (70, '5', 3);
  71 ERROR:  tuple references non-existent key
  72 DETAIL:  Trigger "check_fkeys2_pkey_exist" found tuple referencing non-existent key in "pkeys".
  73 insert into fkeys values (10, '1', 2);
  74 insert into fkeys values (30, '3', 3);
  75 insert into fkeys values (40, '4', 2);
  76 insert into fkeys values (50, '5', 2);
  77 -- no key in pkeys
  78 insert into fkeys values (70, '5', 1);
  79 ERROR:  tuple references non-existent key
  80 DETAIL:  Trigger "check_fkeys_pkey_exist" found tuple referencing non-existent key in "pkeys".
  81 -- no key in fkeys2
  82 insert into fkeys values (60, '6', 4);
  83 ERROR:  tuple references non-existent key
  84 DETAIL:  Trigger "check_fkeys_pkey2_exist" found tuple referencing non-existent key in "fkeys2".
  85 delete from pkeys where pkey1 = 30 and pkey2 = '3';
  86 NOTICE:  check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted
  87 ERROR:  "check_fkeys2_fkey_restrict": tuple is referenced in "fkeys"
  88 CONTEXT:  SQL statement "delete from fkeys2 where fkey21 = $1 and fkey22 = $2 "
  89 delete from pkeys where pkey1 = 40 and pkey2 = '4';
  90 NOTICE:  check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted
  91 NOTICE:  check_pkeys_fkey_cascade: 1 tuple(s) of fkeys2 are deleted
  92 update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 50 and pkey2 = '5';
  93 NOTICE:  check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted
  94 ERROR:  "check_fkeys2_fkey_restrict": tuple is referenced in "fkeys"
  95 CONTEXT:  SQL statement "delete from fkeys2 where fkey21 = $1 and fkey22 = $2 "
  96 update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 10 and pkey2 = '1';
  97 NOTICE:  check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted
  98 NOTICE:  check_pkeys_fkey_cascade: 1 tuple(s) of fkeys2 are deleted
  99 DROP TABLE pkeys;
 100 DROP TABLE fkeys;
 101 DROP TABLE fkeys2;
 102 -- -- I've disabled the funny_dup17 test because the new semantics
 103 -- -- of AFTER ROW triggers, which get now fired at the end of a
 104 -- -- query always, cause funny_dup17 to enter an endless loop.
 105 -- --
 106 -- --      Jan
 107 --
 108 -- create table dup17 (x int4);
 109 --
 110 -- create trigger dup17_before
 111 --      before insert on dup17
 112 --      for each row
 113 --      execute procedure
 114 --      funny_dup17 ()
 115 -- ;
 116 --
 117 -- insert into dup17 values (17);
 118 -- select count(*) from dup17;
 119 -- insert into dup17 values (17);
 120 -- select count(*) from dup17;
 121 --
 122 -- drop trigger dup17_before on dup17;
 123 --
 124 -- create trigger dup17_after
 125 --      after insert on dup17
 126 --      for each row
 127 --      execute procedure
 128 --      funny_dup17 ()
 129 -- ;
 130 -- insert into dup17 values (13);
 131 -- select count(*) from dup17 where x = 13;
 132 -- insert into dup17 values (13);
 133 -- select count(*) from dup17 where x = 13;
 134 --
 135 -- DROP TABLE dup17;
 136 create sequence ttdummy_seq increment 10 start 0 minvalue 0;
 137 create table tttest (
 138         price_id        int4,
 139         price_val       int4,
 140         price_on        int4,
 141         price_off       int4 default 999999
 142 );
 143 create trigger ttdummy
 144         before delete or update on tttest
 145         for each row
 146         execute procedure
 147         ttdummy (price_on, price_off);
 148 create trigger ttserial
 149         before insert or update on tttest
 150         for each row
 151         execute procedure
 152         autoinc (price_on, ttdummy_seq);
 153 insert into tttest values (1, 1, null);
 154 insert into tttest values (2, 2, null);
 155 insert into tttest values (3, 3, 0);
 156 select * from tttest;
 157  price_id | price_val | price_on | price_off
 158 ----------+-----------+----------+-----------
 159         1 |         1 |       10 |    999999
 160         2 |         2 |       20 |    999999
 161         3 |         3 |       30 |    999999
 162 (3 rows)
 163
 164 delete from tttest where price_id = 2;
 165 select * from tttest;
 166  price_id | price_val | price_on | price_off
 167 ----------+-----------+----------+-----------
 168         1 |         1 |       10 |    999999
 169         3 |         3 |       30 |    999999
 170         2 |         2 |       20 |        40
 171 (3 rows)
 172
 173 -- what do we see ?
 174 -- get current prices
 175 select * from tttest where price_off = 999999;
 176  price_id | price_val | price_on | price_off
 177 ----------+-----------+----------+-----------
 178         1 |         1 |       10 |    999999
 179         3 |         3 |       30 |    999999
 180 (2 rows)
 181
 182 -- change price for price_id == 3
 183 update tttest set price_val = 30 where price_id = 3;
 184 select * from tttest;
 185  price_id | price_val | price_on | price_off
 186 ----------+-----------+----------+-----------
 187         1 |         1 |       10 |    999999
 188         2 |         2 |       20 |        40
 189         3 |        30 |       50 |    999999
 190         3 |         3 |       30 |        50
 191 (4 rows)
 192
 193 -- now we want to change pric_id in ALL tuples
 194 -- this gets us not what we need
 195 update tttest set price_id = 5 where price_id = 3;
 196 select * from tttest;
 197  price_id | price_val | price_on | price_off
 198 ----------+-----------+----------+-----------
 199         1 |         1 |       10 |    999999
 200         2 |         2 |       20 |        40
 201         3 |         3 |       30 |        50
 202         5 |        30 |       60 |    999999
 203         3 |        30 |       50 |        60
 204 (5 rows)
 205
 206 -- restore data as before last update:
 207 select set_ttdummy(0);
 208  set_ttdummy
 209 -------------
 210            1
 211 (1 row)
 212
 213 delete from tttest where price_id = 5;
 214 update tttest set price_off = 999999 where price_val = 30;
 215 select * from tttest;
 216  price_id | price_val | price_on | price_off
 217 ----------+-----------+----------+-----------
 218         1 |         1 |       10 |    999999
 219         2 |         2 |       20 |        40
 220         3 |         3 |       30 |        50
 221         3 |        30 |       50 |    999999
 222 (4 rows)
 223
 224 -- and try change price_id now!
 225 update tttest set price_id = 5 where price_id = 3;
 226 select * from tttest;
 227  price_id | price_val | price_on | price_off
 228 ----------+-----------+----------+-----------
 229         1 |         1 |       10 |    999999
 230         2 |         2 |       20 |        40
 231         5 |         3 |       30 |        50
 232         5 |        30 |       50 |    999999
 233 (4 rows)
 234
 235 -- isn't it what we need ?
 236 select set_ttdummy(1);
 237  set_ttdummy
 238 -------------
 239            0
 240 (1 row)
 241
 242 -- we want to correct some "date"
 243 update tttest set price_on = -1 where price_id = 1;
 244 ERROR:  ttdummy (tttest): you cannot change price_on and/or price_off columns (use set_ttdummy)
 245 -- but this doesn't work
 246 -- try in this way
 247 select set_ttdummy(0);
 248  set_ttdummy
 249 -------------
 250            1
 251 (1 row)
 252
 253 update tttest set price_on = -1 where price_id = 1;
 254 select * from tttest;
 255  price_id | price_val | price_on | price_off
 256 ----------+-----------+----------+-----------
 257         2 |         2 |       20 |        40
 258         5 |         3 |       30 |        50
 259         5 |        30 |       50 |    999999
 260         1 |         1 |       -1 |    999999
 261 (4 rows)
 262
 263 -- isn't it what we need ?
 264 -- get price for price_id == 5 as it was @ "date" 35
 265 select * from tttest where price_on <= 35 and price_off > 35 and price_id = 5;
 266  price_id | price_val | price_on | price_off
 267 ----------+-----------+----------+-----------
 268         5 |         3 |       30 |        50
 269 (1 row)
 270
 271 drop table tttest;
 272 drop sequence ttdummy_seq;
 273 --
 274 -- tests for per-statement triggers
 275 --
 276 CREATE TABLE log_table (tstamp timestamp default timeofday()::timestamp);
 277 CREATE TABLE main_table (a int, b int);
 278 COPY main_table (a,b) FROM stdin;
 279 CREATE FUNCTION trigger_func() RETURNS trigger LANGUAGE plpgsql AS '
 280 BEGIN
 281         RAISE NOTICE ''trigger_func(%) called: action = %, when = %, level = %'', TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL;
 282         RETURN NULL;
 283 END;';
 284 CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON main_table
 285 FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('before_ins_stmt');
 286 CREATE TRIGGER after_ins_stmt_trig AFTER INSERT ON main_table
 287 FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('after_ins_stmt');
 288 --
 289 -- if neither 'FOR EACH ROW' nor 'FOR EACH STATEMENT' was specified,
 290 -- CREATE TRIGGER should default to 'FOR EACH STATEMENT'
 291 --
 292 CREATE TRIGGER after_upd_stmt_trig AFTER UPDATE ON main_table
 293 EXECUTE PROCEDURE trigger_func('after_upd_stmt');
 294 CREATE TRIGGER after_upd_row_trig AFTER UPDATE ON main_table
 295 FOR EACH ROW EXECUTE PROCEDURE trigger_func('after_upd_row');
 296 INSERT INTO main_table DEFAULT VALUES;
 297 NOTICE:  trigger_func(before_ins_stmt) called: action = INSERT, when = BEFORE, level = STATEMENT
 298 NOTICE:  trigger_func(after_ins_stmt) called: action = INSERT, when = AFTER, level = STATEMENT
 299 UPDATE main_table SET a = a + 1 WHERE b < 30;
 300 NOTICE:  trigger_func(after_upd_row) called: action = UPDATE, when = AFTER, level = ROW
 301 NOTICE:  trigger_func(after_upd_row) called: action = UPDATE, when = AFTER, level = ROW
 302 NOTICE:  trigger_func(after_upd_row) called: action = UPDATE, when = AFTER, level = ROW
 303 NOTICE:  trigger_func(after_upd_row) called: action = UPDATE, when = AFTER, level = ROW
 304 NOTICE:  trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
 305 -- UPDATE that effects zero rows should still call per-statement trigger
 306 UPDATE main_table SET a = a + 2 WHERE b > 100;
 307 NOTICE:  trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
 308 -- COPY should fire per-row and per-statement INSERT triggers
 309 COPY main_table (a, b) FROM stdin;
 310 NOTICE:  trigger_func(before_ins_stmt) called: action = INSERT, when = BEFORE, level = STATEMENT
 311 NOTICE:  trigger_func(after_ins_stmt) called: action = INSERT, when = AFTER, level = STATEMENT
 312 SELECT * FROM main_table ORDER BY a, b;
 313  a  | b
 314 ----+----
 315   6 | 10
 316  21 | 20
 317  30 | 40
 318  31 | 10
 319  50 | 35
 320  50 | 60
 321  81 | 15
 322     |
 323 (8 rows)
 324
 325 --
 326 -- test triggers with WHEN clause
 327 --
 328 CREATE TRIGGER modified_a BEFORE UPDATE OF a ON main_table
 329 FOR EACH ROW WHEN (OLD.a <> NEW.a) EXECUTE PROCEDURE trigger_func('modified_a');
 330 CREATE TRIGGER modified_any BEFORE UPDATE OF a ON main_table
 331 FOR EACH ROW WHEN (OLD.* IS DISTINCT FROM NEW.*) EXECUTE PROCEDURE trigger_func('modified_any');
 332 CREATE TRIGGER insert_a AFTER INSERT ON main_table
 333 FOR EACH ROW WHEN (NEW.a = 123) EXECUTE PROCEDURE trigger_func('insert_a');
 334 CREATE TRIGGER delete_a AFTER DELETE ON main_table
 335 FOR EACH ROW WHEN (OLD.a = 123) EXECUTE PROCEDURE trigger_func('delete_a');
 336 CREATE TRIGGER insert_when BEFORE INSERT ON main_table
 337 FOR EACH STATEMENT WHEN (true) EXECUTE PROCEDURE trigger_func('insert_when');
 338 CREATE TRIGGER delete_when AFTER DELETE ON main_table
 339 FOR EACH STATEMENT WHEN (true) EXECUTE PROCEDURE trigger_func('delete_when');
 340 INSERT INTO main_table (a) VALUES (123), (456);
 341 NOTICE:  trigger_func(before_ins_stmt) called: action = INSERT, when = BEFORE, level = STATEMENT
 342 NOTICE:  trigger_func(insert_when) called: action = INSERT, when = BEFORE, level = STATEMENT
 343 NOTICE:  trigger_func(insert_a) called: action = INSERT, when = AFTER, level = ROW
 344 NOTICE:  trigger_func(after_ins_stmt) called: action = INSERT, when = AFTER, level = STATEMENT
 345 COPY main_table FROM stdin;
 346 NOTICE:  trigger_func(before_ins_stmt) called: action = INSERT, when = BEFORE, level = STATEMENT
 347 NOTICE:  trigger_func(insert_when) called: action = INSERT, when = BEFORE, level = STATEMENT
 348 NOTICE:  trigger_func(insert_a) called: action = INSERT, when = AFTER, level = ROW
 349 NOTICE:  trigger_func(after_ins_stmt) called: action = INSERT, when = AFTER, level = STATEMENT
 350 DELETE FROM main_table WHERE a IN (123, 456);
 351 NOTICE:  trigger_func(delete_a) called: action = DELETE, when = AFTER, level = ROW
 352 NOTICE:  trigger_func(delete_a) called: action = DELETE, when = AFTER, level = ROW
 353 NOTICE:  trigger_func(delete_when) called: action = DELETE, when = AFTER, level = STATEMENT
 354 UPDATE main_table SET a = 50, b = 60;
 355 NOTICE:  trigger_func(modified_any) called: action = UPDATE, when = BEFORE, level = ROW
 356 NOTICE:  trigger_func(modified_any) called: action = UPDATE, when = BEFORE, level = ROW
 357 NOTICE:  trigger_func(modified_a) called: action = UPDATE, when = BEFORE, level = ROW
 358 NOTICE:  trigger_func(modified_a) called: action = UPDATE, when = BEFORE, level = ROW
 359 NOTICE:  trigger_func(modified_a) called: action = UPDATE, when = BEFORE, level = ROW
 360 NOTICE:  trigger_func(modified_a) called: action = UPDATE, when = BEFORE, level = ROW
 361 NOTICE:  trigger_func(modified_a) called: action = UPDATE, when = BEFORE, level = ROW
 362 NOTICE:  trigger_func(after_upd_row) called: action = UPDATE, when = AFTER, level = ROW
 363 NOTICE:  trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
 364 SELECT * FROM main_table ORDER BY a, b;
 365  a  | b
 366 ----+----
 367   6 | 10
 368  21 | 20
 369  30 | 40
 370  31 | 10
 371  50 | 35
 372  50 | 60
 373  81 | 15
 374     |
 375 (8 rows)
 376
 377 SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_a';
 378                                                              pg_get_triggerdef
 379 --------------------------------------------------------------------------------------------------------------------------------------------
 380  CREATE TRIGGER modified_a BEFORE UPDATE OF a ON main_table FOR EACH ROW WHEN (old.a <> new.a) EXECUTE PROCEDURE trigger_func('modified_a')
 381 (1 row)
 382
 383 SELECT pg_get_triggerdef(oid, false) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_a';
 384                                                               pg_get_triggerdef
 385 ----------------------------------------------------------------------------------------------------------------------------------------------
 386  CREATE TRIGGER modified_a BEFORE UPDATE OF a ON main_table FOR EACH ROW WHEN ((old.a <> new.a)) EXECUTE PROCEDURE trigger_func('modified_a')
 387 (1 row)
 388
 389 SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_any';
 390                                                                       pg_get_triggerdef
 391 --------------------------------------------------------------------------------------------------------------------------------------------------------------
 392  CREATE TRIGGER modified_any BEFORE UPDATE OF a ON main_table FOR EACH ROW WHEN (old.* IS DISTINCT FROM new.*) EXECUTE PROCEDURE trigger_func('modified_any')
 393 (1 row)
 394
 395 DROP TRIGGER modified_a ON main_table;
 396 DROP TRIGGER modified_any ON main_table;
 397 DROP TRIGGER insert_a ON main_table;
 398 DROP TRIGGER delete_a ON main_table;
 399 DROP TRIGGER insert_when ON main_table;
 400 DROP TRIGGER delete_when ON main_table;
 401 -- Test column-level triggers
 402 DROP TRIGGER after_upd_row_trig ON main_table;
 403 CREATE TRIGGER before_upd_a_row_trig BEFORE UPDATE OF a ON main_table
 404 FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_upd_a_row');
 405 CREATE TRIGGER after_upd_b_row_trig AFTER UPDATE OF b ON main_table
 406 FOR EACH ROW EXECUTE PROCEDURE trigger_func('after_upd_b_row');
 407 CREATE TRIGGER after_upd_a_b_row_trig AFTER UPDATE OF a, b ON main_table
 408 FOR EACH ROW EXECUTE PROCEDURE trigger_func('after_upd_a_b_row');
 409 CREATE TRIGGER before_upd_a_stmt_trig BEFORE UPDATE OF a ON main_table
 410 FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('before_upd_a_stmt');
 411 CREATE TRIGGER after_upd_b_stmt_trig AFTER UPDATE OF b ON main_table
 412 FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('after_upd_b_stmt');
 413 SELECT pg_get_triggerdef(oid) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'after_upd_a_b_row_trig';
 414                                                              pg_get_triggerdef
 415 -------------------------------------------------------------------------------------------------------------------------------------------
 416  CREATE TRIGGER after_upd_a_b_row_trig AFTER UPDATE OF a, b ON main_table FOR EACH ROW EXECUTE PROCEDURE trigger_func('after_upd_a_b_row')
 417 (1 row)
 418
 419 UPDATE main_table SET a = 50;
 420 NOTICE:  trigger_func(before_upd_a_stmt) called: action = UPDATE, when = BEFORE, level = STATEMENT
 421 NOTICE:  trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW
 422 NOTICE:  trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW
 423 NOTICE:  trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW
 424 NOTICE:  trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW
 425 NOTICE:  trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW
 426 NOTICE:  trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW
 427 NOTICE:  trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW
 428 NOTICE:  trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW
 429 NOTICE:  trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
 430 UPDATE main_table SET b = 10;
 431 NOTICE:  trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW
 432 NOTICE:  trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW
 433 NOTICE:  trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW
 434 NOTICE:  trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW
 435 NOTICE:  trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW
 436 NOTICE:  trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW
 437 NOTICE:  trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW
 438 NOTICE:  trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW
 439 NOTICE:  trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW
 440 NOTICE:  trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW
 441 NOTICE:  trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW
 442 NOTICE:  trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW
 443 NOTICE:  trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW
 444 NOTICE:  trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW
 445 NOTICE:  trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW
 446 NOTICE:  trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW
 447 NOTICE:  trigger_func(after_upd_b_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
 448 NOTICE:  trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
 449 -- bogus cases
 450 CREATE TRIGGER error_upd_and_col BEFORE UPDATE OR UPDATE OF a ON main_table
 451 FOR EACH ROW EXECUTE PROCEDURE trigger_func('error_upd_and_col');
 452 ERROR:  duplicate trigger events specified at or near "ON"
 453 LINE 1: ...ER error_upd_and_col BEFORE UPDATE OR UPDATE OF a ON main_ta...
 454                                                              ^
 455 CREATE TRIGGER error_upd_a_a BEFORE UPDATE OF a, a ON main_table
 456 FOR EACH ROW EXECUTE PROCEDURE trigger_func('error_upd_a_a');
 457 ERROR:  column "a" specified more than once
 458 CREATE TRIGGER error_ins_a BEFORE INSERT OF a ON main_table
 459 FOR EACH ROW EXECUTE PROCEDURE trigger_func('error_ins_a');
 460 ERROR:  syntax error at or near "OF"
 461 LINE 1: CREATE TRIGGER error_ins_a BEFORE INSERT OF a ON main_table
 462                                                  ^
 463 CREATE TRIGGER error_ins_when BEFORE INSERT OR UPDATE ON main_table
 464 FOR EACH ROW WHEN (OLD.a <> NEW.a)
 465 EXECUTE PROCEDURE trigger_func('error_ins_old');
 466 ERROR:  INSERT trigger's WHEN condition cannot reference OLD values
 467 LINE 2: FOR EACH ROW WHEN (OLD.a <> NEW.a)
 468                            ^
 469 CREATE TRIGGER error_del_when BEFORE DELETE OR UPDATE ON main_table
 470 FOR EACH ROW WHEN (OLD.a <> NEW.a)
 471 EXECUTE PROCEDURE trigger_func('error_del_new');
 472 ERROR:  DELETE trigger's WHEN condition cannot reference NEW values
 473 LINE 2: FOR EACH ROW WHEN (OLD.a <> NEW.a)
 474                                     ^
 475 CREATE TRIGGER error_del_when BEFORE INSERT OR UPDATE ON main_table
 476 FOR EACH ROW WHEN (NEW.tableoid <> 0)
 477 EXECUTE PROCEDURE trigger_func('error_when_sys_column');
 478 ERROR:  BEFORE trigger's WHEN condition cannot reference NEW system columns
 479 LINE 2: FOR EACH ROW WHEN (NEW.tableoid <> 0)
 480                            ^
 481 CREATE TRIGGER error_stmt_when BEFORE UPDATE OF a ON main_table
 482 FOR EACH STATEMENT WHEN (OLD.* IS DISTINCT FROM NEW.*)
 483 EXECUTE PROCEDURE trigger_func('error_stmt_when');
 484 ERROR:  statement trigger's WHEN condition cannot reference column values
 485 LINE 2: FOR EACH STATEMENT WHEN (OLD.* IS DISTINCT FROM NEW.*)
 486                                  ^
 487 -- check dependency restrictions
 488 ALTER TABLE main_table DROP COLUMN b;
 489 ERROR:  cannot drop table main_table column b because other objects depend on it
 490 DETAIL:  trigger after_upd_b_row_trig on table main_table depends on table main_table column b
 491 trigger after_upd_a_b_row_trig on table main_table depends on table main_table column b
 492 trigger after_upd_b_stmt_trig on table main_table depends on table main_table column b
 493 HINT:  Use DROP ... CASCADE to drop the dependent objects too.
 494 -- this should succeed, but we'll roll it back to keep the triggers around
 495 begin;
 496 DROP TRIGGER after_upd_a_b_row_trig ON main_table;
 497 DROP TRIGGER after_upd_b_row_trig ON main_table;
 498 DROP TRIGGER after_upd_b_stmt_trig ON main_table;
 499 ALTER TABLE main_table DROP COLUMN b;
 500 rollback;
 501 -- Test enable/disable triggers
 502 create table trigtest (i serial primary key);
 503 NOTICE:  CREATE TABLE will create implicit sequence "trigtest_i_seq" for serial column "trigtest.i"
 504 NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "trigtest_pkey" for table "trigtest"
 505 -- test that disabling RI triggers works
 506 create table trigtest2 (i int references trigtest(i) on delete cascade);
 507 create function trigtest() returns trigger as $$
 508 begin
 509         raise notice '% % % %', TG_RELNAME, TG_OP, TG_WHEN, TG_LEVEL;
 510         return new;
 511 end;$$ language plpgsql;
 512 create trigger trigtest_b_row_tg before insert or update or delete on trigtest
 513 for each row execute procedure trigtest();
 514 create trigger trigtest_a_row_tg after insert or update or delete on trigtest
 515 for each row execute procedure trigtest();
 516 create trigger trigtest_b_stmt_tg before insert or update or delete on trigtest
 517 for each statement execute procedure trigtest();
 518 create trigger trigtest_a_stmt_tg after insert or update or delete on trigtest
 519 for each statement execute procedure trigtest();
 520 insert into trigtest default values;
 521 NOTICE:  trigtest INSERT BEFORE STATEMENT
 522 NOTICE:  trigtest INSERT BEFORE ROW
 523 NOTICE:  trigtest INSERT AFTER ROW
 524 NOTICE:  trigtest INSERT AFTER STATEMENT
 525 alter table trigtest disable trigger trigtest_b_row_tg;
 526 insert into trigtest default values;
 527 NOTICE:  trigtest INSERT BEFORE STATEMENT
 528 NOTICE:  trigtest INSERT AFTER ROW
 529 NOTICE:  trigtest INSERT AFTER STATEMENT
 530 alter table trigtest disable trigger user;
 531 insert into trigtest default values;
 532 alter table trigtest enable trigger trigtest_a_stmt_tg;
 533 insert into trigtest default values;
 534 NOTICE:  trigtest INSERT AFTER STATEMENT
 535 insert into trigtest2 values(1);
 536 insert into trigtest2 values(2);
 537 delete from trigtest where i=2;
 538 NOTICE:  trigtest DELETE AFTER STATEMENT
 539 select * from trigtest2;
 540  i
 541 ---
 542  1
 543 (1 row)
 544
 545 alter table trigtest disable trigger all;
 546 delete from trigtest where i=1;
 547 select * from trigtest2;
 548  i
 549 ---
 550  1
 551 (1 row)
 552
 553 -- ensure we still insert, even when all triggers are disabled
 554 insert into trigtest default values;
 555 select *  from trigtest;
 556  i
 557 ---
 558  3
 559  4
 560  5
 561 (3 rows)
 562
 563 drop table trigtest2;
 564 drop table trigtest;
 565 -- dump trigger data
 566 CREATE TABLE trigger_test (
 567         i int,
 568         v varchar
 569 );
 570 CREATE OR REPLACE FUNCTION trigger_data()  RETURNS trigger
 571 LANGUAGE plpgsql AS $$
 572
 573 declare
 574
 575         argstr text;
 576         relid text;
 577
 578 begin
 579
 580         relid := TG_relid::regclass;
 581
 582         -- plpgsql can't discover its trigger data in a hash like perl and python
 583         -- can, or by a sort of reflection like tcl can,
 584         -- so we have to hard code the names.
 585         raise NOTICE 'TG_NAME: %', TG_name;
 586         raise NOTICE 'TG_WHEN: %', TG_when;
 587         raise NOTICE 'TG_LEVEL: %', TG_level;
 588         raise NOTICE 'TG_OP: %', TG_op;
 589         raise NOTICE 'TG_RELID::regclass: %', relid;
 590         raise NOTICE 'TG_RELNAME: %', TG_relname;
 591         raise NOTICE 'TG_TABLE_NAME: %', TG_table_name;
 592         raise NOTICE 'TG_TABLE_SCHEMA: %', TG_table_schema;
 593         raise NOTICE 'TG_NARGS: %', TG_nargs;
 594
 595         argstr := '[';
 596         for i in 0 .. TG_nargs - 1 loop
 597                 if i > 0 then
 598                         argstr := argstr || ', ';
 599                 end if;
 600                 argstr := argstr || TG_argv[i];
 601         end loop;
 602         argstr := argstr || ']';
 603         raise NOTICE 'TG_ARGV: %', argstr;
 604
 605         if TG_OP != 'INSERT' then
 606                 raise NOTICE 'OLD: %', OLD;
 607         end if;
 608
 609         if TG_OP != 'DELETE' then
 610                 raise NOTICE 'NEW: %', NEW;
 611         end if;
 612
 613         if TG_OP = 'DELETE' then
 614                 return OLD;
 615         else
 616                 return NEW;
 617         end if;
 618
 619 end;
 620 $$;
 621 CREATE TRIGGER show_trigger_data_trig
 622 BEFORE INSERT OR UPDATE OR DELETE ON trigger_test
 623 FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
 624 insert into trigger_test values(1,'insert');
 625 NOTICE:  TG_NAME: show_trigger_data_trig
 626 NOTICE:  TG_WHEN: BEFORE
 627 NOTICE:  TG_LEVEL: ROW
 628 NOTICE:  TG_OP: INSERT
 629 NOTICE:  TG_RELID::regclass: trigger_test
 630 NOTICE:  TG_RELNAME: trigger_test
 631 NOTICE:  TG_TABLE_NAME: trigger_test
 632 NOTICE:  TG_TABLE_SCHEMA: public
 633 NOTICE:  TG_NARGS: 2
 634 NOTICE:  TG_ARGV: [23, skidoo]
 635 NOTICE:  NEW: (1,insert)
 636 update trigger_test set v = 'update' where i = 1;
 637 NOTICE:  TG_NAME: show_trigger_data_trig
 638 NOTICE:  TG_WHEN: BEFORE
 639 NOTICE:  TG_LEVEL: ROW
 640 NOTICE:  TG_OP: UPDATE
 641 NOTICE:  TG_RELID::regclass: trigger_test
 642 NOTICE:  TG_RELNAME: trigger_test
 643 NOTICE:  TG_TABLE_NAME: trigger_test
 644 NOTICE:  TG_TABLE_SCHEMA: public
 645 NOTICE:  TG_NARGS: 2
 646 NOTICE:  TG_ARGV: [23, skidoo]
 647 NOTICE:  OLD: (1,insert)
 648 NOTICE:  NEW: (1,update)
 649 delete from trigger_test;
 650 NOTICE:  TG_NAME: show_trigger_data_trig
 651 NOTICE:  TG_WHEN: BEFORE
 652 NOTICE:  TG_LEVEL: ROW
 653 NOTICE:  TG_OP: DELETE
 654 NOTICE:  TG_RELID::regclass: trigger_test
 655 NOTICE:  TG_RELNAME: trigger_test
 656 NOTICE:  TG_TABLE_NAME: trigger_test
 657 NOTICE:  TG_TABLE_SCHEMA: public
 658 NOTICE:  TG_NARGS: 2
 659 NOTICE:  TG_ARGV: [23, skidoo]
 660 NOTICE:  OLD: (1,update)
 661 DROP TRIGGER show_trigger_data_trig on trigger_test;
 662 DROP FUNCTION trigger_data();
 663 DROP TABLE trigger_test;
 664 --
 665 -- Test use of row comparisons on OLD/NEW
 666 --
 667 CREATE TABLE trigger_test (f1 int, f2 text, f3 text);
 668 -- this is the obvious (and wrong...) way to compare rows
 669 CREATE FUNCTION mytrigger() RETURNS trigger LANGUAGE plpgsql as $$
 670 begin
 671         if row(old.*) = row(new.*) then
 672                 raise notice 'row % not changed', new.f1;
 673         else
 674                 raise notice 'row % changed', new.f1;
 675         end if;
 676         return new;
 677 end$$;
 678 CREATE TRIGGER t
 679 BEFORE UPDATE ON trigger_test
 680 FOR EACH ROW EXECUTE PROCEDURE mytrigger();
 681 INSERT INTO trigger_test VALUES(1, 'foo', 'bar');
 682 INSERT INTO trigger_test VALUES(2, 'baz', 'quux');
 683 UPDATE trigger_test SET f3 = 'bar';
 684 NOTICE:  row 1 not changed
 685 NOTICE:  row 2 changed
 686 UPDATE trigger_test SET f3 = NULL;
 687 NOTICE:  row 1 changed
 688 NOTICE:  row 2 changed
 689 -- this demonstrates that the above isn't really working as desired:
 690 UPDATE trigger_test SET f3 = NULL;
 691 NOTICE:  row 1 changed
 692 NOTICE:  row 2 changed
 693 -- the right way when considering nulls is
 694 CREATE OR REPLACE FUNCTION mytrigger() RETURNS trigger LANGUAGE plpgsql as $$
 695 begin
 696         if row(old.*) is distinct from row(new.*) then
 697                 raise notice 'row % changed', new.f1;
 698         else
 699                 raise notice 'row % not changed', new.f1;
 700         end if;
 701         return new;
 702 end$$;
 703 UPDATE trigger_test SET f3 = 'bar';
 704 NOTICE:  row 1 changed
 705 NOTICE:  row 2 changed
 706 UPDATE trigger_test SET f3 = NULL;
 707 NOTICE:  row 1 changed
 708 NOTICE:  row 2 changed
 709 UPDATE trigger_test SET f3 = NULL;
 710 NOTICE:  row 1 not changed
 711 NOTICE:  row 2 not changed
 712 DROP TABLE trigger_test;
 713 DROP FUNCTION mytrigger();
 714 -- Test snapshot management in serializable transactions involving triggers
 715 -- per bug report in 6bc73d4c0910042358k3d1adff3qa36f8df75198ecea@mail.gmail.com
 716 CREATE FUNCTION serializable_update_trig() RETURNS trigger LANGUAGE plpgsql AS
 717 $$
 718 declare
 719         rec record;
 720 begin
 721         new.description = 'updated in trigger';
 722         return new;
 723 end;
 724 $$;
 725 CREATE TABLE serializable_update_tab (
 726         id int,
 727         filler  text,
 728         description text
 729 );
 730 CREATE TRIGGER serializable_update_trig BEFORE UPDATE ON serializable_update_tab
 731         FOR EACH ROW EXECUTE PROCEDURE serializable_update_trig();
 732 INSERT INTO serializable_update_tab SELECT a, repeat('xyzxz', 100), 'new'
 733         FROM generate_series(1, 50) a;
 734 BEGIN;
 735 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 736 UPDATE serializable_update_tab SET description = 'no no', id = 1 WHERE id = 1;
 737 COMMIT;
 738 SELECT description FROM serializable_update_tab WHERE id = 1;
 739     description
 740 --------------------
 741  updated in trigger
 742 (1 row)
 743
 744 DROP TABLE serializable_update_tab;
 745 -- minimal update trigger
 746 CREATE TABLE min_updates_test (
 747         f1      text,
 748         f2 int,
 749         f3 int);
 750 CREATE TABLE min_updates_test_oids (
 751         f1      text,
 752         f2 int,
 753         f3 int) WITH OIDS;
 754 INSERT INTO min_updates_test VALUES ('a',1,2),('b','2',null);
 755 INSERT INTO min_updates_test_oids VALUES ('a',1,2),('b','2',null);
 756 CREATE TRIGGER z_min_update
 757 BEFORE UPDATE ON min_updates_test
 758 FOR EACH ROW EXECUTE PROCEDURE suppress_redundant_updates_trigger();
 759 CREATE TRIGGER z_min_update
 760 BEFORE UPDATE ON min_updates_test_oids
 761 FOR EACH ROW EXECUTE PROCEDURE suppress_redundant_updates_trigger();
 762 \set QUIET false
 763 UPDATE min_updates_test SET f1 = f1;
 764 UPDATE 0
 765 UPDATE min_updates_test SET f2 = f2 + 1;
 766 UPDATE 2
 767 UPDATE min_updates_test SET f3 = 2 WHERE f3 is null;
 768 UPDATE 1
 769 UPDATE min_updates_test_oids SET f1 = f1;
 770 UPDATE 0
 771 UPDATE min_updates_test_oids SET f2 = f2 + 1;
 772 UPDATE 2
 773 UPDATE min_updates_test_oids SET f3 = 2 WHERE f3 is null;
 774 UPDATE 1
 775 \set QUIET true
 776 SELECT * FROM min_updates_test;
 777  f1 | f2 | f3
 778 ----+----+----
 779  a  |  2 |  2
 780  b  |  3 |  2
 781 (2 rows)
 782
 783 SELECT * FROM min_updates_test_oids;
 784  f1 | f2 | f3
 785 ----+----+----
 786  a  |  2 |  2
 787  b  |  3 |  2
 788 (2 rows)
 789
 790 DROP TABLE min_updates_test;
 791 DROP TABLE min_updates_test_oids;
 792 --
 793 -- Test triggers on views
 794 --
 795 CREATE VIEW main_view AS SELECT a, b FROM main_table;
 796 -- Updates should fail without rules or triggers
 797 INSERT INTO main_view VALUES (1,2);
 798 ERROR:  cannot insert into view "main_view"
 799 HINT:  You need an unconditional ON INSERT DO INSTEAD rule or an INSTEAD OF INSERT trigger.
 800 UPDATE main_view SET b = 20 WHERE a = 50;
 801 ERROR:  cannot update view "main_view"
 802 HINT:  You need an unconditional ON UPDATE DO INSTEAD rule or an INSTEAD OF UPDATE trigger.
 803 DELETE FROM main_view WHERE a = 50;
 804 ERROR:  cannot delete from view "main_view"
 805 HINT:  You need an unconditional ON DELETE DO INSTEAD rule or an INSTEAD OF DELETE trigger.
 806 -- Should fail even when there are no matching rows
 807 DELETE FROM main_view WHERE a = 51;
 808 ERROR:  cannot delete from view "main_view"
 809 HINT:  You need an unconditional ON DELETE DO INSTEAD rule or an INSTEAD OF DELETE trigger.
 810 -- VIEW trigger function
 811 CREATE OR REPLACE FUNCTION view_trigger() RETURNS trigger
 812 LANGUAGE plpgsql AS $$
 813 declare
 814     argstr text := '';
 815 begin
 816     for i in 0 .. TG_nargs - 1 loop
 817         if i > 0 then
 818             argstr := argstr || ', ';
 819         end if;
 820         argstr := argstr || TG_argv[i];
 821     end loop;
 822
 823     raise notice '% % % % (%)', TG_RELNAME, TG_WHEN, TG_OP, TG_LEVEL, argstr;
 824
 825     if TG_LEVEL = 'ROW' then
 826         if TG_OP = 'INSERT' then
 827             raise NOTICE 'NEW: %', NEW;
 828             INSERT INTO main_table VALUES (NEW.a, NEW.b);
 829             RETURN NEW;
 830         end if;
 831
 832         if TG_OP = 'UPDATE' then
 833             raise NOTICE 'OLD: %, NEW: %', OLD, NEW;
 834             UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b;
 835             if NOT FOUND then RETURN NULL; end if;
 836             RETURN NEW;
 837         end if;
 838
 839         if TG_OP = 'DELETE' then
 840             raise NOTICE 'OLD: %', OLD;
 841             DELETE FROM main_table WHERE a = OLD.a AND b = OLD.b;
 842             if NOT FOUND then RETURN NULL; end if;
 843             RETURN OLD;
 844         end if;
 845     end if;
 846
 847     RETURN NULL;
 848 end;
 849 $$;
 850 -- Before row triggers aren't allowed on views
 851 CREATE TRIGGER invalid_trig BEFORE INSERT ON main_view
 852 FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_ins_row');
 853 ERROR:  "main_view" is a view
 854 DETAIL:  Views cannot have row-level BEFORE or AFTER triggers.
 855 CREATE TRIGGER invalid_trig BEFORE UPDATE ON main_view
 856 FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_upd_row');
 857 ERROR:  "main_view" is a view
 858 DETAIL:  Views cannot have row-level BEFORE or AFTER triggers.
 859 CREATE TRIGGER invalid_trig BEFORE DELETE ON main_view
 860 FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_del_row');
 861 ERROR:  "main_view" is a view
 862 DETAIL:  Views cannot have row-level BEFORE or AFTER triggers.
 863 -- After row triggers aren't allowed on views
 864 CREATE TRIGGER invalid_trig AFTER INSERT ON main_view
 865 FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_ins_row');
 866 ERROR:  "main_view" is a view
 867 DETAIL:  Views cannot have row-level BEFORE or AFTER triggers.
 868 CREATE TRIGGER invalid_trig AFTER UPDATE ON main_view
 869 FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_upd_row');
 870 ERROR:  "main_view" is a view
 871 DETAIL:  Views cannot have row-level BEFORE or AFTER triggers.
 872 CREATE TRIGGER invalid_trig AFTER DELETE ON main_view
 873 FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_del_row');
 874 ERROR:  "main_view" is a view
 875 DETAIL:  Views cannot have row-level BEFORE or AFTER triggers.
 876 -- Truncate triggers aren't allowed on views
 877 CREATE TRIGGER invalid_trig BEFORE TRUNCATE ON main_view
 878 EXECUTE PROCEDURE trigger_func('before_tru_row');
 879 ERROR:  "main_view" is a view
 880 DETAIL:  Views cannot have TRUNCATE triggers.
 881 CREATE TRIGGER invalid_trig AFTER TRUNCATE ON main_view
 882 EXECUTE PROCEDURE trigger_func('before_tru_row');
 883 ERROR:  "main_view" is a view
 884 DETAIL:  Views cannot have TRUNCATE triggers.
 885 -- INSTEAD OF triggers aren't allowed on tables
 886 CREATE TRIGGER invalid_trig INSTEAD OF INSERT ON main_table
 887 FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_ins');
 888 ERROR:  "main_table" is a table
 889 DETAIL:  Tables cannot have INSTEAD OF triggers.
 890 CREATE TRIGGER invalid_trig INSTEAD OF UPDATE ON main_table
 891 FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd');
 892 ERROR:  "main_table" is a table
 893 DETAIL:  Tables cannot have INSTEAD OF triggers.
 894 CREATE TRIGGER invalid_trig INSTEAD OF DELETE ON main_table
 895 FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_del');
 896 ERROR:  "main_table" is a table
 897 DETAIL:  Tables cannot have INSTEAD OF triggers.
 898 -- Don't support WHEN clauses with INSTEAD OF triggers
 899 CREATE TRIGGER invalid_trig INSTEAD OF UPDATE ON main_view
 900 FOR EACH ROW WHEN (OLD.a <> NEW.a) EXECUTE PROCEDURE view_trigger('instead_of_upd');
 901 ERROR:  INSTEAD OF triggers cannot have WHEN conditions
 902 -- Don't support column-level INSTEAD OF triggers
 903 CREATE TRIGGER invalid_trig INSTEAD OF UPDATE OF a ON main_view
 904 FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd');
 905 ERROR:  INSTEAD OF triggers cannot have column lists
 906 -- Don't support statement-level INSTEAD OF triggers
 907 CREATE TRIGGER invalid_trig INSTEAD OF UPDATE ON main_view
 908 EXECUTE PROCEDURE view_trigger('instead_of_upd');
 909 ERROR:  INSTEAD OF triggers must be FOR EACH ROW
 910 -- Valid INSTEAD OF triggers
 911 CREATE TRIGGER instead_of_insert_trig INSTEAD OF INSERT ON main_view
 912 FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_ins');
 913 CREATE TRIGGER instead_of_update_trig INSTEAD OF UPDATE ON main_view
 914 FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd');
 915 CREATE TRIGGER instead_of_delete_trig INSTEAD OF DELETE ON main_view
 916 FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_del');
 917 -- Valid BEFORE statement VIEW triggers
 918 CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON main_view
 919 FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_ins_stmt');
 920 CREATE TRIGGER before_upd_stmt_trig BEFORE UPDATE ON main_view
 921 FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_upd_stmt');
 922 CREATE TRIGGER before_del_stmt_trig BEFORE DELETE ON main_view
 923 FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_del_stmt');
 924 -- Valid AFTER statement VIEW triggers
 925 CREATE TRIGGER after_ins_stmt_trig AFTER INSERT ON main_view
 926 FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_ins_stmt');
 927 CREATE TRIGGER after_upd_stmt_trig AFTER UPDATE ON main_view
 928 FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_upd_stmt');
 929 CREATE TRIGGER after_del_stmt_trig AFTER DELETE ON main_view
 930 FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_del_stmt');
 931 \set QUIET false
 932 -- Insert into view using trigger
 933 INSERT INTO main_view VALUES (20, 30);
 934 NOTICE:  main_view BEFORE INSERT STATEMENT (before_view_ins_stmt)
 935 NOTICE:  main_view INSTEAD OF INSERT ROW (instead_of_ins)
 936 NOTICE:  NEW: (20,30)
 937 NOTICE:  trigger_func(before_ins_stmt) called: action = INSERT, when = BEFORE, level = STATEMENT
 938 CONTEXT:  SQL statement "INSERT INTO main_table VALUES (NEW.a, NEW.b)"
 939 PL/pgSQL function "view_trigger" line 17 at SQL statement
 940 NOTICE:  trigger_func(after_ins_stmt) called: action = INSERT, when = AFTER, level = STATEMENT
 941 CONTEXT:  SQL statement "INSERT INTO main_table VALUES (NEW.a, NEW.b)"
 942 PL/pgSQL function "view_trigger" line 17 at SQL statement
 943 NOTICE:  main_view AFTER INSERT STATEMENT (after_view_ins_stmt)
 944 INSERT 0 1
 945 INSERT INTO main_view VALUES (21, 31) RETURNING a, b;
 946 NOTICE:  main_view BEFORE INSERT STATEMENT (before_view_ins_stmt)
 947 NOTICE:  main_view INSTEAD OF INSERT ROW (instead_of_ins)
 948 NOTICE:  NEW: (21,31)
 949 NOTICE:  trigger_func(before_ins_stmt) called: action = INSERT, when = BEFORE, level = STATEMENT
 950 CONTEXT:  SQL statement "INSERT INTO main_table VALUES (NEW.a, NEW.b)"
 951 PL/pgSQL function "view_trigger" line 17 at SQL statement
 952 NOTICE:  trigger_func(after_ins_stmt) called: action = INSERT, when = AFTER, level = STATEMENT
 953 CONTEXT:  SQL statement "INSERT INTO main_table VALUES (NEW.a, NEW.b)"
 954 PL/pgSQL function "view_trigger" line 17 at SQL statement
 955 NOTICE:  main_view AFTER INSERT STATEMENT (after_view_ins_stmt)
 956  a  | b
 957 ----+----
 958  21 | 31
 959 (1 row)
 960
 961 INSERT 0 1
 962 -- Table trigger will prevent updates
 963 UPDATE main_view SET b = 31 WHERE a = 20;
 964 NOTICE:  main_view BEFORE UPDATE STATEMENT (before_view_upd_stmt)
 965 NOTICE:  main_view INSTEAD OF UPDATE ROW (instead_of_upd)
 966 NOTICE:  OLD: (20,30), NEW: (20,31)
 967 NOTICE:  trigger_func(before_upd_a_stmt) called: action = UPDATE, when = BEFORE, level = STATEMENT
 968 CONTEXT:  SQL statement "UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b"
 969 PL/pgSQL function "view_trigger" line 23 at SQL statement
 970 NOTICE:  trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW
 971 CONTEXT:  SQL statement "UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b"
 972 PL/pgSQL function "view_trigger" line 23 at SQL statement
 973 NOTICE:  trigger_func(after_upd_b_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
 974 CONTEXT:  SQL statement "UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b"
 975 PL/pgSQL function "view_trigger" line 23 at SQL statement
 976 NOTICE:  trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
 977 CONTEXT:  SQL statement "UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b"
 978 PL/pgSQL function "view_trigger" line 23 at SQL statement
 979 NOTICE:  main_view AFTER UPDATE STATEMENT (after_view_upd_stmt)
 980 UPDATE 0
 981 UPDATE main_view SET b = 32 WHERE a = 21 AND b = 31 RETURNING a, b;
 982 NOTICE:  main_view BEFORE UPDATE STATEMENT (before_view_upd_stmt)
 983 NOTICE:  main_view INSTEAD OF UPDATE ROW (instead_of_upd)
 984 NOTICE:  OLD: (21,31), NEW: (21,32)
 985 NOTICE:  trigger_func(before_upd_a_stmt) called: action = UPDATE, when = BEFORE, level = STATEMENT
 986 CONTEXT:  SQL statement "UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b"
 987 PL/pgSQL function "view_trigger" line 23 at SQL statement
 988 NOTICE:  trigger_func(before_upd_a_row) called: action = UPDATE, when = BEFORE, level = ROW
 989 CONTEXT:  SQL statement "UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b"
 990 PL/pgSQL function "view_trigger" line 23 at SQL statement
 991 NOTICE:  trigger_func(after_upd_b_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
 992 CONTEXT:  SQL statement "UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b"
 993 PL/pgSQL function "view_trigger" line 23 at SQL statement
 994 NOTICE:  trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
 995 CONTEXT:  SQL statement "UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b"
 996 PL/pgSQL function "view_trigger" line 23 at SQL statement
 997 NOTICE:  main_view AFTER UPDATE STATEMENT (after_view_upd_stmt)
 998  a | b
 999 ---+---
1000 (0 rows)
1001
1002 UPDATE 0
1003 -- Remove table trigger to allow updates
1004 DROP TRIGGER before_upd_a_row_trig ON main_table;
1005 DROP TRIGGER
1006 UPDATE main_view SET b = 31 WHERE a = 20;
1007 NOTICE:  main_view BEFORE UPDATE STATEMENT (before_view_upd_stmt)
1008 NOTICE:  main_view INSTEAD OF UPDATE ROW (instead_of_upd)
1009 NOTICE:  OLD: (20,30), NEW: (20,31)
1010 NOTICE:  trigger_func(before_upd_a_stmt) called: action = UPDATE, when = BEFORE, level = STATEMENT
1011 CONTEXT:  SQL statement "UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b"
1012 PL/pgSQL function "view_trigger" line 23 at SQL statement
1013 NOTICE:  trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW
1014 CONTEXT:  SQL statement "UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b"
1015 PL/pgSQL function "view_trigger" line 23 at SQL statement
1016 NOTICE:  trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW
1017 CONTEXT:  SQL statement "UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b"
1018 PL/pgSQL function "view_trigger" line 23 at SQL statement
1019 NOTICE:  trigger_func(after_upd_b_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
1020 CONTEXT:  SQL statement "UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b"
1021 PL/pgSQL function "view_trigger" line 23 at SQL statement
1022 NOTICE:  trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
1023 CONTEXT:  SQL statement "UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b"
1024 PL/pgSQL function "view_trigger" line 23 at SQL statement
1025 NOTICE:  main_view AFTER UPDATE STATEMENT (after_view_upd_stmt)
1026 UPDATE 1
1027 UPDATE main_view SET b = 32 WHERE a = 21 AND b = 31 RETURNING a, b;
1028 NOTICE:  main_view BEFORE UPDATE STATEMENT (before_view_upd_stmt)
1029 NOTICE:  main_view INSTEAD OF UPDATE ROW (instead_of_upd)
1030 NOTICE:  OLD: (21,31), NEW: (21,32)
1031 NOTICE:  trigger_func(before_upd_a_stmt) called: action = UPDATE, when = BEFORE, level = STATEMENT
1032 CONTEXT:  SQL statement "UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b"
1033 PL/pgSQL function "view_trigger" line 23 at SQL statement
1034 NOTICE:  trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER, level = ROW
1035 CONTEXT:  SQL statement "UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b"
1036 PL/pgSQL function "view_trigger" line 23 at SQL statement
1037 NOTICE:  trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW
1038 CONTEXT:  SQL statement "UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b"
1039 PL/pgSQL function "view_trigger" line 23 at SQL statement
1040 NOTICE:  trigger_func(after_upd_b_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
1041 CONTEXT:  SQL statement "UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b"
1042 PL/pgSQL function "view_trigger" line 23 at SQL statement
1043 NOTICE:  trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
1044 CONTEXT:  SQL statement "UPDATE main_table SET a = NEW.a, b = NEW.b WHERE a = OLD.a AND b = OLD.b"
1045 PL/pgSQL function "view_trigger" line 23 at SQL statement
1046 NOTICE:  main_view AFTER UPDATE STATEMENT (after_view_upd_stmt)
1047  a  | b
1048 ----+----
1049  21 | 32
1050 (1 row)
1051
1052 UPDATE 1
1053 -- Before and after stmt triggers should fire even when no rows are affected
1054 UPDATE main_view SET b = 0 WHERE false;
1055 NOTICE:  main_view BEFORE UPDATE STATEMENT (before_view_upd_stmt)
1056 NOTICE:  main_view AFTER UPDATE STATEMENT (after_view_upd_stmt)
1057 UPDATE 0
1058 -- Delete from view using trigger
1059 DELETE FROM main_view WHERE a IN (20,21);
1060 NOTICE:  main_view BEFORE DELETE STATEMENT (before_view_del_stmt)
1061 NOTICE:  main_view INSTEAD OF DELETE ROW (instead_of_del)
1062 NOTICE:  OLD: (21,10)
1063 NOTICE:  main_view INSTEAD OF DELETE ROW (instead_of_del)
1064 NOTICE:  OLD: (20,31)
1065 NOTICE:  main_view INSTEAD OF DELETE ROW (instead_of_del)
1066 NOTICE:  OLD: (21,32)
1067 NOTICE:  main_view AFTER DELETE STATEMENT (after_view_del_stmt)
1068 DELETE 3
1069 DELETE FROM main_view WHERE a = 31 RETURNING a, b;
1070 NOTICE:  main_view BEFORE DELETE STATEMENT (before_view_del_stmt)
1071 NOTICE:  main_view INSTEAD OF DELETE ROW (instead_of_del)
1072 NOTICE:  OLD: (31,10)
1073 NOTICE:  main_view AFTER DELETE STATEMENT (after_view_del_stmt)
1074  a  | b
1075 ----+----
1076  31 | 10
1077 (1 row)
1078
1079 DELETE 1
1080 \set QUIET true
1081 -- Describe view should list triggers
1082 \d main_view
1083    View "public.main_view"
1084  Column |  Type   | Modifiers
1085 --------+---------+-----------
1086  a      | integer |
1087  b      | integer |
1088 Triggers:
1089     after_del_stmt_trig AFTER DELETE ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_del_stmt')
1090     after_ins_stmt_trig AFTER INSERT ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_ins_stmt')
1091     after_upd_stmt_trig AFTER UPDATE ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_upd_stmt')
1092     before_del_stmt_trig BEFORE DELETE ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_del_stmt')
1093     before_ins_stmt_trig BEFORE INSERT ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_ins_stmt')
1094     before_upd_stmt_trig BEFORE UPDATE ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_upd_stmt')
1095     instead_of_delete_trig INSTEAD OF DELETE ON main_view FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_del')
1096     instead_of_insert_trig INSTEAD OF INSERT ON main_view FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_ins')
1097     instead_of_update_trig INSTEAD OF UPDATE ON main_view FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd')
1098
1099 -- Test dropping view triggers
1100 DROP TRIGGER instead_of_insert_trig ON main_view;
1101 DROP TRIGGER instead_of_delete_trig ON main_view;
1102 \d+ main_view
1103                View "public.main_view"
1104  Column |  Type   | Modifiers | Storage | Description
1105 --------+---------+-----------+---------+-------------
1106  a      | integer |           | plain   |
1107  b      | integer |           | plain   |
1108 View definition:
1109  SELECT main_table.a, main_table.b
1110    FROM main_table;
1111 Triggers:
1112     after_del_stmt_trig AFTER DELETE ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_del_stmt')
1113     after_ins_stmt_trig AFTER INSERT ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_ins_stmt')
1114     after_upd_stmt_trig AFTER UPDATE ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('after_view_upd_stmt')
1115     before_del_stmt_trig BEFORE DELETE ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_del_stmt')
1116     before_ins_stmt_trig BEFORE INSERT ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_ins_stmt')
1117     before_upd_stmt_trig BEFORE UPDATE ON main_view FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_upd_stmt')
1118     instead_of_update_trig INSTEAD OF UPDATE ON main_view FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd')
1119
1120 DROP VIEW main_view;
1121 --
1122 -- Test triggers on a join view
1123 --
1124 CREATE TABLE country_table (
1125     country_id        serial primary key,
1126     country_name    text unique not null,
1127     continent        text not null
1128 );
1129 NOTICE:  CREATE TABLE will create implicit sequence "country_table_country_id_seq" for serial column "country_table.country_id"
1130 NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "country_table_pkey" for table "country_table"
1131 NOTICE:  CREATE TABLE / UNIQUE will create implicit index "country_table_country_name_key" for table "country_table"
1132 INSERT INTO country_table (country_name, continent)
1133     VALUES ('Japan', 'Asia'),
1134            ('UK', 'Europe'),
1135            ('USA', 'North America')
1136     RETURNING *;
1137  country_id | country_name |   continent
1138 ------------+--------------+---------------
1139           1 | Japan        | Asia
1140           2 | UK           | Europe
1141           3 | USA          | North America
1142 (3 rows)
1143
1144 CREATE TABLE city_table (
1145     city_id        serial primary key,
1146     city_name    text not null,
1147     population    bigint,
1148     country_id    int references country_table
1149 );
1150 NOTICE:  CREATE TABLE will create implicit sequence "city_table_city_id_seq" for serial column "city_table.city_id"
1151 NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "city_table_pkey" for table "city_table"
1152 CREATE VIEW city_view AS
1153     SELECT city_id, city_name, population, country_name, continent
1154     FROM city_table ci
1155     LEFT JOIN country_table co ON co.country_id = ci.country_id;
1156 CREATE FUNCTION city_insert() RETURNS trigger LANGUAGE plpgsql AS $$
1157 declare
1158     ctry_id int;
1159 begin
1160     if NEW.country_name IS NOT NULL then
1161         SELECT country_id, continent INTO ctry_id, NEW.continent
1162             FROM country_table WHERE country_name = NEW.country_name;
1163         if NOT FOUND then
1164             raise exception 'No such country: "%"', NEW.country_name;
1165         end if;
1166     else
1167         NEW.continent := NULL;
1168     end if;
1169
1170     if NEW.city_id IS NOT NULL then
1171         INSERT INTO city_table
1172             VALUES(NEW.city_id, NEW.city_name, NEW.population, ctry_id);
1173     else
1174         INSERT INTO city_table(city_name, population, country_id)
1175             VALUES(NEW.city_name, NEW.population, ctry_id)
1176             RETURNING city_id INTO NEW.city_id;
1177     end if;
1178
1179     RETURN NEW;
1180 end;
1181 $$;
1182 CREATE TRIGGER city_insert_trig INSTEAD OF INSERT ON city_view
1183 FOR EACH ROW EXECUTE PROCEDURE city_insert();
1184 CREATE FUNCTION city_delete() RETURNS trigger LANGUAGE plpgsql AS $$
1185 begin
1186     DELETE FROM city_table WHERE city_id = OLD.city_id;
1187     if NOT FOUND then RETURN NULL; end if;
1188     RETURN OLD;
1189 end;
1190 $$;
1191 CREATE TRIGGER city_delete_trig INSTEAD OF DELETE ON city_view
1192 FOR EACH ROW EXECUTE PROCEDURE city_delete();
1193 CREATE FUNCTION city_update() RETURNS trigger LANGUAGE plpgsql AS $$
1194 declare
1195     ctry_id int;
1196 begin
1197     if NEW.country_name IS DISTINCT FROM OLD.country_name then
1198         SELECT country_id, continent INTO ctry_id, NEW.continent
1199             FROM country_table WHERE country_name = NEW.country_name;
1200         if NOT FOUND then
1201             raise exception 'No such country: "%"', NEW.country_name;
1202         end if;
1203
1204         UPDATE city_table SET city_name = NEW.city_name,
1205                               population = NEW.population,
1206                               country_id = ctry_id
1207             WHERE city_id = OLD.city_id;
1208     else
1209         UPDATE city_table SET city_name = NEW.city_name,
1210                               population = NEW.population
1211             WHERE city_id = OLD.city_id;
1212         NEW.continent := OLD.continent;
1213     end if;
1214
1215     if NOT FOUND then RETURN NULL; end if;
1216     RETURN NEW;
1217 end;
1218 $$;
1219 CREATE TRIGGER city_update_trig INSTEAD OF UPDATE ON city_view
1220 FOR EACH ROW EXECUTE PROCEDURE city_update();
1221 \set QUIET false
1222 -- INSERT .. RETURNING
1223 INSERT INTO city_view(city_name) VALUES('Tokyo') RETURNING *;
1224  city_id | city_name | population | country_name | continent
1225 ---------+-----------+------------+--------------+-----------
1226        1 | Tokyo     |            |              |
1227 (1 row)
1228
1229 INSERT 0 1
1230 INSERT INTO city_view(city_name, population) VALUES('London', 7556900) RETURNING *;
1231  city_id | city_name | population | country_name | continent
1232 ---------+-----------+------------+--------------+-----------
1233        2 | London    |    7556900 |              |
1234 (1 row)
1235
1236 INSERT 0 1
1237 INSERT INTO city_view(city_name, country_name) VALUES('Washington DC', 'USA') RETURNING *;
1238  city_id |   city_name   | population | country_name |   continent
1239 ---------+---------------+------------+--------------+---------------
1240        3 | Washington DC |            | USA          | North America
1241 (1 row)
1242
1243 INSERT 0 1
1244 INSERT INTO city_view(city_id, city_name) VALUES(123456, 'New York') RETURNING *;
1245  city_id | city_name | population | country_name | continent
1246 ---------+-----------+------------+--------------+-----------
1247   123456 | New York  |            |              |
1248 (1 row)
1249
1250 INSERT 0 1
1251 INSERT INTO city_view VALUES(234567, 'Birmingham', 1016800, 'UK', 'EU') RETURNING *;
1252  city_id | city_name  | population | country_name | continent
1253 ---------+------------+------------+--------------+-----------
1254   234567 | Birmingham |    1016800 | UK           | Europe
1255 (1 row)
1256
1257 INSERT 0 1
1258 -- UPDATE .. RETURNING
1259 UPDATE city_view SET country_name = 'Japon' WHERE city_name = 'Tokyo'; -- error
1260 ERROR:  No such country: "Japon"
1261 UPDATE city_view SET country_name = 'Japan' WHERE city_name = 'Takyo'; -- no match
1262 UPDATE 0
1263 UPDATE city_view SET country_name = 'Japan' WHERE city_name = 'Tokyo' RETURNING *; -- OK
1264  city_id | city_name | population | country_name | continent
1265 ---------+-----------+------------+--------------+-----------
1266        1 | Tokyo     |            | Japan        | Asia
1267 (1 row)
1268
1269 UPDATE 1
1270 UPDATE city_view SET population = 13010279 WHERE city_name = 'Tokyo' RETURNING *;
1271  city_id | city_name | population | country_name | continent
1272 ---------+-----------+------------+--------------+-----------
1273        1 | Tokyo     |   13010279 | Japan        | Asia
1274 (1 row)
1275
1276 UPDATE 1
1277 UPDATE city_view SET country_name = 'UK' WHERE city_name = 'New York' RETURNING *;
1278  city_id | city_name | population | country_name | continent
1279 ---------+-----------+------------+--------------+-----------
1280   123456 | New York  |            | UK           | Europe
1281 (1 row)
1282
1283 UPDATE 1
1284 UPDATE city_view SET country_name = 'USA', population = 8391881 WHERE city_name = 'New York' RETURNING *;
1285  city_id | city_name | population | country_name |   continent
1286 ---------+-----------+------------+--------------+---------------
1287   123456 | New York  |    8391881 | USA          | North America
1288 (1 row)
1289
1290 UPDATE 1
1291 UPDATE city_view SET continent = 'EU' WHERE continent = 'Europe' RETURNING *;
1292  city_id | city_name  | population | country_name | continent
1293 ---------+------------+------------+--------------+-----------
1294   234567 | Birmingham |    1016800 | UK           | Europe
1295 (1 row)
1296
1297 UPDATE 1
1298 UPDATE city_view v1 SET country_name = v2.country_name FROM city_view v2
1299     WHERE v2.city_name = 'Birmingham' AND v1.city_name = 'London' RETURNING *;
1300  city_id | city_name | population | country_name | continent | city_id | city_name  | population | country_name | continent
1301 ---------+-----------+------------+--------------+-----------+---------+------------+------------+--------------+-----------
1302        2 | London    |    7556900 | UK           | Europe    |  234567 | Birmingham |    1016800 | UK           | Europe
1303 (1 row)
1304
1305 UPDATE 1
1306 -- DELETE .. RETURNING
1307 DELETE FROM city_view WHERE city_name = 'Birmingham' RETURNING *;
1308  city_id | city_name  | population | country_name | continent
1309 ---------+------------+------------+--------------+-----------
1310   234567 | Birmingham |    1016800 | UK           | Europe
1311 (1 row)
1312
1313 DELETE 1
1314 \set QUIET true
1315 -- read-only view with WHERE clause
1316 CREATE VIEW european_city_view AS
1317     SELECT * FROM city_view WHERE continent = 'Europe';
1318 SELECT count(*) FROM european_city_view;
1319  count
1320 -------
1321      1
1322 (1 row)
1323
1324 CREATE FUNCTION no_op_trig_fn() RETURNS trigger LANGUAGE plpgsql
1325 AS 'begin RETURN NULL; end';
1326 CREATE TRIGGER no_op_trig INSTEAD OF INSERT OR UPDATE OR DELETE
1327 ON european_city_view FOR EACH ROW EXECUTE PROCEDURE no_op_trig_fn();
1328 \set QUIET false
1329 INSERT INTO european_city_view VALUES (0, 'x', 10000, 'y', 'z');
1330 INSERT 0 0
1331 UPDATE european_city_view SET population = 10000;
1332 UPDATE 0
1333 DELETE FROM european_city_view;
1334 DELETE 0
1335 \set QUIET true
1336 -- rules bypassing no-op triggers
1337 CREATE RULE european_city_insert_rule AS ON INSERT TO european_city_view
1338 DO INSTEAD INSERT INTO city_view
1339 VALUES (NEW.city_id, NEW.city_name, NEW.population, NEW.country_name, NEW.continent)
1340 RETURNING *;
1341 CREATE RULE european_city_update_rule AS ON UPDATE TO european_city_view
1342 DO INSTEAD UPDATE city_view SET
1343     city_name = NEW.city_name,
1344     population = NEW.population,
1345     country_name = NEW.country_name
1346 WHERE city_id = OLD.city_id
1347 RETURNING NEW.*;
1348 CREATE RULE european_city_delete_rule AS ON DELETE TO european_city_view
1349 DO INSTEAD DELETE FROM city_view WHERE city_id = OLD.city_id RETURNING *;
1350 \set QUIET false
1351 -- INSERT not limited by view's WHERE clause, but UPDATE AND DELETE are
1352 INSERT INTO european_city_view(city_name, country_name)
1353     VALUES ('Cambridge', 'USA') RETURNING *;
1354  city_id | city_name | population | country_name |   continent
1355 ---------+-----------+------------+--------------+---------------
1356        4 | Cambridge |            | USA          | North America
1357 (1 row)
1358
1359 INSERT 0 1
1360 UPDATE european_city_view SET country_name = 'UK'
1361     WHERE city_name = 'Cambridge';
1362 UPDATE 0
1363 DELETE FROM european_city_view WHERE city_name = 'Cambridge';
1364 DELETE 0
1365 -- UPDATE and DELETE via rule and trigger
1366 UPDATE city_view SET country_name = 'UK'
1367     WHERE city_name = 'Cambridge' RETURNING *;
1368  city_id | city_name | population | country_name | continent
1369 ---------+-----------+------------+--------------+-----------
1370        4 | Cambridge |            | UK           | Europe
1371 (1 row)
1372
1373 UPDATE 1
1374 UPDATE european_city_view SET population = 122800
1375     WHERE city_name = 'Cambridge' RETURNING *;
1376  city_id | city_name | population | country_name | continent
1377 ---------+-----------+------------+--------------+-----------
1378        4 | Cambridge |     122800 | UK           | Europe
1379 (1 row)
1380
1381 UPDATE 1
1382 DELETE FROM european_city_view WHERE city_name = 'Cambridge' RETURNING *;
1383  city_id | city_name | population | country_name | continent
1384 ---------+-----------+------------+--------------+-----------
1385        4 | Cambridge |     122800 | UK           | Europe
1386 (1 row)
1387
1388 DELETE 1
1389 -- join UPDATE test
1390 UPDATE city_view v SET population = 599657
1391     FROM city_table ci, country_table co
1392     WHERE ci.city_name = 'Washington DC' and co.country_name = 'USA'
1393     AND v.city_id = ci.city_id AND v.country_name = co.country_name
1394     RETURNING co.country_id, v.country_name,
1395               v.city_id, v.city_name, v.population;
1396  country_id | country_name | city_id |   city_name   | population
1397 ------------+--------------+---------+---------------+------------
1398           3 | USA          |       3 | Washington DC |     599657
1399 (1 row)
1400
1401 UPDATE 1
1402 \set QUIET true
1403 SELECT * FROM city_view;
1404  city_id |   city_name   | population | country_name |   continent
1405 ---------+---------------+------------+--------------+---------------
1406        1 | Tokyo         |   13010279 | Japan        | Asia
1407   123456 | New York      |    8391881 | USA          | North America
1408        2 | London        |    7556900 | UK           | Europe
1409        3 | Washington DC |     599657 | USA          | North America
1410 (4 rows)
1411
1412 DROP TABLE city_table CASCADE;
1413 NOTICE:  drop cascades to 2 other objects
1414 DETAIL:  drop cascades to view city_view
1415 drop cascades to view european_city_view
1416 DROP TABLE country_table;