1CREATE EXTENSION dblink; 2-- want context for notices 3\set SHOW_CONTEXT always 4CREATE TABLE foo(f1 int, f2 text, f3 text[], primary key (f1,f2)); 5INSERT INTO foo VALUES (0,'a','{"a0","b0","c0"}'); 6INSERT INTO foo VALUES (1,'b','{"a1","b1","c1"}'); 7INSERT INTO foo VALUES (2,'c','{"a2","b2","c2"}'); 8INSERT INTO foo VALUES (3,'d','{"a3","b3","c3"}'); 9INSERT INTO foo VALUES (4,'e','{"a4","b4","c4"}'); 10INSERT INTO foo VALUES (5,'f','{"a5","b5","c5"}'); 11INSERT INTO foo VALUES (6,'g','{"a6","b6","c6"}'); 12INSERT INTO foo VALUES (7,'h','{"a7","b7","c7"}'); 13INSERT INTO foo VALUES (8,'i','{"a8","b8","c8"}'); 14INSERT INTO foo VALUES (9,'j','{"a9","b9","c9"}'); 15-- misc utilities 16-- list the primary key fields 17SELECT * 18FROM dblink_get_pkey('foo'); 19 position | colname 20----------+--------- 21 1 | f1 22 2 | f2 23(2 rows) 24 25-- build an insert statement based on a local tuple, 26-- replacing the primary key values with new ones 27SELECT dblink_build_sql_insert('foo','1 2',2,'{"0", "a"}','{"99", "xyz"}'); 28 dblink_build_sql_insert 29----------------------------------------------------------- 30 INSERT INTO foo(f1,f2,f3) VALUES('99','xyz','{a0,b0,c0}') 31(1 row) 32 33-- too many pk fields, should fail 34SELECT dblink_build_sql_insert('foo','1 2 3 4',4,'{"0", "a", "{a0,b0,c0}"}','{"99", "xyz", "{za0,zb0,zc0}"}'); 35ERROR: invalid attribute number 4 36-- build an update statement based on a local tuple, 37-- replacing the primary key values with new ones 38SELECT dblink_build_sql_update('foo','1 2',2,'{"0", "a"}','{"99", "xyz"}'); 39 dblink_build_sql_update 40---------------------------------------------------------------------------------------- 41 UPDATE foo SET f1 = '99', f2 = 'xyz', f3 = '{a0,b0,c0}' WHERE f1 = '99' AND f2 = 'xyz' 42(1 row) 43 44-- too many pk fields, should fail 45SELECT dblink_build_sql_update('foo','1 2 3 4',4,'{"0", "a", "{a0,b0,c0}"}','{"99", "xyz", "{za0,zb0,zc0}"}'); 46ERROR: invalid attribute number 4 47-- build a delete statement based on a local tuple, 48SELECT dblink_build_sql_delete('foo','1 2',2,'{"0", "a"}'); 49 dblink_build_sql_delete 50--------------------------------------------- 51 DELETE FROM foo WHERE f1 = '0' AND f2 = 'a' 52(1 row) 53 54-- too many pk fields, should fail 55SELECT dblink_build_sql_delete('foo','1 2 3 4',4,'{"0", "a", "{a0,b0,c0}"}'); 56ERROR: invalid attribute number 4 57-- retest using a quoted and schema qualified table 58CREATE SCHEMA "MySchema"; 59CREATE TABLE "MySchema"."Foo"(f1 int, f2 text, f3 text[], primary key (f1,f2)); 60INSERT INTO "MySchema"."Foo" VALUES (0,'a','{"a0","b0","c0"}'); 61-- list the primary key fields 62SELECT * 63FROM dblink_get_pkey('"MySchema"."Foo"'); 64 position | colname 65----------+--------- 66 1 | f1 67 2 | f2 68(2 rows) 69 70-- build an insert statement based on a local tuple, 71-- replacing the primary key values with new ones 72SELECT dblink_build_sql_insert('"MySchema"."Foo"','1 2',2,'{"0", "a"}','{"99", "xyz"}'); 73 dblink_build_sql_insert 74------------------------------------------------------------------------ 75 INSERT INTO "MySchema"."Foo"(f1,f2,f3) VALUES('99','xyz','{a0,b0,c0}') 76(1 row) 77 78-- build an update statement based on a local tuple, 79-- replacing the primary key values with new ones 80SELECT dblink_build_sql_update('"MySchema"."Foo"','1 2',2,'{"0", "a"}','{"99", "xyz"}'); 81 dblink_build_sql_update 82----------------------------------------------------------------------------------------------------- 83 UPDATE "MySchema"."Foo" SET f1 = '99', f2 = 'xyz', f3 = '{a0,b0,c0}' WHERE f1 = '99' AND f2 = 'xyz' 84(1 row) 85 86-- build a delete statement based on a local tuple, 87SELECT dblink_build_sql_delete('"MySchema"."Foo"','1 2',2,'{"0", "a"}'); 88 dblink_build_sql_delete 89---------------------------------------------------------- 90 DELETE FROM "MySchema"."Foo" WHERE f1 = '0' AND f2 = 'a' 91(1 row) 92 93CREATE FUNCTION connection_parameters() RETURNS text LANGUAGE SQL AS $f$ 94 SELECT $$dbname='$$||current_database()||$$' port=$$||current_setting('port'); 95$f$; 96-- regular old dblink 97SELECT * 98FROM dblink(connection_parameters(),'SELECT * FROM foo') AS t(a int, b text, c text[]) 99WHERE t.a > 7; 100 a | b | c 101---+---+------------ 102 8 | i | {a8,b8,c8} 103 9 | j | {a9,b9,c9} 104(2 rows) 105 106-- should generate "connection not available" error 107SELECT * 108FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[]) 109WHERE t.a > 7; 110ERROR: connection not available 111-- The first-level connection's backend will crash on exit given OpenLDAP 112-- [2.4.24, 2.4.31]. We won't see evidence of any crash until the victim 113-- process terminates and the postmaster responds. If process termination 114-- entails writing a core dump, that can take awhile. Wait for the process to 115-- vanish. At that point, the postmaster has called waitpid() on the crashed 116-- process, and it will accept no new connections until it has reinitialized 117-- the cluster. (We can't exploit pg_stat_activity, because the crash happens 118-- after the backend updates shared memory to reflect its impending exit.) 119DO $pl$ 120DECLARE 121 detail text; 122BEGIN 123 PERFORM wait_pid(crash_pid) 124 FROM dblink(connection_parameters(), $$ 125 SELECT pg_backend_pid() FROM dblink( 126 'service=test_ldap '||connection_parameters(), 127 -- This string concatenation is a hack to shoehorn a 128 -- set_pgservicefile call into the SQL statement. 129 'SELECT 1' || set_pgservicefile('pg_service.conf') 130 ) t(c int) 131 $$) AS t(crash_pid int); 132EXCEPTION WHEN OTHERS THEN 133 GET STACKED DIAGNOSTICS detail = PG_EXCEPTION_DETAIL; 134 -- Expected error in a non-LDAP build. 135 IF NOT detail LIKE 'syntax error in service file%' THEN RAISE; END IF; 136END 137$pl$; 138-- create a persistent connection 139SELECT dblink_connect(connection_parameters()); 140 dblink_connect 141---------------- 142 OK 143(1 row) 144 145-- use the persistent connection 146SELECT * 147FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[]) 148WHERE t.a > 7; 149 a | b | c 150---+---+------------ 151 8 | i | {a8,b8,c8} 152 9 | j | {a9,b9,c9} 153(2 rows) 154 155-- open a cursor with bad SQL and fail_on_error set to false 156SELECT dblink_open('rmt_foo_cursor','SELECT * FROM foobar',false); 157NOTICE: relation "foobar" does not exist 158CONTEXT: Error occurred on dblink connection named "unnamed": could not open cursor. 159 dblink_open 160------------- 161 ERROR 162(1 row) 163 164-- reset remote transaction state 165SELECT dblink_exec('ABORT'); 166 dblink_exec 167------------- 168 ROLLBACK 169(1 row) 170 171-- open a cursor 172SELECT dblink_open('rmt_foo_cursor','SELECT * FROM foo'); 173 dblink_open 174------------- 175 OK 176(1 row) 177 178-- close the cursor 179SELECT dblink_close('rmt_foo_cursor',false); 180 dblink_close 181-------------- 182 OK 183(1 row) 184 185-- open the cursor again 186SELECT dblink_open('rmt_foo_cursor','SELECT * FROM foo'); 187 dblink_open 188------------- 189 OK 190(1 row) 191 192-- fetch some data 193SELECT * 194FROM dblink_fetch('rmt_foo_cursor',4) AS t(a int, b text, c text[]); 195 a | b | c 196---+---+------------ 197 0 | a | {a0,b0,c0} 198 1 | b | {a1,b1,c1} 199 2 | c | {a2,b2,c2} 200 3 | d | {a3,b3,c3} 201(4 rows) 202 203SELECT * 204FROM dblink_fetch('rmt_foo_cursor',4) AS t(a int, b text, c text[]); 205 a | b | c 206---+---+------------ 207 4 | e | {a4,b4,c4} 208 5 | f | {a5,b5,c5} 209 6 | g | {a6,b6,c6} 210 7 | h | {a7,b7,c7} 211(4 rows) 212 213-- this one only finds two rows left 214SELECT * 215FROM dblink_fetch('rmt_foo_cursor',4) AS t(a int, b text, c text[]); 216 a | b | c 217---+---+------------ 218 8 | i | {a8,b8,c8} 219 9 | j | {a9,b9,c9} 220(2 rows) 221 222-- intentionally botch a fetch 223SELECT * 224FROM dblink_fetch('rmt_foobar_cursor',4,false) AS t(a int, b text, c text[]); 225NOTICE: cursor "rmt_foobar_cursor" does not exist 226CONTEXT: Error occurred on dblink connection named "unnamed": could not fetch from cursor. 227 a | b | c 228---+---+--- 229(0 rows) 230 231-- reset remote transaction state 232SELECT dblink_exec('ABORT'); 233 dblink_exec 234------------- 235 ROLLBACK 236(1 row) 237 238-- close the wrong cursor 239SELECT dblink_close('rmt_foobar_cursor',false); 240NOTICE: cursor "rmt_foobar_cursor" does not exist 241CONTEXT: Error occurred on dblink connection named "unnamed": could not close cursor. 242 dblink_close 243-------------- 244 ERROR 245(1 row) 246 247-- should generate 'cursor "rmt_foo_cursor" not found' error 248SELECT * 249FROM dblink_fetch('rmt_foo_cursor',4) AS t(a int, b text, c text[]); 250ERROR: cursor "rmt_foo_cursor" does not exist 251CONTEXT: Error occurred on dblink connection named "unnamed": could not fetch from cursor. 252-- this time, 'cursor "rmt_foo_cursor" not found' as a notice 253SELECT * 254FROM dblink_fetch('rmt_foo_cursor',4,false) AS t(a int, b text, c text[]); 255NOTICE: cursor "rmt_foo_cursor" does not exist 256CONTEXT: Error occurred on dblink connection named "unnamed": could not fetch from cursor. 257 a | b | c 258---+---+--- 259(0 rows) 260 261-- close the persistent connection 262SELECT dblink_disconnect(); 263 dblink_disconnect 264------------------- 265 OK 266(1 row) 267 268-- should generate "connection not available" error 269SELECT * 270FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[]) 271WHERE t.a > 7; 272ERROR: connection not available 273-- put more data into our table, first using arbitrary connection syntax 274-- but truncate the actual return value so we can use diff to check for success 275SELECT substr(dblink_exec(connection_parameters(),'INSERT INTO foo VALUES(10,''k'',''{"a10","b10","c10"}'')'),1,6); 276 substr 277-------- 278 INSERT 279(1 row) 280 281-- create a persistent connection 282SELECT dblink_connect(connection_parameters()); 283 dblink_connect 284---------------- 285 OK 286(1 row) 287 288-- put more data into our table, using persistent connection syntax 289-- but truncate the actual return value so we can use diff to check for success 290SELECT substr(dblink_exec('INSERT INTO foo VALUES(11,''l'',''{"a11","b11","c11"}'')'),1,6); 291 substr 292-------- 293 INSERT 294(1 row) 295 296-- let's see it 297SELECT * 298FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[]); 299 a | b | c 300----+---+--------------- 301 0 | a | {a0,b0,c0} 302 1 | b | {a1,b1,c1} 303 2 | c | {a2,b2,c2} 304 3 | d | {a3,b3,c3} 305 4 | e | {a4,b4,c4} 306 5 | f | {a5,b5,c5} 307 6 | g | {a6,b6,c6} 308 7 | h | {a7,b7,c7} 309 8 | i | {a8,b8,c8} 310 9 | j | {a9,b9,c9} 311 10 | k | {a10,b10,c10} 312 11 | l | {a11,b11,c11} 313(12 rows) 314 315-- bad remote select 316SELECT * 317FROM dblink('SELECT * FROM foobar',false) AS t(a int, b text, c text[]); 318NOTICE: relation "foobar" does not exist 319CONTEXT: Error occurred on dblink connection named "unnamed": could not execute query. 320 a | b | c 321---+---+--- 322(0 rows) 323 324-- change some data 325SELECT dblink_exec('UPDATE foo SET f3[2] = ''b99'' WHERE f1 = 11'); 326 dblink_exec 327------------- 328 UPDATE 1 329(1 row) 330 331-- let's see it 332SELECT * 333FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[]) 334WHERE a = 11; 335 a | b | c 336----+---+--------------- 337 11 | l | {a11,b99,c11} 338(1 row) 339 340-- botch a change to some other data 341SELECT dblink_exec('UPDATE foobar SET f3[2] = ''b99'' WHERE f1 = 11',false); 342NOTICE: relation "foobar" does not exist 343CONTEXT: Error occurred on dblink connection named "unnamed": could not execute command. 344 dblink_exec 345------------- 346 ERROR 347(1 row) 348 349-- delete some data 350SELECT dblink_exec('DELETE FROM foo WHERE f1 = 11'); 351 dblink_exec 352------------- 353 DELETE 1 354(1 row) 355 356-- let's see it 357SELECT * 358FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[]) 359WHERE a = 11; 360 a | b | c 361---+---+--- 362(0 rows) 363 364-- close the persistent connection 365SELECT dblink_disconnect(); 366 dblink_disconnect 367------------------- 368 OK 369(1 row) 370 371-- 372-- tests for the new named persistent connection syntax 373-- 374-- should generate "missing "=" after "myconn" in connection info string" error 375SELECT * 376FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[]) 377WHERE t.a > 7; 378ERROR: could not establish connection 379DETAIL: missing "=" after "myconn" in connection info string 380-- create a named persistent connection 381SELECT dblink_connect('myconn',connection_parameters()); 382 dblink_connect 383---------------- 384 OK 385(1 row) 386 387-- use the named persistent connection 388SELECT * 389FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[]) 390WHERE t.a > 7; 391 a | b | c 392----+---+--------------- 393 8 | i | {a8,b8,c8} 394 9 | j | {a9,b9,c9} 395 10 | k | {a10,b10,c10} 396(3 rows) 397 398-- use the named persistent connection, but get it wrong 399SELECT * 400FROM dblink('myconn','SELECT * FROM foobar',false) AS t(a int, b text, c text[]) 401WHERE t.a > 7; 402NOTICE: relation "foobar" does not exist 403CONTEXT: Error occurred on dblink connection named "myconn": could not execute query. 404 a | b | c 405---+---+--- 406(0 rows) 407 408-- create a second named persistent connection 409-- should error with "duplicate connection name" 410SELECT dblink_connect('myconn',connection_parameters()); 411ERROR: duplicate connection name 412-- create a second named persistent connection with a new name 413SELECT dblink_connect('myconn2',connection_parameters()); 414 dblink_connect 415---------------- 416 OK 417(1 row) 418 419-- use the second named persistent connection 420SELECT * 421FROM dblink('myconn2','SELECT * FROM foo') AS t(a int, b text, c text[]) 422WHERE t.a > 7; 423 a | b | c 424----+---+--------------- 425 8 | i | {a8,b8,c8} 426 9 | j | {a9,b9,c9} 427 10 | k | {a10,b10,c10} 428(3 rows) 429 430-- close the second named persistent connection 431SELECT dblink_disconnect('myconn2'); 432 dblink_disconnect 433------------------- 434 OK 435(1 row) 436 437-- open a cursor incorrectly 438SELECT dblink_open('myconn','rmt_foo_cursor','SELECT * FROM foobar',false); 439NOTICE: relation "foobar" does not exist 440CONTEXT: Error occurred on dblink connection named "myconn": could not open cursor. 441 dblink_open 442------------- 443 ERROR 444(1 row) 445 446-- reset remote transaction state 447SELECT dblink_exec('myconn','ABORT'); 448 dblink_exec 449------------- 450 ROLLBACK 451(1 row) 452 453-- test opening cursor in a transaction 454SELECT dblink_exec('myconn','BEGIN'); 455 dblink_exec 456------------- 457 BEGIN 458(1 row) 459 460-- an open transaction will prevent dblink_open() from opening its own 461SELECT dblink_open('myconn','rmt_foo_cursor','SELECT * FROM foo'); 462 dblink_open 463------------- 464 OK 465(1 row) 466 467-- this should not commit the transaction because the client opened it 468SELECT dblink_close('myconn','rmt_foo_cursor'); 469 dblink_close 470-------------- 471 OK 472(1 row) 473 474-- this should succeed because we have an open transaction 475SELECT dblink_exec('myconn','DECLARE xact_test CURSOR FOR SELECT * FROM foo'); 476 dblink_exec 477---------------- 478 DECLARE CURSOR 479(1 row) 480 481-- commit remote transaction 482SELECT dblink_exec('myconn','COMMIT'); 483 dblink_exec 484------------- 485 COMMIT 486(1 row) 487 488-- test automatic transactions for multiple cursor opens 489SELECT dblink_open('myconn','rmt_foo_cursor','SELECT * FROM foo'); 490 dblink_open 491------------- 492 OK 493(1 row) 494 495-- the second cursor 496SELECT dblink_open('myconn','rmt_foo_cursor2','SELECT * FROM foo'); 497 dblink_open 498------------- 499 OK 500(1 row) 501 502-- this should not commit the transaction 503SELECT dblink_close('myconn','rmt_foo_cursor2'); 504 dblink_close 505-------------- 506 OK 507(1 row) 508 509-- this should succeed because we have an open transaction 510SELECT dblink_exec('myconn','DECLARE xact_test CURSOR FOR SELECT * FROM foo'); 511 dblink_exec 512---------------- 513 DECLARE CURSOR 514(1 row) 515 516-- this should commit the transaction 517SELECT dblink_close('myconn','rmt_foo_cursor'); 518 dblink_close 519-------------- 520 OK 521(1 row) 522 523-- this should fail because there is no open transaction 524SELECT dblink_exec('myconn','DECLARE xact_test CURSOR FOR SELECT * FROM foo'); 525ERROR: DECLARE CURSOR can only be used in transaction blocks 526CONTEXT: Error occurred on dblink connection named "myconn": could not execute command. 527-- reset remote transaction state 528SELECT dblink_exec('myconn','ABORT'); 529 dblink_exec 530------------- 531 ROLLBACK 532(1 row) 533 534-- open a cursor 535SELECT dblink_open('myconn','rmt_foo_cursor','SELECT * FROM foo'); 536 dblink_open 537------------- 538 OK 539(1 row) 540 541-- fetch some data 542SELECT * 543FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]); 544 a | b | c 545---+---+------------ 546 0 | a | {a0,b0,c0} 547 1 | b | {a1,b1,c1} 548 2 | c | {a2,b2,c2} 549 3 | d | {a3,b3,c3} 550(4 rows) 551 552SELECT * 553FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]); 554 a | b | c 555---+---+------------ 556 4 | e | {a4,b4,c4} 557 5 | f | {a5,b5,c5} 558 6 | g | {a6,b6,c6} 559 7 | h | {a7,b7,c7} 560(4 rows) 561 562-- this one only finds three rows left 563SELECT * 564FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]); 565 a | b | c 566----+---+--------------- 567 8 | i | {a8,b8,c8} 568 9 | j | {a9,b9,c9} 569 10 | k | {a10,b10,c10} 570(3 rows) 571 572-- fetch some data incorrectly 573SELECT * 574FROM dblink_fetch('myconn','rmt_foobar_cursor',4,false) AS t(a int, b text, c text[]); 575NOTICE: cursor "rmt_foobar_cursor" does not exist 576CONTEXT: Error occurred on dblink connection named "myconn": could not fetch from cursor. 577 a | b | c 578---+---+--- 579(0 rows) 580 581-- reset remote transaction state 582SELECT dblink_exec('myconn','ABORT'); 583 dblink_exec 584------------- 585 ROLLBACK 586(1 row) 587 588-- should generate 'cursor "rmt_foo_cursor" not found' error 589SELECT * 590FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]); 591ERROR: cursor "rmt_foo_cursor" does not exist 592CONTEXT: Error occurred on dblink connection named "myconn": could not fetch from cursor. 593-- close the named persistent connection 594SELECT dblink_disconnect('myconn'); 595 dblink_disconnect 596------------------- 597 OK 598(1 row) 599 600-- should generate "missing "=" after "myconn" in connection info string" error 601SELECT * 602FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[]) 603WHERE t.a > 7; 604ERROR: could not establish connection 605DETAIL: missing "=" after "myconn" in connection info string 606-- create a named persistent connection 607SELECT dblink_connect('myconn',connection_parameters()); 608 dblink_connect 609---------------- 610 OK 611(1 row) 612 613-- put more data into our table, using named persistent connection syntax 614-- but truncate the actual return value so we can use diff to check for success 615SELECT substr(dblink_exec('myconn','INSERT INTO foo VALUES(11,''l'',''{"a11","b11","c11"}'')'),1,6); 616 substr 617-------- 618 INSERT 619(1 row) 620 621-- let's see it 622SELECT * 623FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[]); 624 a | b | c 625----+---+--------------- 626 0 | a | {a0,b0,c0} 627 1 | b | {a1,b1,c1} 628 2 | c | {a2,b2,c2} 629 3 | d | {a3,b3,c3} 630 4 | e | {a4,b4,c4} 631 5 | f | {a5,b5,c5} 632 6 | g | {a6,b6,c6} 633 7 | h | {a7,b7,c7} 634 8 | i | {a8,b8,c8} 635 9 | j | {a9,b9,c9} 636 10 | k | {a10,b10,c10} 637 11 | l | {a11,b11,c11} 638(12 rows) 639 640-- change some data 641SELECT dblink_exec('myconn','UPDATE foo SET f3[2] = ''b99'' WHERE f1 = 11'); 642 dblink_exec 643------------- 644 UPDATE 1 645(1 row) 646 647-- let's see it 648SELECT * 649FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[]) 650WHERE a = 11; 651 a | b | c 652----+---+--------------- 653 11 | l | {a11,b99,c11} 654(1 row) 655 656-- delete some data 657SELECT dblink_exec('myconn','DELETE FROM foo WHERE f1 = 11'); 658 dblink_exec 659------------- 660 DELETE 1 661(1 row) 662 663-- let's see it 664SELECT * 665FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[]) 666WHERE a = 11; 667 a | b | c 668---+---+--- 669(0 rows) 670 671-- close the named persistent connection 672SELECT dblink_disconnect('myconn'); 673 dblink_disconnect 674------------------- 675 OK 676(1 row) 677 678-- close the named persistent connection again 679-- should get 'connection "myconn" not available' error 680SELECT dblink_disconnect('myconn'); 681ERROR: connection "myconn" not available 682-- test asynchronous queries 683SELECT dblink_connect('dtest1', connection_parameters()); 684 dblink_connect 685---------------- 686 OK 687(1 row) 688 689SELECT * from 690 dblink_send_query('dtest1', 'select * from foo where f1 < 3') as t1; 691 t1 692---- 693 1 694(1 row) 695 696SELECT dblink_connect('dtest2', connection_parameters()); 697 dblink_connect 698---------------- 699 OK 700(1 row) 701 702SELECT * from 703 dblink_send_query('dtest2', 'select * from foo where f1 > 2 and f1 < 7') as t1; 704 t1 705---- 706 1 707(1 row) 708 709SELECT dblink_connect('dtest3', connection_parameters()); 710 dblink_connect 711---------------- 712 OK 713(1 row) 714 715SELECT * from 716 dblink_send_query('dtest3', 'select * from foo where f1 > 6') as t1; 717 t1 718---- 719 1 720(1 row) 721 722CREATE TEMPORARY TABLE result AS 723(SELECT * from dblink_get_result('dtest1') as t1(f1 int, f2 text, f3 text[])) 724UNION 725(SELECT * from dblink_get_result('dtest2') as t2(f1 int, f2 text, f3 text[])) 726UNION 727(SELECT * from dblink_get_result('dtest3') as t3(f1 int, f2 text, f3 text[])) 728ORDER by f1; 729-- dblink_get_connections returns an array with elements in a machine-dependent 730-- ordering, so we must resort to unnesting and sorting for a stable result 731create function unnest(anyarray) returns setof anyelement 732language sql strict immutable as $$ 733select $1[i] from generate_series(array_lower($1,1), array_upper($1,1)) as i 734$$; 735SELECT * FROM unnest(dblink_get_connections()) ORDER BY 1; 736 unnest 737-------- 738 dtest1 739 dtest2 740 dtest3 741(3 rows) 742 743SELECT dblink_is_busy('dtest1'); 744 dblink_is_busy 745---------------- 746 0 747(1 row) 748 749SELECT dblink_disconnect('dtest1'); 750 dblink_disconnect 751------------------- 752 OK 753(1 row) 754 755SELECT dblink_disconnect('dtest2'); 756 dblink_disconnect 757------------------- 758 OK 759(1 row) 760 761SELECT dblink_disconnect('dtest3'); 762 dblink_disconnect 763------------------- 764 OK 765(1 row) 766 767SELECT * from result; 768 f1 | f2 | f3 769----+----+--------------- 770 0 | a | {a0,b0,c0} 771 1 | b | {a1,b1,c1} 772 2 | c | {a2,b2,c2} 773 3 | d | {a3,b3,c3} 774 4 | e | {a4,b4,c4} 775 5 | f | {a5,b5,c5} 776 6 | g | {a6,b6,c6} 777 7 | h | {a7,b7,c7} 778 8 | i | {a8,b8,c8} 779 9 | j | {a9,b9,c9} 780 10 | k | {a10,b10,c10} 781(11 rows) 782 783SELECT dblink_connect('dtest1', connection_parameters()); 784 dblink_connect 785---------------- 786 OK 787(1 row) 788 789SELECT * from 790 dblink_send_query('dtest1', 'select * from foo where f1 < 3') as t1; 791 t1 792---- 793 1 794(1 row) 795 796SELECT dblink_cancel_query('dtest1'); 797 dblink_cancel_query 798--------------------- 799 OK 800(1 row) 801 802SELECT dblink_error_message('dtest1'); 803 dblink_error_message 804---------------------- 805 OK 806(1 row) 807 808SELECT dblink_disconnect('dtest1'); 809 dblink_disconnect 810------------------- 811 OK 812(1 row) 813 814-- test foreign data wrapper functionality 815CREATE ROLE regress_dblink_user; 816DO $d$ 817 BEGIN 818 EXECUTE $$CREATE SERVER fdtest FOREIGN DATA WRAPPER dblink_fdw 819 OPTIONS (dbname '$$||current_database()||$$', 820 port '$$||current_setting('port')||$$' 821 )$$; 822 END; 823$d$; 824CREATE USER MAPPING FOR public SERVER fdtest 825 OPTIONS (server 'localhost'); -- fail, can't specify server here 826ERROR: invalid option "server" 827HINT: Valid options in this context are: user, password 828CREATE USER MAPPING FOR public SERVER fdtest OPTIONS (user :'USER'); 829GRANT USAGE ON FOREIGN SERVER fdtest TO regress_dblink_user; 830GRANT EXECUTE ON FUNCTION dblink_connect_u(text, text) TO regress_dblink_user; 831SET SESSION AUTHORIZATION regress_dblink_user; 832-- should fail 833SELECT dblink_connect('myconn', 'fdtest'); 834ERROR: password is required 835DETAIL: Non-superusers must provide a password in the connection string. 836-- should succeed 837SELECT dblink_connect_u('myconn', 'fdtest'); 838 dblink_connect_u 839------------------ 840 OK 841(1 row) 842 843SELECT * FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[]); 844 a | b | c 845----+---+--------------- 846 0 | a | {a0,b0,c0} 847 1 | b | {a1,b1,c1} 848 2 | c | {a2,b2,c2} 849 3 | d | {a3,b3,c3} 850 4 | e | {a4,b4,c4} 851 5 | f | {a5,b5,c5} 852 6 | g | {a6,b6,c6} 853 7 | h | {a7,b7,c7} 854 8 | i | {a8,b8,c8} 855 9 | j | {a9,b9,c9} 856 10 | k | {a10,b10,c10} 857(11 rows) 858 859\c - - 860REVOKE USAGE ON FOREIGN SERVER fdtest FROM regress_dblink_user; 861REVOKE EXECUTE ON FUNCTION dblink_connect_u(text, text) FROM regress_dblink_user; 862DROP USER regress_dblink_user; 863DROP USER MAPPING FOR public SERVER fdtest; 864DROP SERVER fdtest; 865-- test asynchronous notifications 866SELECT dblink_connect(connection_parameters()); 867 dblink_connect 868---------------- 869 OK 870(1 row) 871 872--should return listen 873SELECT dblink_exec('LISTEN regression'); 874 dblink_exec 875------------- 876 LISTEN 877(1 row) 878 879--should return listen 880SELECT dblink_exec('LISTEN foobar'); 881 dblink_exec 882------------- 883 LISTEN 884(1 row) 885 886SELECT dblink_exec('NOTIFY regression'); 887 dblink_exec 888------------- 889 NOTIFY 890(1 row) 891 892SELECT dblink_exec('NOTIFY foobar'); 893 dblink_exec 894------------- 895 NOTIFY 896(1 row) 897 898SELECT 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(); 899 notify_name | is_self_notify | extra 900-------------+----------------+------- 901 regression | t | 902 foobar | t | 903(2 rows) 904 905SELECT * from dblink_get_notify(); 906 notify_name | be_pid | extra 907-------------+--------+------- 908(0 rows) 909 910SELECT dblink_disconnect(); 911 dblink_disconnect 912------------------- 913 OK 914(1 row) 915 916-- test dropped columns in dblink_build_sql_insert, dblink_build_sql_update 917CREATE TEMP TABLE test_dropped 918( 919 col1 INT NOT NULL DEFAULT 111, 920 id SERIAL PRIMARY KEY, 921 col2 INT NOT NULL DEFAULT 112, 922 col2b INT NOT NULL DEFAULT 113 923); 924INSERT INTO test_dropped VALUES(default); 925ALTER TABLE test_dropped 926 DROP COLUMN col1, 927 DROP COLUMN col2, 928 ADD COLUMN col3 VARCHAR(10) NOT NULL DEFAULT 'foo', 929 ADD COLUMN col4 INT NOT NULL DEFAULT 42; 930SELECT dblink_build_sql_insert('test_dropped', '1', 1, 931 ARRAY['1'::TEXT], ARRAY['2'::TEXT]); 932 dblink_build_sql_insert 933--------------------------------------------------------------------------- 934 INSERT INTO test_dropped(id,col2b,col3,col4) VALUES('2','113','foo','42') 935(1 row) 936 937SELECT dblink_build_sql_update('test_dropped', '1', 1, 938 ARRAY['1'::TEXT], ARRAY['2'::TEXT]); 939 dblink_build_sql_update 940------------------------------------------------------------------------------------------- 941 UPDATE test_dropped SET id = '2', col2b = '113', col3 = 'foo', col4 = '42' WHERE id = '2' 942(1 row) 943 944SELECT dblink_build_sql_delete('test_dropped', '1', 1, 945 ARRAY['2'::TEXT]); 946 dblink_build_sql_delete 947----------------------------------------- 948 DELETE FROM test_dropped WHERE id = '2' 949(1 row) 950 951-- test local mimicry of remote GUC values that affect datatype I/O 952SET datestyle = ISO, MDY; 953SET intervalstyle = postgres; 954SET timezone = UTC; 955SELECT dblink_connect('myconn',connection_parameters()); 956 dblink_connect 957---------------- 958 OK 959(1 row) 960 961SELECT dblink_exec('myconn', 'SET datestyle = GERMAN, DMY;'); 962 dblink_exec 963------------- 964 SET 965(1 row) 966 967-- single row synchronous case 968SELECT * 969FROM dblink('myconn', 970 'SELECT * FROM (VALUES (''12.03.2013 00:00:00+00'')) t') 971 AS t(a timestamptz); 972 a 973------------------------ 974 2013-03-12 00:00:00+00 975(1 row) 976 977-- multi-row synchronous case 978SELECT * 979FROM dblink('myconn', 980 'SELECT * FROM 981 (VALUES (''12.03.2013 00:00:00+00''), 982 (''12.03.2013 00:00:00+00'')) t') 983 AS t(a timestamptz); 984 a 985------------------------ 986 2013-03-12 00:00:00+00 987 2013-03-12 00:00:00+00 988(2 rows) 989 990-- single-row asynchronous case 991SELECT * 992FROM dblink_send_query('myconn', 993 'SELECT * FROM 994 (VALUES (''12.03.2013 00:00:00+00'')) t'); 995 dblink_send_query 996------------------- 997 1 998(1 row) 999 1000CREATE TEMPORARY TABLE result AS 1001(SELECT * from dblink_get_result('myconn') as t(t timestamptz)) 1002UNION ALL 1003(SELECT * from dblink_get_result('myconn') as t(t timestamptz)); 1004SELECT * FROM result; 1005 t 1006------------------------ 1007 2013-03-12 00:00:00+00 1008(1 row) 1009 1010DROP TABLE result; 1011-- multi-row asynchronous case 1012SELECT * 1013FROM dblink_send_query('myconn', 1014 'SELECT * FROM 1015 (VALUES (''12.03.2013 00:00:00+00''), 1016 (''12.03.2013 00:00:00+00'')) t'); 1017 dblink_send_query 1018------------------- 1019 1 1020(1 row) 1021 1022CREATE TEMPORARY TABLE result AS 1023(SELECT * from dblink_get_result('myconn') as t(t timestamptz)) 1024UNION ALL 1025(SELECT * from dblink_get_result('myconn') as t(t timestamptz)) 1026UNION ALL 1027(SELECT * from dblink_get_result('myconn') as t(t timestamptz)); 1028SELECT * FROM result; 1029 t 1030------------------------ 1031 2013-03-12 00:00:00+00 1032 2013-03-12 00:00:00+00 1033(2 rows) 1034 1035DROP TABLE result; 1036-- Try an ambiguous interval 1037SELECT dblink_exec('myconn', 'SET intervalstyle = sql_standard;'); 1038 dblink_exec 1039------------- 1040 SET 1041(1 row) 1042 1043SELECT * 1044FROM dblink('myconn', 1045 'SELECT * FROM (VALUES (''-1 2:03:04'')) i') 1046 AS i(i interval); 1047 i 1048------------------- 1049 -1 days -02:03:04 1050(1 row) 1051 1052-- Try swapping to another format to ensure the GUCs are tracked 1053-- properly through a change. 1054CREATE TEMPORARY TABLE result (t timestamptz); 1055SELECT dblink_exec('myconn', 'SET datestyle = ISO, MDY;'); 1056 dblink_exec 1057------------- 1058 SET 1059(1 row) 1060 1061INSERT INTO result 1062 SELECT * 1063 FROM dblink('myconn', 1064 'SELECT * FROM (VALUES (''03.12.2013 00:00:00+00'')) t') 1065 AS t(a timestamptz); 1066SELECT dblink_exec('myconn', 'SET datestyle = GERMAN, DMY;'); 1067 dblink_exec 1068------------- 1069 SET 1070(1 row) 1071 1072INSERT INTO result 1073 SELECT * 1074 FROM dblink('myconn', 1075 'SELECT * FROM (VALUES (''12.03.2013 00:00:00+00'')) t') 1076 AS t(a timestamptz); 1077SELECT * FROM result; 1078 t 1079------------------------ 1080 2013-03-12 00:00:00+00 1081 2013-03-12 00:00:00+00 1082(2 rows) 1083 1084DROP TABLE result; 1085-- Check error throwing in dblink_fetch 1086SELECT dblink_open('myconn','error_cursor', 1087 'SELECT * FROM (VALUES (''1''), (''not an int'')) AS t(text);'); 1088 dblink_open 1089------------- 1090 OK 1091(1 row) 1092 1093SELECT * 1094FROM dblink_fetch('myconn','error_cursor', 1) AS t(i int); 1095 i 1096--- 1097 1 1098(1 row) 1099 1100SELECT * 1101FROM dblink_fetch('myconn','error_cursor', 1) AS t(i int); 1102ERROR: invalid input syntax for integer: "not an int" 1103-- Make sure that the local settings have retained their values in spite 1104-- of shenanigans on the connection. 1105SHOW datestyle; 1106 DateStyle 1107----------- 1108 ISO, MDY 1109(1 row) 1110 1111SHOW intervalstyle; 1112 IntervalStyle 1113--------------- 1114 postgres 1115(1 row) 1116 1117-- Clean up GUC-setting tests 1118SELECT dblink_disconnect('myconn'); 1119 dblink_disconnect 1120------------------- 1121 OK 1122(1 row) 1123 1124RESET datestyle; 1125RESET intervalstyle; 1126RESET timezone; 1127