1 SET SEARCH_PATH = public;
2 SET DATESTYLE = 'Postgres, MDY';
4 -- =============================================================================
6 -- =============================================================================
8 CREATE ROLE contrib_regression_role_1;
10 -- create remote database 1
11 CREATE DATABASE contrib_regression_f1;
12 \c contrib_regression_f1
17 update_ts timestamp with time zone,
18 constraint pk_person primary key (id)
21 INSERT INTO person VALUES(1, 'foo', '01-31-2000', '01-31-2000 00:00:00+00:00');
22 INSERT INTO person VALUES(2, 'bar', '01-31-1900', '01-31-1900 00:00:00+00:00');
23 INSERT INTO person VALUES(3, 'buz', NULL, NULL);
26 -- create remote database 2
27 CREATE DATABASE contrib_regression_f2;
28 \c contrib_regression_f2
33 update_ts timestamp with time zone,
34 constraint pk_person primary key (id)
37 INSERT INTO person VALUES(1, 'foo', '01-31-2000', '01-31-2000 00:00:00+00:00');
38 INSERT INTO person VALUES(2, 'bar', '01-31-1900', '01-31-1900 00:00:00+00:00');
39 INSERT INTO person VALUES(3, 'buz', NULL, NULL);
42 -- connect database for regression test
45 -- create FOREIGN DATA WRAPPER for PostgresSQL
46 CREATE FOREIGN DATA WRAPPER contrib_regression_wrapper
47 HANDLER postgresql_fdw_handler
48 VALIDATOR postgresql_fdw_validator;
50 -- create FOREIGN SERVER for remote database 1
51 CREATE SERVER contrib_regression_srv_1
52 FOREIGN DATA WRAPPER contrib_regression_wrapper
53 OPTIONS (host 'localhost', dbname 'contrib_regression_f1');
54 CREATE USER MAPPING FOR PUBLIC SERVER contrib_regression_srv_1;
56 -- create FOREIGN SERVER for remote database 2
57 CREATE SERVER contrib_regression_srv_2
58 FOREIGN DATA WRAPPER contrib_regression_wrapper
59 OPTIONS (host 'localhost', dbname 'contrib_regression_f2');
60 CREATE USER MAPPING FOR PUBLIC SERVER contrib_regression_srv_2;
62 -- Check ALTER FOREIGN TABLE OWNER TO before create various test tables
63 CREATE FOREIGN TABLE ft1 (c1 integer) SERVER contrib_regression_srv_2;
64 ALTER FOREIGN TABLE ft1 OWNER TO contrib_regression_role_1;
66 DROP FOREIGN TABLE ft1;
68 -- create entity of local table with same contents
69 CREATE TABLE person_l (
73 update_ts timestamp with time zone,
74 constraint pk_person_l primary key (id)
77 INSERT INTO person_l VALUES(1, 'foo', '01-31-2000', '01-31-2000 00:00:00+00:00');
78 INSERT INTO person_l VALUES(2, 'bar', '01-31-1900', '01-31-1900 00:00:00+00:00');
79 INSERT INTO person_l VALUES(4, 'bar', NULL, NULL);
82 -- create foreign table which references table 'person'
83 CREATE FOREIGN TABLE person (
87 update_ts timestamp with time zone
88 ) SERVER contrib_regression_srv_1;
89 CREATE FOREIGN TABLE person2 (
93 update_ts timestamp with time zone
94 ) SERVER contrib_regression_srv_2 OPTIONS (nspname 'public', relname 'person');
97 -- =============================================================================
98 -- Misc statement section
99 -- =============================================================================
100 ALTER FOREIGN TABLE person INHERIT person_l;
101 -- row lock via view is not allowed too.
102 CREATE OR REPLACE VIEW v_person AS SELECT * FROM person;
103 SELECT * FROM v_person FOR UPDATE NOWAIT;
105 -- row lock via CTE is not allowed but no error occurs.
106 WITH t AS (SELECT * FROM person) SELECT * FROM t ORDER BY id FOR UPDATE NOWAIT; -- not error
107 -- row lock in CTE is not allowed and an error occurs.
108 WITH t AS (SELECT * FROM person FOR UPDATE) SELECT * FROM t ORDER BY id; -- error
111 ALTER FOREIGN TABLE person NO INHERIT person_l;
113 -- =============================================================================
114 -- Connection cache test section
115 -- XXX: some of these tests should been moved to regression test of core.
116 -- =============================================================================
117 -- clear connection cache
120 -- access foreign table (no result needed)
121 SELECT * FROM person WHERE 1 = 0;
123 -- one connection must be cached
124 SELECT srvname, (user = usename) usename, fdwname
125 FROM pg_foreign_connections ORDER BY srvname;
127 -- access remote database 2 (no result needed)
128 SELECT id, name, birthday FROM person2 WHERE 1 = 0;
130 -- two connection must be cached
131 SELECT srvname, (user = usename) usename, fdwname
132 FROM pg_foreign_connections ORDER BY srvname;
134 -- disconnect from all servers
137 -- connection cache must be empty
138 SELECT srvname, (user = usename) usename, fdwname
139 FROM pg_foreign_connections ORDER BY srvname;
141 -- access remote database 1 and 2 (no result needed)
142 SELECT id, name, birthday FROM person WHERE 1 = 0;
143 SELECT id, name, birthday FROM person2 WHERE 1 = 0;
145 -- two connection must be cached
146 SELECT srvname, (user = usename) usename, fdwname
147 FROM pg_foreign_connections ORDER BY srvname;
149 -- change authorization identifier
150 SET SESSION AUTHORIZATION contrib_regression_role_1;
152 -- connection cache must be empty
153 SELECT srvname, (user = usename) usename, fdwname
154 FROM pg_foreign_connections ORDER BY srvname;
157 RESET SESSION AUTHORIZATION;
158 DROP FOREIGN TABLE person2;
161 -- =============================================================================
162 -- Query test section
163 -- =============================================================================
164 -- all tuples with ORDER BY clause
165 SELECT id, name, birthday,
166 xmin, xmax, cmin, cmax, ctid, (tableoid = 'person'::regclass) tableoid
167 FROM person ORDER BY id;
169 -- operator and function call in SELECT clause
170 SELECT id + 10 id, upper(name) upper_name, birthday FROM person ORDER BY id;
172 -- various join/subquery situations
173 SELECT f.id, f.name, f.birthday, p.id, p.name, p.birthday FROM person f JOIN person_l p ON (f.id = p.id) ORDER BY f.id, p.id;
174 SELECT f.id, f.name, f.birthday, p.id, p.name, p.birthday FROM person f LEFT JOIN person_l p ON (f.id = p.id) ORDER BY f.id, p.id;
175 SELECT f.id, f.name, f.birthday, p.id, p.name, p.birthday FROM person f RIGHT JOIN person_l p ON (f.id = p.id) ORDER BY f.id, p.id;
176 SELECT f.id, f.name, f.birthday, p.id, p.name, p.birthday FROM person f FULL OUTER JOIN person_l p ON (f.id = p.id) ORDER BY f.id, p.id;
177 SELECT id, name, birthday FROM person f WHERE f.id = (SELECT min(p.id) FROM person_l p) ORDER BY f.id;
178 SELECT id, name, birthday FROM person f WHERE (f.id, f.name) IN (SELECT p.id, p.name FROM person_l p) ORDER BY f.id;
180 -- union/intersect/except
181 SELECT id, name, birthday FROM person f UNION SELECT id, name, birthday FROM person_l p ORDER BY id;
182 SELECT name FROM person f INTERSECT SELECT name FROM person_l p ORDER BY name;
183 SELECT name FROM person f EXCEPT SELECT name FROM person_l p ORDER BY name;
185 -- WHERE clause evaluation in the foreign server
186 SELECT id, name, birthday FROM person WHERE id = 1;
187 SELECT id, name, birthday FROM person WHERE birthday IS NULL ORDER BY id;
188 SELECT id, name, birthday FROM person WHERE id IN (1, -1, 5) ORDER BY id;
189 SELECT id, name, birthday FROM person WHERE id IS DISTINCT from 1 ORDER BY id;
190 SELECT id, name, birthday FROM person WHERE ARRAY[0,id,2] = ARRAY[0,1,2] ORDER BY id;
192 -- WHERE clause evaluation in local server
193 SELECT id, name, birthday FROM person WHERE update_ts < '01-31-1999'::date ORDER BY id;
196 SELECT id, name, birthday FROM person f ORDER BY id LIMIT 1 OFFSET 1;
199 PREPARE ST1(integer, integer) AS SELECT $1 param, id, name, birthday FROM person f WHERE f.id = $2;
204 -- =============================================================================
206 -- =============================================================================
207 -- NOT NULL and CHECK constraints in column constraint syntax
208 CREATE FOREIGN TABLE ft2 (c1 integer NOT NULL, c2 text CHECK (length(c2) > 0)) SERVER contrib_regression_srv_1;
211 -- CHECK constraints in table constraint syntax
212 CREATE FOREIGN TABLE ft3 (c1 integer, c2 text, CONSTRAINT ft3_c2_check CHECK (length(c2) > 0)) SERVER contrib_regression_srv_1;
215 -- PRIMARY KEY in column constraint syntax - error
216 CREATE FOREIGN TABLE ft4 (c1 integer PRIMARY KEY) SERVER contrib_regression_srv_1; -- error
218 -- UNIQUE in column constraint syntax - error
219 CREATE FOREIGN TABLE ft4 (c1 integer UNIQUE) SERVER contrib_regression_srv_1; -- error
221 -- FOREIGN KEY in column constraint syntax - error
222 CREATE TABLE t1 (c1 integer PRIMARY KEY);
223 CREATE FOREIGN TABLE ft4 (c1 integer REFERENCES t1 (c1)) SERVER contrib_regression_srv_1; -- error
225 -- PRIMARY KEY in table constraint syntax - error
226 CREATE FOREIGN TABLE ft4 (c1 integer, CONSTRAINT ft4_pkey PRIMARY KEY (c1)) SERVER contrib_regression_srv_1; -- error
228 -- UNIQUE in table constraint syntax - error
229 CREATE FOREIGN TABLE ft4 (c1 integer, CONSTRAINT ft4_c1_unique UNIQUE (c1)) SERVER contrib_regression_srv_1; -- error
231 -- FOREIGN KEY in table constraint syntax - error
232 CREATE FOREIGN TABLE ft4 (c1 integer, CONSTRAINT ft4_c1_fkey FOREIGN KEY (c1) REFERENCES t1 (c1)) SERVER contrib_regression_srv_1; -- error
235 DROP FOREIGN TABLE ft2;
236 DROP FOREIGN TABLE ft3;
239 -- delete all data to use as inherited (parent) table
240 DELETE FROM person_l;
241 -- change table definition
242 \c contrib_regression_f1
243 ALTER TABLE person ADD COLUMN dummy text;
244 UPDATE person SET dummy = 'dummy value';
245 \c contrib_regression
246 ALTER FOREIGN TABLE person INHERIT person_l;
247 ALTER TABLE person_l ADD COLUMN dummy text; -- added to person too
248 CREATE RULE rl_person_insert AS ON INSERT TO person DO INSTEAD INSERT INTO person_l VALUES (NEW.*);
251 -- content of person must be showed
252 INSERT INTO person VALUES (-1, 'FOO', '2100-01-31', null, 'DUMMY');
253 SELECT * FROM person_l ORDER BY id;
255 -- restore table definition
256 \c contrib_regression_f1
257 ALTER TABLE person DROP COLUMN dummy;
258 \c contrib_regression
259 DROP RULE rl_person_insert ON person;
260 ALTER TABLE person_l DROP COLUMN dummy;
261 ALTER FOREIGN TABLE person NO INHERIT person_l;
263 -- no child table, result must be its own content
264 SELECT * FROM person_l ORDER BY id;
266 -- query for foreign table which has no column
267 ALTER FOREIGN TABLE person DROP COLUMN id;
268 ALTER FOREIGN TABLE person DROP COLUMN name;
269 ALTER FOREIGN TABLE person DROP COLUMN birthday;
270 ALTER FOREIGN TABLE person DROP COLUMN update_ts;
271 SELECT * FROM person;
273 -- =============================================================================
275 -- =============================================================================
277 DROP FOREIGN DATA WRAPPER contrib_regression_wrapper CASCADE;
278 DROP DATABASE contrib_regression_f1;
279 DROP DATABASE contrib_regression_f2;
280 DROP ROLE contrib_regression_role_1;