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 -- bug before 9.3.5 due to sloppy handling of remote-estimate parameters
 204 SELECT * FROM ft1 WHERE c1 = ANY (ARRAY(SELECT c1 FROM ft2 WHERE c1 < 5));
 205 SELECT * FROM ft2 WHERE c1 = ANY (ARRAY(SELECT c1 FROM ft1 WHERE c1 < 5));
 206
 207 -- ===================================================================
 208 -- parameterized queries
 209 -- ===================================================================
 210 -- simple join
 211 PREPARE st1(int, int) AS SELECT t1.c3, t2.c3 FROM ft1 t1, ft2 t2 WHERE t1.c1 = $1 AND t2.c1 = $2;
 212 EXPLAIN (VERBOSE, COSTS false) EXECUTE st1(1, 2);
 213 EXECUTE st1(1, 1);
 214 EXECUTE st1(101, 101);
 215 -- subquery using stable function (can't be sent to remote)
 216 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;
 217 EXPLAIN (VERBOSE, COSTS false) EXECUTE st2(10, 20);
 218 EXECUTE st2(10, 20);
 219 EXECUTE st2(101, 121);
 220 -- subquery using immutable function (can be sent to remote)
 221 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;
 222 EXPLAIN (VERBOSE, COSTS false) EXECUTE st3(10, 20);
 223 EXECUTE st3(10, 20);
 224 EXECUTE st3(20, 30);
 225 -- custom plan should be chosen initially
 226 PREPARE st4(int) AS SELECT * FROM ft1 t1 WHERE t1.c1 = $1;
 227 EXPLAIN (VERBOSE, COSTS false) EXECUTE st4(1);
 228 EXPLAIN (VERBOSE, COSTS false) EXECUTE st4(1);
 229 EXPLAIN (VERBOSE, COSTS false) EXECUTE st4(1);
 230 EXPLAIN (VERBOSE, COSTS false) EXECUTE st4(1);
 231 EXPLAIN (VERBOSE, COSTS false) EXECUTE st4(1);
 232 -- once we try it enough times, should switch to generic plan
 233 EXPLAIN (VERBOSE, COSTS false) EXECUTE st4(1);
 234 -- value of $1 should not be sent to remote
 235 PREPARE st5(user_enum,int) AS SELECT * FROM ft1 t1 WHERE c8 = $1 and c1 = $2;
 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 EXPLAIN (VERBOSE, COSTS false) EXECUTE st5('foo', 1);
 240 EXPLAIN (VERBOSE, COSTS false) EXECUTE st5('foo', 1);
 241 EXPLAIN (VERBOSE, COSTS false) EXECUTE st5('foo', 1);
 242 EXECUTE st5('foo', 1);
 243
 244 -- cleanup
 245 DEALLOCATE st1;
 246 DEALLOCATE st2;
 247 DEALLOCATE st3;
 248 DEALLOCATE st4;
 249 DEALLOCATE st5;
 250
 251 -- ===================================================================
 252 -- used in pl/pgsql function
 253 -- ===================================================================
 254 CREATE OR REPLACE FUNCTION f_test(p_c1 int) RETURNS int AS $$
 255 DECLARE
 256         v_c1 int;
 257 BEGIN
 258     SELECT c1 INTO v_c1 FROM ft1 WHERE c1 = p_c1 LIMIT 1;
 259     PERFORM c1 FROM ft1 WHERE c1 = p_c1 AND p_c1 = v_c1 LIMIT 1;
 260     RETURN v_c1;
 261 END;
 262 $$ LANGUAGE plpgsql;
 263 SELECT f_test(100);
 264 DROP FUNCTION f_test(int);
 265
 266 -- ===================================================================
 267 -- conversion error
 268 -- ===================================================================
 269 ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE int;
 270 SELECT * FROM ft1 WHERE c1 = 1;  -- ERROR
 271 ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE user_enum;
 272
 273 -- ===================================================================
 274 -- subtransaction
 275 --  + local/remote error doesn't break cursor
 276 -- ===================================================================
 277 BEGIN;
 278 DECLARE c CURSOR FOR SELECT * FROM ft1 ORDER BY c1;
 279 FETCH c;
 280 SAVEPOINT s;
 281 ERROR OUT;          -- ERROR
 282 ROLLBACK TO s;
 283 FETCH c;
 284 SAVEPOINT s;
 285 SELECT * FROM ft1 WHERE 1 / (c1 - 1) > 0;  -- ERROR
 286 ROLLBACK TO s;
 287 FETCH c;
 288 SELECT * FROM ft1 ORDER BY c1 LIMIT 1;
 289 COMMIT;
 290
 291 -- ===================================================================
 292 -- test handling of collations
 293 -- ===================================================================
 294 create table loct3 (f1 text collate "C", f2 text);
 295 create foreign table ft3 (f1 text collate "C", f2 text)
 296   server loopback options (table_name 'loct3');
 297
 298 -- can be sent to remote
 299 explain (verbose, costs off) select * from ft3 where f1 = 'foo';
 300 explain (verbose, costs off) select * from ft3 where f1 COLLATE "C" = 'foo';
 301 explain (verbose, costs off) select * from ft3 where f2 = 'foo';
 302 -- can't be sent to remote
 303 explain (verbose, costs off) select * from ft3 where f1 COLLATE "POSIX" = 'foo';
 304 explain (verbose, costs off) select * from ft3 where f1 = 'foo' COLLATE "C";
 305 explain (verbose, costs off) select * from ft3 where f2 COLLATE "C" = 'foo';
 306 explain (verbose, costs off) select * from ft3 where f2 = 'foo' COLLATE "C";
 307
 308 -- ===================================================================
 309 -- test writable foreign table stuff
 310 -- ===================================================================
 311 EXPLAIN (verbose, costs off)
 312 INSERT INTO ft2 (c1,c2,c3) SELECT c1+1000,c2+100, c3 || c3 FROM ft2 LIMIT 20;
 313 INSERT INTO ft2 (c1,c2,c3) SELECT c1+1000,c2+100, c3 || c3 FROM ft2 LIMIT 20;
 314 INSERT INTO ft2 (c1,c2,c3)
 315   VALUES (1101,201,'aaa'), (1102,202,'bbb'), (1103,203,'ccc') RETURNING *;
 316 INSERT INTO ft2 (c1,c2,c3) VALUES (1104,204,'ddd'), (1105,205,'eee');
 317 UPDATE ft2 SET c2 = c2 + 300, c3 = c3 || '_update3' WHERE c1 % 10 = 3;
 318 UPDATE ft2 SET c2 = c2 + 400, c3 = c3 || '_update7' WHERE c1 % 10 = 7 RETURNING *;
 319 EXPLAIN (verbose, costs off)
 320 UPDATE ft2 SET c2 = ft2.c2 + 500, c3 = ft2.c3 || '_update9', c7 = DEFAULT
 321   FROM ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 9;
 322 UPDATE ft2 SET c2 = ft2.c2 + 500, c3 = ft2.c3 || '_update9', c7 = DEFAULT
 323   FROM ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 9;
 324 EXPLAIN (verbose, costs off)
 325   DELETE FROM ft2 WHERE c1 % 10 = 5 RETURNING c1, c4;
 326 DELETE FROM ft2 WHERE c1 % 10 = 5 RETURNING c1, c4;
 327 EXPLAIN (verbose, costs off)
 328 DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 2;
 329 DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 2;
 330 SELECT c1,c2,c3,c4 FROM ft2 ORDER BY c1;
 331
 332 -- Test that trigger on remote table works as expected
 333 CREATE OR REPLACE FUNCTION "S 1".F_BRTRIG() RETURNS trigger AS $$
 334 BEGIN
 335     NEW.c3 = NEW.c3 || '_trig_update';
 336     RETURN NEW;
 337 END;
 338 $$ LANGUAGE plpgsql;
 339 CREATE TRIGGER t1_br_insert BEFORE INSERT OR UPDATE
 340     ON "S 1"."T 1" FOR EACH ROW EXECUTE PROCEDURE "S 1".F_BRTRIG();
 341
 342 INSERT INTO ft2 (c1,c2,c3) VALUES (1208, 818, 'fff') RETURNING *;
 343 INSERT INTO ft2 (c1,c2,c3,c6) VALUES (1218, 818, 'ggg', '(--;') RETURNING *;
 344 UPDATE ft2 SET c2 = c2 + 600 WHERE c1 % 10 = 8 AND c1 < 1200 RETURNING *;
 345
 346 -- Test errors thrown on remote side during update
 347 ALTER TABLE "S 1"."T 1" ADD CONSTRAINT c2positive CHECK (c2 >= 0);
 348
 349 INSERT INTO ft1(c1, c2) VALUES(11, 12);  -- duplicate key
 350 INSERT INTO ft1(c1, c2) VALUES(1111, -2);  -- c2positive
 351 UPDATE ft1 SET c2 = -c2 WHERE c1 = 1;  -- c2positive
 352
 353 -- Test savepoint/rollback behavior
 354 select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
 355 select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1;
 356 begin;
 357 update ft2 set c2 = 42 where c2 = 0;
 358 select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
 359 savepoint s1;
 360 update ft2 set c2 = 44 where c2 = 4;
 361 select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
 362 release savepoint s1;
 363 select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
 364 savepoint s2;
 365 update ft2 set c2 = 46 where c2 = 6;
 366 select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
 367 rollback to savepoint s2;
 368 select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
 369 release savepoint s2;
 370 select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
 371 savepoint s3;
 372 update ft2 set c2 = -2 where c2 = 42 and c1 = 10; -- fail on remote side
 373 rollback to savepoint s3;
 374 select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
 375 release savepoint s3;
 376 select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
 377 -- none of the above is committed yet remotely
 378 select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1;
 379 commit;
 380 select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
 381 select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1;
 382
 383 -- ===================================================================
 384 -- test serial columns (ie, sequence-based defaults)
 385 -- ===================================================================
 386 create table loc1 (f1 serial, f2 text);
 387 create foreign table rem1 (f1 serial, f2 text)
 388   server loopback options(table_name 'loc1');
 389 select pg_catalog.setval('rem1_f1_seq', 10, false);
 390 insert into loc1(f2) values('hi');
 391 insert into rem1(f2) values('hi remote');
 392 insert into loc1(f2) values('bye');
 393 insert into rem1(f2) values('bye remote');
 394 select * from loc1;
 395 select * from rem1;
 396
 397 -- ===================================================================
 398 -- test local triggers
 399 -- ===================================================================
 400
 401 -- Trigger functions "borrowed" from triggers regress test.
 402 CREATE FUNCTION trigger_func() RETURNS trigger LANGUAGE plpgsql AS $$
 403 BEGIN
 404         RAISE NOTICE 'trigger_func(%) called: action = %, when = %, level = %',
 405                 TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL;
 406         RETURN NULL;
 407 END;$$;
 408
 409 CREATE TRIGGER trig_stmt_before BEFORE DELETE OR INSERT OR UPDATE ON rem1
 410         FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func();
 411 CREATE TRIGGER trig_stmt_after AFTER DELETE OR INSERT OR UPDATE ON rem1
 412         FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func();
 413
 414 CREATE OR REPLACE FUNCTION trigger_data()  RETURNS trigger
 415 LANGUAGE plpgsql AS $$
 416
 417 declare
 418         oldnew text[];
 419         relid text;
 420     argstr text;
 421 begin
 422
 423         relid := TG_relid::regclass;
 424         argstr := '';
 425         for i in 0 .. TG_nargs - 1 loop
 426                 if i > 0 then
 427                         argstr := argstr || ', ';
 428                 end if;
 429                 argstr := argstr || TG_argv[i];
 430         end loop;
 431
 432     RAISE NOTICE '%(%) % % % ON %',
 433                 tg_name, argstr, TG_when, TG_level, TG_OP, relid;
 434     oldnew := '{}'::text[];
 435         if TG_OP != 'INSERT' then
 436                 oldnew := array_append(oldnew, format('OLD: %s', OLD));
 437         end if;
 438
 439         if TG_OP != 'DELETE' then
 440                 oldnew := array_append(oldnew, format('NEW: %s', NEW));
 441         end if;
 442
 443     RAISE NOTICE '%', array_to_string(oldnew, ',');
 444
 445         if TG_OP = 'DELETE' then
 446                 return OLD;
 447         else
 448                 return NEW;
 449         end if;
 450 end;
 451 $$;
 452
 453 -- Test basic functionality
 454 CREATE TRIGGER trig_row_before
 455 BEFORE INSERT OR UPDATE OR DELETE ON rem1
 456 FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
 457
 458 CREATE TRIGGER trig_row_after
 459 AFTER INSERT OR UPDATE OR DELETE ON rem1
 460 FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
 461
 462 delete from rem1;
 463 insert into rem1 values(1,'insert');
 464 update rem1 set f2  = 'update' where f1 = 1;
 465 update rem1 set f2 = f2 || f2;
 466
 467
 468 -- cleanup
 469 DROP TRIGGER trig_row_before ON rem1;
 470 DROP TRIGGER trig_row_after ON rem1;
 471 DROP TRIGGER trig_stmt_before ON rem1;
 472 DROP TRIGGER trig_stmt_after ON rem1;
 473
 474 DELETE from rem1;
 475
 476
 477 -- Test WHEN conditions
 478
 479 CREATE TRIGGER trig_row_before_insupd
 480 BEFORE INSERT OR UPDATE ON rem1
 481 FOR EACH ROW
 482 WHEN (NEW.f2 like '%update%')
 483 EXECUTE PROCEDURE trigger_data(23,'skidoo');
 484
 485 CREATE TRIGGER trig_row_after_insupd
 486 AFTER INSERT OR UPDATE ON rem1
 487 FOR EACH ROW
 488 WHEN (NEW.f2 like '%update%')
 489 EXECUTE PROCEDURE trigger_data(23,'skidoo');
 490
 491 -- Insert or update not matching: nothing happens
 492 INSERT INTO rem1 values(1, 'insert');
 493 UPDATE rem1 set f2 = 'test';
 494
 495 -- Insert or update matching: triggers are fired
 496 INSERT INTO rem1 values(2, 'update');
 497 UPDATE rem1 set f2 = 'update update' where f1 = '2';
 498
 499 CREATE TRIGGER trig_row_before_delete
 500 BEFORE DELETE ON rem1
 501 FOR EACH ROW
 502 WHEN (OLD.f2 like '%update%')
 503 EXECUTE PROCEDURE trigger_data(23,'skidoo');
 504
 505 CREATE TRIGGER trig_row_after_delete
 506 AFTER DELETE ON rem1
 507 FOR EACH ROW
 508 WHEN (OLD.f2 like '%update%')
 509 EXECUTE PROCEDURE trigger_data(23,'skidoo');
 510
 511 -- Trigger is fired for f1=2, not for f1=1
 512 DELETE FROM rem1;
 513
 514 -- cleanup
 515 DROP TRIGGER trig_row_before_insupd ON rem1;
 516 DROP TRIGGER trig_row_after_insupd ON rem1;
 517 DROP TRIGGER trig_row_before_delete ON rem1;
 518 DROP TRIGGER trig_row_after_delete ON rem1;
 519
 520
 521 -- Test various RETURN statements in BEFORE triggers.
 522
 523 CREATE FUNCTION trig_row_before_insupdate() RETURNS TRIGGER AS $$
 524   BEGIN
 525     NEW.f2 := NEW.f2 || ' triggered !';
 526     RETURN NEW;
 527   END
 528 $$ language plpgsql;
 529
 530 CREATE TRIGGER trig_row_before_insupd
 531 BEFORE INSERT OR UPDATE ON rem1
 532 FOR EACH ROW EXECUTE PROCEDURE trig_row_before_insupdate();
 533
 534 -- The new values should have 'triggered' appended
 535 INSERT INTO rem1 values(1, 'insert');
 536 SELECT * from loc1;
 537 INSERT INTO rem1 values(2, 'insert') RETURNING f2;
 538 SELECT * from loc1;
 539 UPDATE rem1 set f2 = '';
 540 SELECT * from loc1;
 541 UPDATE rem1 set f2 = 'skidoo' RETURNING f2;
 542 SELECT * from loc1;
 543
 544 DELETE FROM rem1;
 545
 546 -- Add a second trigger, to check that the changes are propagated correctly
 547 -- from trigger to trigger
 548 CREATE TRIGGER trig_row_before_insupd2
 549 BEFORE INSERT OR UPDATE ON rem1
 550 FOR EACH ROW EXECUTE PROCEDURE trig_row_before_insupdate();
 551
 552 INSERT INTO rem1 values(1, 'insert');
 553 SELECT * from loc1;
 554 INSERT INTO rem1 values(2, 'insert') RETURNING f2;
 555 SELECT * from loc1;
 556 UPDATE rem1 set f2 = '';
 557 SELECT * from loc1;
 558 UPDATE rem1 set f2 = 'skidoo' RETURNING f2;
 559 SELECT * from loc1;
 560
 561 DROP TRIGGER trig_row_before_insupd ON rem1;
 562 DROP TRIGGER trig_row_before_insupd2 ON rem1;
 563
 564 DELETE from rem1;
 565
 566 INSERT INTO rem1 VALUES (1, 'test');
 567
 568 -- Test with a trigger returning NULL
 569 CREATE FUNCTION trig_null() RETURNS TRIGGER AS $$
 570   BEGIN
 571     RETURN NULL;
 572   END
 573 $$ language plpgsql;
 574
 575 CREATE TRIGGER trig_null
 576 BEFORE INSERT OR UPDATE OR DELETE ON rem1
 577 FOR EACH ROW EXECUTE PROCEDURE trig_null();
 578
 579 -- Nothing should have changed.
 580 INSERT INTO rem1 VALUES (2, 'test2');
 581
 582 SELECT * from loc1;
 583
 584 UPDATE rem1 SET f2 = 'test2';
 585
 586 SELECT * from loc1;
 587
 588 DELETE from rem1;
 589
 590 SELECT * from loc1;
 591
 592 DROP TRIGGER trig_null ON rem1;
 593 DELETE from rem1;
 594
 595 -- Test a combination of local and remote triggers
 596 CREATE TRIGGER trig_row_before
 597 BEFORE INSERT OR UPDATE OR DELETE ON rem1
 598 FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
 599
 600 CREATE TRIGGER trig_row_after
 601 AFTER INSERT OR UPDATE OR DELETE ON rem1
 602 FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
 603
 604 CREATE TRIGGER trig_local_before BEFORE INSERT OR UPDATE ON loc1
 605 FOR EACH ROW EXECUTE PROCEDURE trig_row_before_insupdate();
 606
 607 INSERT INTO rem1(f2) VALUES ('test');
 608 UPDATE rem1 SET f2 = 'testo';
 609
 610 -- Test returning a system attribute
 611 INSERT INTO rem1(f2) VALUES ('test') RETURNING ctid;