granicus.if.org Git - postgresql/blob - contrib/postgres_fdw/sql/postgres_fdw.sql

   1 -- ===================================================================
   2 -- create FDW objects
   3 -- ===================================================================
   4
   5 CREATE EXTENSION postgres_fdw;
   6
   7 CREATE SERVER testserver1 FOREIGN DATA WRAPPER postgres_fdw;
   8 CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw
   9   OPTIONS (dbname 'contrib_regression');
  10
  11 CREATE USER MAPPING FOR public SERVER testserver1
  12         OPTIONS (user 'value', password 'value');
  13 CREATE USER MAPPING FOR CURRENT_USER SERVER loopback;
  14
  15 -- ===================================================================
  16 -- create objects used through FDW loopback server
  17 -- ===================================================================
  18 CREATE TYPE user_enum AS ENUM ('foo', 'bar', 'buz');
  19 CREATE SCHEMA "S 1";
  20 CREATE TABLE "S 1"."T 1" (
  21         "C 1" int NOT NULL,
  22         c2 int NOT NULL,
  23         c3 text,
  24         c4 timestamptz,
  25         c5 timestamp,
  26         c6 varchar(10),
  27         c7 char(10),
  28         c8 user_enum,
  29         CONSTRAINT t1_pkey PRIMARY KEY ("C 1")
  30 );
  31 CREATE TABLE "S 1"."T 2" (
  32         c1 int NOT NULL,
  33         c2 text,
  34         CONSTRAINT t2_pkey PRIMARY KEY (c1)
  35 );
  36
  37 INSERT INTO "S 1"."T 1"
  38         SELECT id,
  39                id % 10,
  40                to_char(id, 'FM00000'),
  41                '1970-01-01'::timestamptz + ((id % 100) || ' days')::interval,
  42                '1970-01-01'::timestamp + ((id % 100) || ' days')::interval,
  43                id % 10,
  44                id % 10,
  45                'foo'::user_enum
  46         FROM generate_series(1, 1000) id;
  47 INSERT INTO "S 1"."T 2"
  48         SELECT id,
  49                'AAA' || to_char(id, 'FM000')
  50         FROM generate_series(1, 100) id;
  51
  52 ANALYZE "S 1"."T 1";
  53 ANALYZE "S 1"."T 2";
  54
  55 -- ===================================================================
  56 -- create foreign tables
  57 -- ===================================================================
  58 CREATE FOREIGN TABLE ft1 (
  59         c0 int,
  60         c1 int NOT NULL,
  61         c2 int NOT NULL,
  62         c3 text,
  63         c4 timestamptz,
  64         c5 timestamp,
  65         c6 varchar(10),
  66         c7 char(10) default 'ft1',
  67         c8 user_enum
  68 ) SERVER loopback;
  69 ALTER FOREIGN TABLE ft1 DROP COLUMN c0;
  70
  71 CREATE FOREIGN TABLE ft2 (
  72         c1 int NOT NULL,
  73         c2 int NOT NULL,
  74         cx int,
  75         c3 text,
  76         c4 timestamptz,
  77         c5 timestamp,
  78         c6 varchar(10),
  79         c7 char(10) default 'ft2',
  80         c8 user_enum
  81 ) SERVER loopback;
  82 ALTER FOREIGN TABLE ft2 DROP COLUMN cx;
  83
  84 -- ===================================================================
  85 -- tests for validator
  86 -- ===================================================================
  87 -- requiressl, krbsrvname and gsslib are omitted because they depend on
  88 -- configure options
  89 ALTER SERVER testserver1 OPTIONS (
  90         use_remote_estimate 'false',
  91         updatable 'true',
  92         fdw_startup_cost '123.456',
  93         fdw_tuple_cost '0.123',
  94         service 'value',
  95         connect_timeout 'value',
  96         dbname 'value',
  97         host 'value',
  98         hostaddr 'value',
  99         port 'value',
 100         --client_encoding 'value',
 101         application_name 'value',
 102         --fallback_application_name 'value',
 103         keepalives 'value',
 104         keepalives_idle 'value',
 105         keepalives_interval 'value',
 106         -- requiressl 'value',
 107         sslcompression 'value',
 108         sslmode 'value',
 109         sslcert 'value',
 110         sslkey 'value',
 111         sslrootcert 'value',
 112         sslcrl 'value'
 113         --requirepeer 'value',
 114         -- krbsrvname 'value',
 115         -- gsslib 'value',
 116         --replication 'value'
 117 );
 118 ALTER USER MAPPING FOR public SERVER testserver1
 119         OPTIONS (DROP user, DROP password);
 120 ALTER FOREIGN TABLE ft1 OPTIONS (schema_name 'S 1', table_name 'T 1');
 121 ALTER FOREIGN TABLE ft2 OPTIONS (schema_name 'S 1', table_name 'T 1');
 122 ALTER FOREIGN TABLE ft1 ALTER COLUMN c1 OPTIONS (column_name 'C 1');
 123 ALTER FOREIGN TABLE ft2 ALTER COLUMN c1 OPTIONS (column_name 'C 1');
 124 \det+
 125
 126 -- Now we should be able to run ANALYZE.
 127 -- To exercise multiple code paths, we use local stats on ft1
 128 -- and remote-estimate mode on ft2.
 129 ANALYZE ft1;
 130 ALTER FOREIGN TABLE ft2 OPTIONS (use_remote_estimate 'true');
 131
 132 -- ===================================================================
 133 -- simple queries
 134 -- ===================================================================
 135 -- single table, with/without alias
 136 EXPLAIN (COSTS false) SELECT * FROM ft1 ORDER BY c3, c1 OFFSET 100 LIMIT 10;
 137 SELECT * FROM ft1 ORDER BY c3, c1 OFFSET 100 LIMIT 10;
 138 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
 139 SELECT * FROM ft1 t1 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
 140 -- whole-row reference
 141 EXPLAIN (VERBOSE, COSTS false) SELECT t1 FROM ft1 t1 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
 142 SELECT t1 FROM ft1 t1 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
 143 -- empty result
 144 SELECT * FROM ft1 WHERE false;
 145 -- with WHERE clause
 146 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = 101 AND t1.c6 = '1' AND t1.c7 >= '1';
 147 SELECT * FROM ft1 t1 WHERE t1.c1 = 101 AND t1.c6 = '1' AND t1.c7 >= '1';
 148 -- aggregate
 149 SELECT COUNT(*) FROM ft1 t1;
 150 -- join two tables
 151 SELECT t1.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
 152 -- subquery
 153 SELECT * FROM ft1 t1 WHERE t1.c3 IN (SELECT c3 FROM ft2 t2 WHERE c1 <= 10) ORDER BY c1;
 154 -- subquery+MAX
 155 SELECT * FROM ft1 t1 WHERE t1.c3 = (SELECT MAX(c3) FROM ft2 t2) ORDER BY c1;
 156 -- used in CTE
 157 WITH t1 AS (SELECT * FROM ft1 WHERE c1 <= 10) SELECT t2.c1, t2.c2, t2.c3, t2.c4 FROM t1, ft2 t2 WHERE t1.c1 = t2.c1 ORDER BY t1.c1;
 158 -- fixed values
 159 SELECT 'fixed', NULL FROM ft1 t1 WHERE c1 = 1;
 160 -- user-defined operator/function
 161 CREATE FUNCTION postgres_fdw_abs(int) RETURNS int AS $$
 162 BEGIN
 163 RETURN abs($1);
 164 END
 165 $$ LANGUAGE plpgsql IMMUTABLE;
 166 CREATE OPERATOR === (
 167     LEFTARG = int,
 168     RIGHTARG = int,
 169     PROCEDURE = int4eq,
 170     COMMUTATOR = ===,
 171     NEGATOR = !==
 172 );
 173 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = postgres_fdw_abs(t1.c2);
 174 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 === t1.c2;
 175 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = abs(t1.c2);
 176 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = t1.c2;
 177
 178 -- ===================================================================
 179 -- WHERE with remotely-executable conditions
 180 -- ===================================================================
 181 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = 1;         -- Var, OpExpr(b), Const
 182 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = 100 AND t1.c2 = 0; -- BoolExpr
 183 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c1 IS NULL;        -- NullTest
 184 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c1 IS NOT NULL;    -- NullTest
 185 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE round(abs(c1), 0) = 1; -- FuncExpr
 186 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c1 = -c1;          -- OpExpr(l)
 187 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE 1 = c1!;           -- OpExpr(r)
 188 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE (c1 IS NOT NULL) IS DISTINCT FROM (c1 IS NOT NULL); -- DistinctExpr
 189 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c1 = ANY(ARRAY[c2, 1, c1 + 0]); -- ScalarArrayOpExpr
 190 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c1 = (ARRAY[c1,c2,3])[1]; -- ArrayRef
 191 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c6 = E'foo''s\\bar';  -- check special chars
 192 EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c8 = 'foo';  -- can't be sent to remote
 193 -- parameterized remote path
 194 EXPLAIN (VERBOSE, COSTS false)
 195   SELECT * FROM ft2 a, ft2 b WHERE a.c1 = 47 AND b.c1 = a.c2;
 196 SELECT * FROM ft2 a, ft2 b WHERE a.c1 = 47 AND b.c1 = a.c2;
 197 -- check both safe and unsafe join conditions
 198 EXPLAIN (VERBOSE, COSTS false)
 199   SELECT * FROM ft2 a, ft2 b
 200   WHERE a.c2 = 6 AND b.c1 = a.c1 AND a.c8 = 'foo' AND b.c7 = upper(a.c7);
 201 SELECT * FROM ft2 a, ft2 b
 202 WHERE a.c2 = 6 AND b.c1 = a.c1 AND a.c8 = 'foo' AND b.c7 = upper(a.c7);
 203
 204 -- ===================================================================
 205 -- parameterized queries
 206 -- ===================================================================
 207 -- simple join
 208 PREPARE st1(int, int) AS SELECT t1.c3, t2.c3 FROM ft1 t1, ft2 t2 WHERE t1.c1 = $1 AND t2.c1 = $2;
 209 EXPLAIN (VERBOSE, COSTS false) EXECUTE st1(1, 2);
 210 EXECUTE st1(1, 1);
 211 EXECUTE st1(101, 101);
 212 -- subquery using stable function (can't be sent to remote)
 213 PREPARE st2(int) AS SELECT * FROM ft1 t1 WHERE t1.c1 < $2 AND t1.c3 IN (SELECT c3 FROM ft2 t2 WHERE c1 > $1 AND date(c4) = '1970-01-17'::date) ORDER BY c1;
 214 EXPLAIN (VERBOSE, COSTS false) EXECUTE st2(10, 20);
 215 EXECUTE st2(10, 20);
 216 EXECUTE st2(101, 121);
 217 -- subquery using immutable function (can be sent to remote)
 218 PREPARE st3(int) AS SELECT * FROM ft1 t1 WHERE t1.c1 < $2 AND t1.c3 IN (SELECT c3 FROM ft2 t2 WHERE c1 > $1 AND date(c5) = '1970-01-17'::date) ORDER BY c1;
 219 EXPLAIN (VERBOSE, COSTS false) EXECUTE st3(10, 20);
 220 EXECUTE st3(10, 20);
 221 EXECUTE st3(20, 30);
 222 -- custom plan should be chosen initially
 223 PREPARE st4(int) AS SELECT * FROM ft1 t1 WHERE t1.c1 = $1;
 224 EXPLAIN (VERBOSE, COSTS false) EXECUTE st4(1);
 225 EXPLAIN (VERBOSE, COSTS false) EXECUTE st4(1);
 226 EXPLAIN (VERBOSE, COSTS false) EXECUTE st4(1);
 227 EXPLAIN (VERBOSE, COSTS false) EXECUTE st4(1);
 228 EXPLAIN (VERBOSE, COSTS false) EXECUTE st4(1);
 229 -- once we try it enough times, should switch to generic plan
 230 EXPLAIN (VERBOSE, COSTS false) EXECUTE st4(1);
 231 -- value of $1 should not be sent to remote
 232 PREPARE st5(user_enum,int) AS SELECT * FROM ft1 t1 WHERE c8 = $1 and c1 = $2;
 233 EXPLAIN (VERBOSE, COSTS false) EXECUTE st5('foo', 1);
 234 EXPLAIN (VERBOSE, COSTS false) EXECUTE st5('foo', 1);
 235 EXPLAIN (VERBOSE, COSTS false) EXECUTE st5('foo', 1);
 236 EXPLAIN (VERBOSE, COSTS false) EXECUTE st5('foo', 1);
 237 EXPLAIN (VERBOSE, COSTS false) EXECUTE st5('foo', 1);
 238 EXPLAIN (VERBOSE, COSTS false) EXECUTE st5('foo', 1);
 239 EXECUTE st5('foo', 1);
 240
 241 -- cleanup
 242 DEALLOCATE st1;
 243 DEALLOCATE st2;
 244 DEALLOCATE st3;
 245 DEALLOCATE st4;
 246 DEALLOCATE st5;
 247
 248 -- ===================================================================
 249 -- used in pl/pgsql function
 250 -- ===================================================================
 251 CREATE OR REPLACE FUNCTION f_test(p_c1 int) RETURNS int AS $$
 252 DECLARE
 253         v_c1 int;
 254 BEGIN
 255     SELECT c1 INTO v_c1 FROM ft1 WHERE c1 = p_c1 LIMIT 1;
 256     PERFORM c1 FROM ft1 WHERE c1 = p_c1 AND p_c1 = v_c1 LIMIT 1;
 257     RETURN v_c1;
 258 END;
 259 $$ LANGUAGE plpgsql;
 260 SELECT f_test(100);
 261 DROP FUNCTION f_test(int);
 262
 263 -- ===================================================================
 264 -- conversion error
 265 -- ===================================================================
 266 ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE int;
 267 SELECT * FROM ft1 WHERE c1 = 1;  -- ERROR
 268 ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE user_enum;
 269
 270 -- ===================================================================
 271 -- subtransaction
 272 --  + local/remote error doesn't break cursor
 273 -- ===================================================================
 274 BEGIN;
 275 DECLARE c CURSOR FOR SELECT * FROM ft1 ORDER BY c1;
 276 FETCH c;
 277 SAVEPOINT s;
 278 ERROR OUT;          -- ERROR
 279 ROLLBACK TO s;
 280 FETCH c;
 281 SAVEPOINT s;
 282 SELECT * FROM ft1 WHERE 1 / (c1 - 1) > 0;  -- ERROR
 283 ROLLBACK TO s;
 284 FETCH c;
 285 SELECT * FROM ft1 ORDER BY c1 LIMIT 1;
 286 COMMIT;
 287
 288 -- ===================================================================
 289 -- test handling of collations
 290 -- ===================================================================
 291 create table loct3 (f1 text collate "C", f2 text);
 292 create foreign table ft3 (f1 text collate "C", f2 text)
 293   server loopback options (table_name 'loct3');
 294
 295 -- can be sent to remote
 296 explain (verbose, costs off) select * from ft3 where f1 = 'foo';
 297 explain (verbose, costs off) select * from ft3 where f1 COLLATE "C" = 'foo';
 298 explain (verbose, costs off) select * from ft3 where f2 = 'foo';
 299 -- can't be sent to remote
 300 explain (verbose, costs off) select * from ft3 where f1 COLLATE "POSIX" = 'foo';
 301 explain (verbose, costs off) select * from ft3 where f1 = 'foo' COLLATE "C";
 302 explain (verbose, costs off) select * from ft3 where f2 COLLATE "C" = 'foo';
 303 explain (verbose, costs off) select * from ft3 where f2 = 'foo' COLLATE "C";
 304
 305 -- ===================================================================
 306 -- test writable foreign table stuff
 307 -- ===================================================================
 308 EXPLAIN (verbose, costs off)
 309 INSERT INTO ft2 (c1,c2,c3) SELECT c1+1000,c2+100, c3 || c3 FROM ft2 LIMIT 20;
 310 INSERT INTO ft2 (c1,c2,c3) SELECT c1+1000,c2+100, c3 || c3 FROM ft2 LIMIT 20;
 311 INSERT INTO ft2 (c1,c2,c3)
 312   VALUES (1101,201,'aaa'), (1102,202,'bbb'), (1103,203,'ccc') RETURNING *;
 313 INSERT INTO ft2 (c1,c2,c3) VALUES (1104,204,'ddd'), (1105,205,'eee');
 314 UPDATE ft2 SET c2 = c2 + 300, c3 = c3 || '_update3' WHERE c1 % 10 = 3;
 315 UPDATE ft2 SET c2 = c2 + 400, c3 = c3 || '_update7' WHERE c1 % 10 = 7 RETURNING *;
 316 EXPLAIN (verbose, costs off)
 317 UPDATE ft2 SET c2 = ft2.c2 + 500, c3 = ft2.c3 || '_update9', c7 = DEFAULT
 318   FROM ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 9;
 319 UPDATE ft2 SET c2 = ft2.c2 + 500, c3 = ft2.c3 || '_update9', c7 = DEFAULT
 320   FROM ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 9;
 321 EXPLAIN (verbose, costs off)
 322   DELETE FROM ft2 WHERE c1 % 10 = 5 RETURNING c1, c4;
 323 DELETE FROM ft2 WHERE c1 % 10 = 5 RETURNING c1, c4;
 324 EXPLAIN (verbose, costs off)
 325 DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 2;
 326 DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 2;
 327 SELECT c1,c2,c3,c4 FROM ft2 ORDER BY c1;
 328
 329 -- Test that trigger on remote table works as expected
 330 CREATE OR REPLACE FUNCTION "S 1".F_BRTRIG() RETURNS trigger AS $$
 331 BEGIN
 332     NEW.c3 = NEW.c3 || '_trig_update';
 333     RETURN NEW;
 334 END;
 335 $$ LANGUAGE plpgsql;
 336 CREATE TRIGGER t1_br_insert BEFORE INSERT OR UPDATE
 337     ON "S 1"."T 1" FOR EACH ROW EXECUTE PROCEDURE "S 1".F_BRTRIG();
 338
 339 INSERT INTO ft2 (c1,c2,c3) VALUES (1208, 818, 'fff') RETURNING *;
 340 INSERT INTO ft2 (c1,c2,c3,c6) VALUES (1218, 818, 'ggg', '(--;') RETURNING *;
 341 UPDATE ft2 SET c2 = c2 + 600 WHERE c1 % 10 = 8 AND c1 < 1200 RETURNING *;
 342
 343 -- Test errors thrown on remote side during update
 344 ALTER TABLE "S 1"."T 1" ADD CONSTRAINT c2positive CHECK (c2 >= 0);
 345
 346 INSERT INTO ft1(c1, c2) VALUES(11, 12);  -- duplicate key
 347 INSERT INTO ft1(c1, c2) VALUES(1111, -2);  -- c2positive
 348 UPDATE ft1 SET c2 = -c2 WHERE c1 = 1;  -- c2positive
 349
 350 -- Test savepoint/rollback behavior
 351 select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
 352 select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1;
 353 begin;
 354 update ft2 set c2 = 42 where c2 = 0;
 355 select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
 356 savepoint s1;
 357 update ft2 set c2 = 44 where c2 = 4;
 358 select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
 359 release savepoint s1;
 360 select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
 361 savepoint s2;
 362 update ft2 set c2 = 46 where c2 = 6;
 363 select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
 364 rollback to savepoint s2;
 365 select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
 366 release savepoint s2;
 367 select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
 368 savepoint s3;
 369 update ft2 set c2 = -2 where c2 = 42 and c1 = 10; -- fail on remote side
 370 rollback to savepoint s3;
 371 select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
 372 release savepoint s3;
 373 select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
 374 -- none of the above is committed yet remotely
 375 select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1;
 376 commit;
 377 select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
 378 select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1;
 379
 380 -- ===================================================================
 381 -- test serial columns (ie, sequence-based defaults)
 382 -- ===================================================================
 383 create table loc1 (f1 serial, f2 text);
 384 create foreign table rem1 (f1 serial, f2 text)
 385   server loopback options(table_name 'loc1');
 386 select pg_catalog.setval('rem1_f1_seq', 10, false);
 387 insert into loc1(f2) values('hi');
 388 insert into rem1(f2) values('hi remote');
 389 insert into loc1(f2) values('bye');
 390 insert into rem1(f2) values('bye remote');
 391 select * from loc1;
 392 select * from rem1;
 393
 394 -- ===================================================================
 395 -- test local triggers
 396 -- ===================================================================
 397
 398 -- Trigger functions "borrowed" from triggers regress test.
 399 CREATE FUNCTION trigger_func() RETURNS trigger LANGUAGE plpgsql AS $$
 400 BEGIN
 401         RAISE NOTICE 'trigger_func(%) called: action = %, when = %, level = %',
 402                 TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL;
 403         RETURN NULL;
 404 END;$$;
 405
 406 CREATE TRIGGER trig_stmt_before BEFORE DELETE OR INSERT OR UPDATE ON rem1
 407         FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func();
 408 CREATE TRIGGER trig_stmt_after AFTER DELETE OR INSERT OR UPDATE ON rem1
 409         FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func();
 410
 411 CREATE OR REPLACE FUNCTION trigger_data()  RETURNS trigger
 412 LANGUAGE plpgsql AS $$
 413
 414 declare
 415         oldnew text[];
 416         relid text;
 417     argstr text;
 418 begin
 419
 420         relid := TG_relid::regclass;
 421         argstr := '';
 422         for i in 0 .. TG_nargs - 1 loop
 423                 if i > 0 then
 424                         argstr := argstr || ', ';
 425                 end if;
 426                 argstr := argstr || TG_argv[i];
 427         end loop;
 428
 429     RAISE NOTICE '%(%) % % % ON %',
 430                 tg_name, argstr, TG_when, TG_level, TG_OP, relid;
 431     oldnew := '{}'::text[];
 432         if TG_OP != 'INSERT' then
 433                 oldnew := array_append(oldnew, format('OLD: %s', OLD));
 434         end if;
 435
 436         if TG_OP != 'DELETE' then
 437                 oldnew := array_append(oldnew, format('NEW: %s', NEW));
 438         end if;
 439
 440     RAISE NOTICE '%', array_to_string(oldnew, ',');
 441
 442         if TG_OP = 'DELETE' then
 443                 return OLD;
 444         else
 445                 return NEW;
 446         end if;
 447 end;
 448 $$;
 449
 450 -- Test basic functionality
 451 CREATE TRIGGER trig_row_before
 452 BEFORE INSERT OR UPDATE OR DELETE ON rem1
 453 FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
 454
 455 CREATE TRIGGER trig_row_after
 456 AFTER INSERT OR UPDATE OR DELETE ON rem1
 457 FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
 458
 459 delete from rem1;
 460 insert into rem1 values(1,'insert');
 461 update rem1 set f2  = 'update' where f1 = 1;
 462 update rem1 set f2 = f2 || f2;
 463
 464
 465 -- cleanup
 466 DROP TRIGGER trig_row_before ON rem1;
 467 DROP TRIGGER trig_row_after ON rem1;
 468 DROP TRIGGER trig_stmt_before ON rem1;
 469 DROP TRIGGER trig_stmt_after ON rem1;
 470
 471 DELETE from rem1;
 472
 473
 474 -- Test WHEN conditions
 475
 476 CREATE TRIGGER trig_row_before_insupd
 477 BEFORE INSERT OR UPDATE ON rem1
 478 FOR EACH ROW
 479 WHEN (NEW.f2 like '%update%')
 480 EXECUTE PROCEDURE trigger_data(23,'skidoo');
 481
 482 CREATE TRIGGER trig_row_after_insupd
 483 AFTER INSERT OR UPDATE ON rem1
 484 FOR EACH ROW
 485 WHEN (NEW.f2 like '%update%')
 486 EXECUTE PROCEDURE trigger_data(23,'skidoo');
 487
 488 -- Insert or update not matching: nothing happens
 489 INSERT INTO rem1 values(1, 'insert');
 490 UPDATE rem1 set f2 = 'test';
 491
 492 -- Insert or update matching: triggers are fired
 493 INSERT INTO rem1 values(2, 'update');
 494 UPDATE rem1 set f2 = 'update update' where f1 = '2';
 495
 496 CREATE TRIGGER trig_row_before_delete
 497 BEFORE DELETE ON rem1
 498 FOR EACH ROW
 499 WHEN (OLD.f2 like '%update%')
 500 EXECUTE PROCEDURE trigger_data(23,'skidoo');
 501
 502 CREATE TRIGGER trig_row_after_delete
 503 AFTER DELETE ON rem1
 504 FOR EACH ROW
 505 WHEN (OLD.f2 like '%update%')
 506 EXECUTE PROCEDURE trigger_data(23,'skidoo');
 507
 508 -- Trigger is fired for f1=2, not for f1=1
 509 DELETE FROM rem1;
 510
 511 -- cleanup
 512 DROP TRIGGER trig_row_before_insupd ON rem1;
 513 DROP TRIGGER trig_row_after_insupd ON rem1;
 514 DROP TRIGGER trig_row_before_delete ON rem1;
 515 DROP TRIGGER trig_row_after_delete ON rem1;
 516
 517
 518 -- Test various RETURN statements in BEFORE triggers.
 519
 520 CREATE FUNCTION trig_row_before_insupdate() RETURNS TRIGGER AS $$
 521   BEGIN
 522     NEW.f2 := NEW.f2 || ' triggered !';
 523     RETURN NEW;
 524   END
 525 $$ language plpgsql;
 526
 527 CREATE TRIGGER trig_row_before_insupd
 528 BEFORE INSERT OR UPDATE ON rem1
 529 FOR EACH ROW EXECUTE PROCEDURE trig_row_before_insupdate();
 530
 531 -- The new values should have 'triggered' appended
 532 INSERT INTO rem1 values(1, 'insert');
 533 SELECT * from loc1;
 534 INSERT INTO rem1 values(2, 'insert') RETURNING f2;
 535 SELECT * from loc1;
 536 UPDATE rem1 set f2 = '';
 537 SELECT * from loc1;
 538 UPDATE rem1 set f2 = 'skidoo' RETURNING f2;
 539 SELECT * from loc1;
 540
 541 DELETE FROM rem1;
 542
 543 -- Add a second trigger, to check that the changes are propagated correctly
 544 -- from trigger to trigger
 545 CREATE TRIGGER trig_row_before_insupd2
 546 BEFORE INSERT OR UPDATE ON rem1
 547 FOR EACH ROW EXECUTE PROCEDURE trig_row_before_insupdate();
 548
 549 INSERT INTO rem1 values(1, 'insert');
 550 SELECT * from loc1;
 551 INSERT INTO rem1 values(2, 'insert') RETURNING f2;
 552 SELECT * from loc1;
 553 UPDATE rem1 set f2 = '';
 554 SELECT * from loc1;
 555 UPDATE rem1 set f2 = 'skidoo' RETURNING f2;
 556 SELECT * from loc1;
 557
 558 DROP TRIGGER trig_row_before_insupd ON rem1;
 559 DROP TRIGGER trig_row_before_insupd2 ON rem1;
 560
 561 DELETE from rem1;
 562
 563 INSERT INTO rem1 VALUES (1, 'test');
 564
 565 -- Test with a trigger returning NULL
 566 CREATE FUNCTION trig_null() RETURNS TRIGGER AS $$
 567   BEGIN
 568     RETURN NULL;
 569   END
 570 $$ language plpgsql;
 571
 572 CREATE TRIGGER trig_null
 573 BEFORE INSERT OR UPDATE OR DELETE ON rem1
 574 FOR EACH ROW EXECUTE PROCEDURE trig_null();
 575
 576 -- Nothing should have changed.
 577 INSERT INTO rem1 VALUES (2, 'test2');
 578
 579 SELECT * from loc1;
 580
 581 UPDATE rem1 SET f2 = 'test2';
 582
 583 SELECT * from loc1;
 584
 585 DELETE from rem1;
 586
 587 SELECT * from loc1;
 588
 589 DROP TRIGGER trig_null ON rem1;
 590 DELETE from rem1;
 591
 592 -- Test a combination of local and remote triggers
 593 CREATE TRIGGER trig_row_before
 594 BEFORE INSERT OR UPDATE OR DELETE ON rem1
 595 FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
 596
 597 CREATE TRIGGER trig_row_after
 598 AFTER INSERT OR UPDATE OR DELETE ON rem1
 599 FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
 600
 601 CREATE TRIGGER trig_local_before BEFORE INSERT OR UPDATE ON loc1
 602 FOR EACH ROW EXECUTE PROCEDURE trig_row_before_insupdate();
 603
 604 INSERT INTO rem1(f2) VALUES ('test');
 605 UPDATE rem1 SET f2 = 'testo';
 606
 607 -- Test returning a system attribute
 608 INSERT INTO rem1(f2) VALUES ('test') RETURNING ctid;