1 -- Adjust this setting to control where the objects get created.
2 SET search_path = public;
5 -- Define the functions and test data
8 -- Turn off echoing so that expected file does not depend on
9 -- contents of dblink.sql.
10 SET client_min_messages = warning;
14 RESET client_min_messages;
16 CREATE TABLE foo(f1 int, f2 text, f3 text[], primary key (f1,f2));
17 INSERT INTO foo VALUES (0,'a','{"a0","b0","c0"}');
18 INSERT INTO foo VALUES (1,'b','{"a1","b1","c1"}');
19 INSERT INTO foo VALUES (2,'c','{"a2","b2","c2"}');
20 INSERT INTO foo VALUES (3,'d','{"a3","b3","c3"}');
21 INSERT INTO foo VALUES (4,'e','{"a4","b4","c4"}');
22 INSERT INTO foo VALUES (5,'f','{"a5","b5","c5"}');
23 INSERT INTO foo VALUES (6,'g','{"a6","b6","c6"}');
24 INSERT INTO foo VALUES (7,'h','{"a7","b7","c7"}');
25 INSERT INTO foo VALUES (8,'i','{"a8","b8","c8"}');
26 INSERT INTO foo VALUES (9,'j','{"a9","b9","c9"}');
30 -- list the primary key fields
32 FROM dblink_get_pkey('foo');
34 -- build an insert statement based on a local tuple,
35 -- replacing the primary key values with new ones
36 SELECT dblink_build_sql_insert('foo','1 2',2,'{"0", "a"}','{"99", "xyz"}');
37 -- too many pk fields, should fail
38 SELECT dblink_build_sql_insert('foo','1 2 3 4',4,'{"0", "a", "{a0,b0,c0}"}','{"99", "xyz", "{za0,zb0,zc0}"}');
40 -- build an update statement based on a local tuple,
41 -- replacing the primary key values with new ones
42 SELECT dblink_build_sql_update('foo','1 2',2,'{"0", "a"}','{"99", "xyz"}');
43 -- too many pk fields, should fail
44 SELECT dblink_build_sql_update('foo','1 2 3 4',4,'{"0", "a", "{a0,b0,c0}"}','{"99", "xyz", "{za0,zb0,zc0}"}');
46 -- build a delete statement based on a local tuple,
47 SELECT dblink_build_sql_delete('foo','1 2',2,'{"0", "a"}');
48 -- too many pk fields, should fail
49 SELECT dblink_build_sql_delete('foo','1 2 3 4',4,'{"0", "a", "{a0,b0,c0}"}');
51 -- retest using a quoted and schema qualified table
52 CREATE SCHEMA "MySchema";
53 CREATE TABLE "MySchema"."Foo"(f1 int, f2 text, f3 text[], primary key (f1,f2));
54 INSERT INTO "MySchema"."Foo" VALUES (0,'a','{"a0","b0","c0"}');
56 -- list the primary key fields
58 FROM dblink_get_pkey('"MySchema"."Foo"');
60 -- build an insert statement based on a local tuple,
61 -- replacing the primary key values with new ones
62 SELECT dblink_build_sql_insert('"MySchema"."Foo"','1 2',2,'{"0", "a"}','{"99", "xyz"}');
64 -- build an update statement based on a local tuple,
65 -- replacing the primary key values with new ones
66 SELECT dblink_build_sql_update('"MySchema"."Foo"','1 2',2,'{"0", "a"}','{"99", "xyz"}');
68 -- build a delete statement based on a local tuple,
69 SELECT dblink_build_sql_delete('"MySchema"."Foo"','1 2',2,'{"0", "a"}');
73 FROM dblink('dbname=contrib_regression','SELECT * FROM foo') AS t(a int, b text, c text[])
76 -- should generate "connection not available" error
78 FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[])
81 -- create a persistent connection
82 SELECT dblink_connect('dbname=contrib_regression');
84 -- use the persistent connection
86 FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[])
89 -- open a cursor with bad SQL and fail_on_error set to false
90 SELECT dblink_open('rmt_foo_cursor','SELECT * FROM foobar',false);
92 -- reset remote transaction state
93 SELECT dblink_exec('ABORT');
96 SELECT dblink_open('rmt_foo_cursor','SELECT * FROM foo');
99 SELECT dblink_close('rmt_foo_cursor',false);
101 -- open the cursor again
102 SELECT dblink_open('rmt_foo_cursor','SELECT * FROM foo');
106 FROM dblink_fetch('rmt_foo_cursor',4) AS t(a int, b text, c text[]);
109 FROM dblink_fetch('rmt_foo_cursor',4) AS t(a int, b text, c text[]);
111 -- this one only finds two rows left
113 FROM dblink_fetch('rmt_foo_cursor',4) AS t(a int, b text, c text[]);
115 -- intentionally botch a fetch
117 FROM dblink_fetch('rmt_foobar_cursor',4,false) AS t(a int, b text, c text[]);
119 -- reset remote transaction state
120 SELECT dblink_exec('ABORT');
122 -- close the wrong cursor
123 SELECT dblink_close('rmt_foobar_cursor',false);
125 -- should generate 'cursor "rmt_foo_cursor" not found' error
127 FROM dblink_fetch('rmt_foo_cursor',4) AS t(a int, b text, c text[]);
129 -- this time, 'cursor "rmt_foo_cursor" not found' as a notice
131 FROM dblink_fetch('rmt_foo_cursor',4,false) AS t(a int, b text, c text[]);
133 -- close the persistent connection
134 SELECT dblink_disconnect();
136 -- should generate "connection not available" error
138 FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[])
141 -- put more data into our slave table, first using arbitrary connection syntax
142 -- but truncate the actual return value so we can use diff to check for success
143 SELECT substr(dblink_exec('dbname=contrib_regression','INSERT INTO foo VALUES(10,''k'',''{"a10","b10","c10"}'')'),1,6);
145 -- create a persistent connection
146 SELECT dblink_connect('dbname=contrib_regression');
148 -- put more data into our slave table, using persistent connection syntax
149 -- but truncate the actual return value so we can use diff to check for success
150 SELECT substr(dblink_exec('INSERT INTO foo VALUES(11,''l'',''{"a11","b11","c11"}'')'),1,6);
154 FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[]);
158 FROM dblink('SELECT * FROM foobar',false) AS t(a int, b text, c text[]);
161 SELECT dblink_exec('UPDATE foo SET f3[2] = ''b99'' WHERE f1 = 11');
165 FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[])
168 -- botch a change to some other data
169 SELECT dblink_exec('UPDATE foobar SET f3[2] = ''b99'' WHERE f1 = 11',false);
172 SELECT dblink_exec('DELETE FROM foo WHERE f1 = 11');
176 FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[])
179 -- close the persistent connection
180 SELECT dblink_disconnect();
183 -- tests for the new named persistent connection syntax
186 -- should generate "missing "=" after "myconn" in connection info string" error
188 FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[])
191 -- create a named persistent connection
192 SELECT dblink_connect('myconn','dbname=contrib_regression');
194 -- use the named persistent connection
196 FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[])
199 -- use the named persistent connection, but get it wrong
201 FROM dblink('myconn','SELECT * FROM foobar',false) AS t(a int, b text, c text[])
204 -- create a second named persistent connection
205 -- should error with "duplicate connection name"
206 SELECT dblink_connect('myconn','dbname=contrib_regression');
208 -- create a second named persistent connection with a new name
209 SELECT dblink_connect('myconn2','dbname=contrib_regression');
211 -- use the second named persistent connection
213 FROM dblink('myconn2','SELECT * FROM foo') AS t(a int, b text, c text[])
216 -- close the second named persistent connection
217 SELECT dblink_disconnect('myconn2');
219 -- open a cursor incorrectly
220 SELECT dblink_open('myconn','rmt_foo_cursor','SELECT * FROM foobar',false);
222 -- reset remote transaction state
223 SELECT dblink_exec('myconn','ABORT');
225 -- test opening cursor in a transaction
226 SELECT dblink_exec('myconn','BEGIN');
228 -- an open transaction will prevent dblink_open() from opening its own
229 SELECT dblink_open('myconn','rmt_foo_cursor','SELECT * FROM foo');
231 -- this should not commit the transaction because the client opened it
232 SELECT dblink_close('myconn','rmt_foo_cursor');
234 -- this should succeed because we have an open transaction
235 SELECT dblink_exec('myconn','DECLARE xact_test CURSOR FOR SELECT * FROM foo');
237 -- commit remote transaction
238 SELECT dblink_exec('myconn','COMMIT');
240 -- test automatic transactions for multiple cursor opens
241 SELECT dblink_open('myconn','rmt_foo_cursor','SELECT * FROM foo');
244 SELECT dblink_open('myconn','rmt_foo_cursor2','SELECT * FROM foo');
246 -- this should not commit the transaction
247 SELECT dblink_close('myconn','rmt_foo_cursor2');
249 -- this should succeed because we have an open transaction
250 SELECT dblink_exec('myconn','DECLARE xact_test CURSOR FOR SELECT * FROM foo');
252 -- this should commit the transaction
253 SELECT dblink_close('myconn','rmt_foo_cursor');
255 -- this should fail because there is no open transaction
256 SELECT dblink_exec('myconn','DECLARE xact_test CURSOR FOR SELECT * FROM foo');
258 -- reset remote transaction state
259 SELECT dblink_exec('myconn','ABORT');
262 SELECT dblink_open('myconn','rmt_foo_cursor','SELECT * FROM foo');
266 FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]);
269 FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]);
271 -- this one only finds three rows left
273 FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]);
275 -- fetch some data incorrectly
277 FROM dblink_fetch('myconn','rmt_foobar_cursor',4,false) AS t(a int, b text, c text[]);
279 -- reset remote transaction state
280 SELECT dblink_exec('myconn','ABORT');
282 -- should generate 'cursor "rmt_foo_cursor" not found' error
284 FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]);
286 -- close the named persistent connection
287 SELECT dblink_disconnect('myconn');
289 -- should generate "missing "=" after "myconn" in connection info string" error
291 FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[])
294 -- create a named persistent connection
295 SELECT dblink_connect('myconn','dbname=contrib_regression');
297 -- put more data into our slave table, using named persistent connection syntax
298 -- but truncate the actual return value so we can use diff to check for success
299 SELECT substr(dblink_exec('myconn','INSERT INTO foo VALUES(11,''l'',''{"a11","b11","c11"}'')'),1,6);
303 FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[]);
306 SELECT dblink_exec('myconn','UPDATE foo SET f3[2] = ''b99'' WHERE f1 = 11');
310 FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[])
314 SELECT dblink_exec('myconn','DELETE FROM foo WHERE f1 = 11');
318 FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[])
321 -- close the named persistent connection
322 SELECT dblink_disconnect('myconn');
324 -- close the named persistent connection again
325 -- should get 'connection "myconn" not available' error
326 SELECT dblink_disconnect('myconn');
328 -- test asynchronous queries
329 SELECT dblink_connect('dtest1', 'dbname=contrib_regression');
331 dblink_send_query('dtest1', 'select * from foo where f1 < 3') as t1;
333 SELECT dblink_connect('dtest2', 'dbname=contrib_regression');
335 dblink_send_query('dtest2', 'select * from foo where f1 > 2 and f1 < 7') as t1;
337 SELECT dblink_connect('dtest3', 'dbname=contrib_regression');
339 dblink_send_query('dtest3', 'select * from foo where f1 > 6') as t1;
341 CREATE TEMPORARY TABLE result AS
342 (SELECT * from dblink_get_result('dtest1') as t1(f1 int, f2 text, f3 text[]))
344 (SELECT * from dblink_get_result('dtest2') as t2(f1 int, f2 text, f3 text[]))
346 (SELECT * from dblink_get_result('dtest3') as t3(f1 int, f2 text, f3 text[]))
349 -- dblink_get_connections returns an array with elements in a machine-dependent
350 -- ordering, so we must resort to unnesting and sorting for a stable result
351 create function unnest(anyarray) returns setof anyelement
352 language sql strict immutable as $$
353 select $1[i] from generate_series(array_lower($1,1), array_upper($1,1)) as i
356 SELECT * FROM unnest(dblink_get_connections()) ORDER BY 1;
358 SELECT dblink_is_busy('dtest1');
360 SELECT dblink_disconnect('dtest1');
361 SELECT dblink_disconnect('dtest2');
362 SELECT dblink_disconnect('dtest3');
364 SELECT * from result;
366 SELECT dblink_connect('dtest1', 'dbname=contrib_regression');
368 dblink_send_query('dtest1', 'select * from foo where f1 < 3') as t1;
370 SELECT dblink_cancel_query('dtest1');
371 SELECT dblink_error_message('dtest1');
372 SELECT dblink_disconnect('dtest1');
374 -- test foreign data wrapper functionality
375 CREATE USER dblink_regression_test;
377 CREATE FOREIGN DATA WRAPPER postgresql;
378 CREATE SERVER fdtest FOREIGN DATA WRAPPER postgresql OPTIONS (dbname 'contrib_regression');
379 CREATE USER MAPPING FOR public SERVER fdtest;
380 GRANT USAGE ON FOREIGN SERVER fdtest TO dblink_regression_test;
381 GRANT EXECUTE ON FUNCTION dblink_connect_u(text, text) TO dblink_regression_test;
383 \set ORIGINAL_USER :USER
384 \c - dblink_regression_test
386 SELECT dblink_connect('myconn', 'fdtest');
388 SELECT dblink_connect_u('myconn', 'fdtest');
389 SELECT * FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[]);
392 REVOKE USAGE ON FOREIGN SERVER fdtest FROM dblink_regression_test;
393 REVOKE EXECUTE ON FUNCTION dblink_connect_u(text, text) FROM dblink_regression_test;
394 DROP USER dblink_regression_test;
395 DROP USER MAPPING FOR public SERVER fdtest;
397 DROP FOREIGN DATA WRAPPER postgresql;
399 -- test asynchronous notifications
400 SELECT dblink_connect('dbname=contrib_regression');
402 --should return listen
403 SELECT dblink_exec('LISTEN regression');
404 --should return listen
405 SELECT dblink_exec('LISTEN foobar');
407 SELECT dblink_exec('NOTIFY regression');
408 SELECT dblink_exec('NOTIFY foobar');
410 SELECT notify_name, be_pid = (select t.be_pid from dblink('select pg_backend_pid()') as t(be_pid int)) AS is_self_notify, extra from dblink_get_notify();
412 SELECT * from dblink_get_notify();
414 SELECT dblink_disconnect();
416 -- test dropped columns in dblink_build_sql_insert, dblink_build_sql_update
417 CREATE TEMP TABLE test_dropped
419 col1 INT NOT NULL DEFAULT 111,
420 id SERIAL PRIMARY KEY,
421 col2 INT NOT NULL DEFAULT 112,
422 col2b INT NOT NULL DEFAULT 113
425 INSERT INTO test_dropped VALUES(default);
427 ALTER TABLE test_dropped
430 ADD COLUMN col3 VARCHAR(10) NOT NULL DEFAULT 'foo',
431 ADD COLUMN col4 INT NOT NULL DEFAULT 42;
433 SELECT dblink_build_sql_insert('test_dropped', '1', 1,
434 ARRAY['1'::TEXT], ARRAY['2'::TEXT]);
436 SELECT dblink_build_sql_update('test_dropped', '1', 1,
437 ARRAY['1'::TEXT], ARRAY['2'::TEXT]);
439 SELECT dblink_build_sql_delete('test_dropped', '1', 1,