diff options
Diffstat (limited to 'contrib/dblink/expected/dblink.out')
| -rw-r--r-- | contrib/dblink/expected/dblink.out | 1203 |
1 files changed, 1203 insertions, 0 deletions
diff --git a/contrib/dblink/expected/dblink.out b/contrib/dblink/expected/dblink.out new file mode 100644 index 0000000..c7bde6a --- /dev/null +++ b/contrib/dblink/expected/dblink.out @@ -0,0 +1,1203 @@ +CREATE EXTENSION dblink; +-- directory paths and dlsuffix are passed to us in environment variables +\getenv abs_srcdir PG_ABS_SRCDIR +\getenv libdir PG_LIBDIR +\getenv dlsuffix PG_DLSUFFIX +\set regresslib :libdir '/regress' :dlsuffix +-- create some functions needed for tests +CREATE FUNCTION setenv(text, text) + RETURNS void + AS :'regresslib', 'regress_setenv' + LANGUAGE C STRICT; +CREATE FUNCTION wait_pid(int) + RETURNS void + AS :'regresslib' + LANGUAGE C STRICT; +\set path :abs_srcdir '/' +\set fnbody 'SELECT setenv(''PGSERVICEFILE'', ' :'path' ' || $1)' +CREATE FUNCTION set_pgservicefile(text) RETURNS void LANGUAGE SQL + AS :'fnbody'; +-- want context for notices +\set SHOW_CONTEXT always +CREATE TABLE foo(f1 int, f2 text, f3 text[], primary key (f1,f2)); +INSERT INTO foo VALUES (0,'a','{"a0","b0","c0"}'); +INSERT INTO foo VALUES (1,'b','{"a1","b1","c1"}'); +INSERT INTO foo VALUES (2,'c','{"a2","b2","c2"}'); +INSERT INTO foo VALUES (3,'d','{"a3","b3","c3"}'); +INSERT INTO foo VALUES (4,'e','{"a4","b4","c4"}'); +INSERT INTO foo VALUES (5,'f','{"a5","b5","c5"}'); +INSERT INTO foo VALUES (6,'g','{"a6","b6","c6"}'); +INSERT INTO foo VALUES (7,'h','{"a7","b7","c7"}'); +INSERT INTO foo VALUES (8,'i','{"a8","b8","c8"}'); +INSERT INTO foo VALUES (9,'j','{"a9","b9","c9"}'); +-- misc utilities +-- list the primary key fields +SELECT * +FROM dblink_get_pkey('foo'); + position | colname +----------+--------- + 1 | f1 + 2 | f2 +(2 rows) + +-- build an insert statement based on a local tuple, +-- replacing the primary key values with new ones +SELECT dblink_build_sql_insert('foo','1 2',2,'{"0", "a"}','{"99", "xyz"}'); + dblink_build_sql_insert +----------------------------------------------------------- + INSERT INTO foo(f1,f2,f3) VALUES('99','xyz','{a0,b0,c0}') +(1 row) + +-- too many pk fields, should fail +SELECT dblink_build_sql_insert('foo','1 2 3 4',4,'{"0", "a", "{a0,b0,c0}"}','{"99", "xyz", "{za0,zb0,zc0}"}'); +ERROR: invalid attribute number 4 +-- build an update statement based on a local tuple, +-- replacing the primary key values with new ones +SELECT dblink_build_sql_update('foo','1 2',2,'{"0", "a"}','{"99", "xyz"}'); + dblink_build_sql_update +---------------------------------------------------------------------------------------- + UPDATE foo SET f1 = '99', f2 = 'xyz', f3 = '{a0,b0,c0}' WHERE f1 = '99' AND f2 = 'xyz' +(1 row) + +-- too many pk fields, should fail +SELECT dblink_build_sql_update('foo','1 2 3 4',4,'{"0", "a", "{a0,b0,c0}"}','{"99", "xyz", "{za0,zb0,zc0}"}'); +ERROR: invalid attribute number 4 +-- build a delete statement based on a local tuple, +SELECT dblink_build_sql_delete('foo','1 2',2,'{"0", "a"}'); + dblink_build_sql_delete +--------------------------------------------- + DELETE FROM foo WHERE f1 = '0' AND f2 = 'a' +(1 row) + +-- too many pk fields, should fail +SELECT dblink_build_sql_delete('foo','1 2 3 4',4,'{"0", "a", "{a0,b0,c0}"}'); +ERROR: invalid attribute number 4 +-- repeat the test for table with primary key index with included columns +CREATE TABLE foo_1(f1 int, f2 text, f3 text[], primary key (f1,f2) include (f3)); +INSERT INTO foo_1 VALUES (0,'a','{"a0","b0","c0"}'); +INSERT INTO foo_1 VALUES (1,'b','{"a1","b1","c1"}'); +INSERT INTO foo_1 VALUES (2,'c','{"a2","b2","c2"}'); +INSERT INTO foo_1 VALUES (3,'d','{"a3","b3","c3"}'); +INSERT INTO foo_1 VALUES (4,'e','{"a4","b4","c4"}'); +INSERT INTO foo_1 VALUES (5,'f','{"a5","b5","c5"}'); +INSERT INTO foo_1 VALUES (6,'g','{"a6","b6","c6"}'); +INSERT INTO foo_1 VALUES (7,'h','{"a7","b7","c7"}'); +INSERT INTO foo_1 VALUES (8,'i','{"a8","b8","c8"}'); +INSERT INTO foo_1 VALUES (9,'j','{"a9","b9","c9"}'); +-- misc utilities +-- list the primary key fields +SELECT * +FROM dblink_get_pkey('foo_1'); + position | colname +----------+--------- + 1 | f1 + 2 | f2 +(2 rows) + +-- build an insert statement based on a local tuple, +-- replacing the primary key values with new ones +SELECT dblink_build_sql_insert('foo_1','1 2',2,'{"0", "a"}','{"99", "xyz"}'); + dblink_build_sql_insert +------------------------------------------------------------- + INSERT INTO foo_1(f1,f2,f3) VALUES('99','xyz','{a0,b0,c0}') +(1 row) + +-- too many pk fields, should fail +SELECT dblink_build_sql_insert('foo_1','1 2 3 4',4,'{"0", "a", "{a0,b0,c0}"}','{"99", "xyz", "{za0,zb0,zc0}"}'); +ERROR: invalid attribute number 4 +-- build an update statement based on a local tuple, +-- replacing the primary key values with new ones +SELECT dblink_build_sql_update('foo_1','1 2',2,'{"0", "a"}','{"99", "xyz"}'); + dblink_build_sql_update +------------------------------------------------------------------------------------------ + UPDATE foo_1 SET f1 = '99', f2 = 'xyz', f3 = '{a0,b0,c0}' WHERE f1 = '99' AND f2 = 'xyz' +(1 row) + +-- too many pk fields, should fail +SELECT dblink_build_sql_update('foo_1','1 2 3 4',4,'{"0", "a", "{a0,b0,c0}"}','{"99", "xyz", "{za0,zb0,zc0}"}'); +ERROR: invalid attribute number 4 +-- build a delete statement based on a local tuple, +SELECT dblink_build_sql_delete('foo_1','1 2',2,'{"0", "a"}'); + dblink_build_sql_delete +----------------------------------------------- + DELETE FROM foo_1 WHERE f1 = '0' AND f2 = 'a' +(1 row) + +-- too many pk fields, should fail +SELECT dblink_build_sql_delete('foo_1','1 2 3 4',4,'{"0", "a", "{a0,b0,c0}"}'); +ERROR: invalid attribute number 4 +DROP TABLE foo_1; +-- retest using a quoted and schema qualified table +CREATE SCHEMA "MySchema"; +CREATE TABLE "MySchema"."Foo"(f1 int, f2 text, f3 text[], primary key (f1,f2)); +INSERT INTO "MySchema"."Foo" VALUES (0,'a','{"a0","b0","c0"}'); +-- list the primary key fields +SELECT * +FROM dblink_get_pkey('"MySchema"."Foo"'); + position | colname +----------+--------- + 1 | f1 + 2 | f2 +(2 rows) + +-- build an insert statement based on a local tuple, +-- replacing the primary key values with new ones +SELECT dblink_build_sql_insert('"MySchema"."Foo"','1 2',2,'{"0", "a"}','{"99", "xyz"}'); + dblink_build_sql_insert +------------------------------------------------------------------------ + INSERT INTO "MySchema"."Foo"(f1,f2,f3) VALUES('99','xyz','{a0,b0,c0}') +(1 row) + +-- build an update statement based on a local tuple, +-- replacing the primary key values with new ones +SELECT dblink_build_sql_update('"MySchema"."Foo"','1 2',2,'{"0", "a"}','{"99", "xyz"}'); + dblink_build_sql_update +----------------------------------------------------------------------------------------------------- + UPDATE "MySchema"."Foo" SET f1 = '99', f2 = 'xyz', f3 = '{a0,b0,c0}' WHERE f1 = '99' AND f2 = 'xyz' +(1 row) + +-- build a delete statement based on a local tuple, +SELECT dblink_build_sql_delete('"MySchema"."Foo"','1 2',2,'{"0", "a"}'); + dblink_build_sql_delete +---------------------------------------------------------- + DELETE FROM "MySchema"."Foo" WHERE f1 = '0' AND f2 = 'a' +(1 row) + +CREATE FUNCTION connection_parameters() RETURNS text LANGUAGE SQL AS $f$ + SELECT $$dbname='$$||current_database()||$$' port=$$||current_setting('port'); +$f$; +-- regular old dblink +SELECT * +FROM dblink(connection_parameters(),'SELECT * FROM foo') AS t(a int, b text, c text[]) +WHERE t.a > 7; + a | b | c +---+---+------------ + 8 | i | {a8,b8,c8} + 9 | j | {a9,b9,c9} +(2 rows) + +-- should generate "connection not available" error +SELECT * +FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[]) +WHERE t.a > 7; +ERROR: connection not available +-- The first-level connection's backend will crash on exit given OpenLDAP +-- [2.4.24, 2.4.31]. We won't see evidence of any crash until the victim +-- process terminates and the postmaster responds. If process termination +-- entails writing a core dump, that can take awhile. Wait for the process to +-- vanish. At that point, the postmaster has called waitpid() on the crashed +-- process, and it will accept no new connections until it has reinitialized +-- the cluster. (We can't exploit pg_stat_activity, because the crash happens +-- after the backend updates shared memory to reflect its impending exit.) +DO $pl$ +DECLARE + detail text; +BEGIN + PERFORM wait_pid(crash_pid) + FROM dblink(connection_parameters(), $$ + SELECT pg_backend_pid() FROM dblink( + 'service=test_ldap '||connection_parameters(), + -- This string concatenation is a hack to shoehorn a + -- set_pgservicefile call into the SQL statement. + 'SELECT 1' || set_pgservicefile('pg_service.conf') + ) t(c int) + $$) AS t(crash_pid int); +EXCEPTION WHEN OTHERS THEN + GET STACKED DIAGNOSTICS detail = PG_EXCEPTION_DETAIL; + -- Expected error in a non-LDAP build. + IF NOT detail LIKE 'syntax error in service file%' THEN RAISE; END IF; +END +$pl$; +-- create a persistent connection +SELECT dblink_connect(connection_parameters()); + dblink_connect +---------------- + OK +(1 row) + +-- use the persistent connection +SELECT * +FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[]) +WHERE t.a > 7; + a | b | c +---+---+------------ + 8 | i | {a8,b8,c8} + 9 | j | {a9,b9,c9} +(2 rows) + +-- open a cursor with bad SQL and fail_on_error set to false +SELECT dblink_open('rmt_foo_cursor','SELECT * FROM foobar',false); +NOTICE: relation "foobar" does not exist +CONTEXT: while opening cursor "rmt_foo_cursor" on unnamed dblink connection + dblink_open +------------- + ERROR +(1 row) + +-- reset remote transaction state +SELECT dblink_exec('ABORT'); + dblink_exec +------------- + ROLLBACK +(1 row) + +-- open a cursor +SELECT dblink_open('rmt_foo_cursor','SELECT * FROM foo'); + dblink_open +------------- + OK +(1 row) + +-- close the cursor +SELECT dblink_close('rmt_foo_cursor',false); + dblink_close +-------------- + OK +(1 row) + +-- open the cursor again +SELECT dblink_open('rmt_foo_cursor','SELECT * FROM foo'); + dblink_open +------------- + OK +(1 row) + +-- fetch some data +SELECT * +FROM dblink_fetch('rmt_foo_cursor',4) AS t(a int, b text, c text[]); + a | b | c +---+---+------------ + 0 | a | {a0,b0,c0} + 1 | b | {a1,b1,c1} + 2 | c | {a2,b2,c2} + 3 | d | {a3,b3,c3} +(4 rows) + +SELECT * +FROM dblink_fetch('rmt_foo_cursor',4) AS t(a int, b text, c text[]); + a | b | c +---+---+------------ + 4 | e | {a4,b4,c4} + 5 | f | {a5,b5,c5} + 6 | g | {a6,b6,c6} + 7 | h | {a7,b7,c7} +(4 rows) + +-- this one only finds two rows left +SELECT * +FROM dblink_fetch('rmt_foo_cursor',4) AS t(a int, b text, c text[]); + a | b | c +---+---+------------ + 8 | i | {a8,b8,c8} + 9 | j | {a9,b9,c9} +(2 rows) + +-- intentionally botch a fetch +SELECT * +FROM dblink_fetch('rmt_foobar_cursor',4,false) AS t(a int, b text, c text[]); +NOTICE: cursor "rmt_foobar_cursor" does not exist +CONTEXT: while fetching from cursor "rmt_foobar_cursor" on unnamed dblink connection + a | b | c +---+---+--- +(0 rows) + +-- reset remote transaction state +SELECT dblink_exec('ABORT'); + dblink_exec +------------- + ROLLBACK +(1 row) + +-- close the wrong cursor +SELECT dblink_close('rmt_foobar_cursor',false); +NOTICE: cursor "rmt_foobar_cursor" does not exist +CONTEXT: while closing cursor "rmt_foobar_cursor" on unnamed dblink connection + dblink_close +-------------- + ERROR +(1 row) + +-- should generate 'cursor "rmt_foo_cursor" not found' error +SELECT * +FROM dblink_fetch('rmt_foo_cursor',4) AS t(a int, b text, c text[]); +ERROR: cursor "rmt_foo_cursor" does not exist +CONTEXT: while fetching from cursor "rmt_foo_cursor" on unnamed dblink connection +-- this time, 'cursor "rmt_foo_cursor" not found' as a notice +SELECT * +FROM dblink_fetch('rmt_foo_cursor',4,false) AS t(a int, b text, c text[]); +NOTICE: cursor "rmt_foo_cursor" does not exist +CONTEXT: while fetching from cursor "rmt_foo_cursor" on unnamed dblink connection + a | b | c +---+---+--- +(0 rows) + +-- close the persistent connection +SELECT dblink_disconnect(); + dblink_disconnect +------------------- + OK +(1 row) + +-- should generate "connection not available" error +SELECT * +FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[]) +WHERE t.a > 7; +ERROR: connection not available +-- put more data into our table, first using arbitrary connection syntax +-- but truncate the actual return value so we can use diff to check for success +SELECT substr(dblink_exec(connection_parameters(),'INSERT INTO foo VALUES(10,''k'',''{"a10","b10","c10"}'')'),1,6); + substr +-------- + INSERT +(1 row) + +-- create a persistent connection +SELECT dblink_connect(connection_parameters()); + dblink_connect +---------------- + OK +(1 row) + +-- put more data into our table, using persistent connection syntax +-- but truncate the actual return value so we can use diff to check for success +SELECT substr(dblink_exec('INSERT INTO foo VALUES(11,''l'',''{"a11","b11","c11"}'')'),1,6); + substr +-------- + INSERT +(1 row) + +-- let's see it +SELECT * +FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[]); + a | b | c +----+---+--------------- + 0 | a | {a0,b0,c0} + 1 | b | {a1,b1,c1} + 2 | c | {a2,b2,c2} + 3 | d | {a3,b3,c3} + 4 | e | {a4,b4,c4} + 5 | f | {a5,b5,c5} + 6 | g | {a6,b6,c6} + 7 | h | {a7,b7,c7} + 8 | i | {a8,b8,c8} + 9 | j | {a9,b9,c9} + 10 | k | {a10,b10,c10} + 11 | l | {a11,b11,c11} +(12 rows) + +-- bad remote select +SELECT * +FROM dblink('SELECT * FROM foobar',false) AS t(a int, b text, c text[]); +NOTICE: relation "foobar" does not exist +CONTEXT: while executing query on unnamed dblink connection + a | b | c +---+---+--- +(0 rows) + +-- change some data +SELECT dblink_exec('UPDATE foo SET f3[2] = ''b99'' WHERE f1 = 11'); + dblink_exec +------------- + UPDATE 1 +(1 row) + +-- let's see it +SELECT * +FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[]) +WHERE a = 11; + a | b | c +----+---+--------------- + 11 | l | {a11,b99,c11} +(1 row) + +-- botch a change to some other data +SELECT dblink_exec('UPDATE foobar SET f3[2] = ''b99'' WHERE f1 = 11',false); +NOTICE: relation "foobar" does not exist +CONTEXT: while executing command on unnamed dblink connection + dblink_exec +------------- + ERROR +(1 row) + +-- delete some data +SELECT dblink_exec('DELETE FROM foo WHERE f1 = 11'); + dblink_exec +------------- + DELETE 1 +(1 row) + +-- let's see it +SELECT * +FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[]) +WHERE a = 11; + a | b | c +---+---+--- +(0 rows) + +-- close the persistent connection +SELECT dblink_disconnect(); + dblink_disconnect +------------------- + OK +(1 row) + +-- +-- tests for the new named persistent connection syntax +-- +-- should generate "missing "=" after "myconn" in connection info string" error +SELECT * +FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[]) +WHERE t.a > 7; +ERROR: could not establish connection +DETAIL: missing "=" after "myconn" in connection info string +-- create a named persistent connection +SELECT dblink_connect('myconn',connection_parameters()); + dblink_connect +---------------- + OK +(1 row) + +-- use the named persistent connection +SELECT * +FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[]) +WHERE t.a > 7; + a | b | c +----+---+--------------- + 8 | i | {a8,b8,c8} + 9 | j | {a9,b9,c9} + 10 | k | {a10,b10,c10} +(3 rows) + +-- use the named persistent connection, but get it wrong +SELECT * +FROM dblink('myconn','SELECT * FROM foobar',false) AS t(a int, b text, c text[]) +WHERE t.a > 7; +NOTICE: relation "foobar" does not exist +CONTEXT: while executing query on dblink connection named "myconn" + a | b | c +---+---+--- +(0 rows) + +-- create a second named persistent connection +-- should error with "duplicate connection name" +SELECT dblink_connect('myconn',connection_parameters()); +ERROR: duplicate connection name +-- create a second named persistent connection with a new name +SELECT dblink_connect('myconn2',connection_parameters()); + dblink_connect +---------------- + OK +(1 row) + +-- use the second named persistent connection +SELECT * +FROM dblink('myconn2','SELECT * FROM foo') AS t(a int, b text, c text[]) +WHERE t.a > 7; + a | b | c +----+---+--------------- + 8 | i | {a8,b8,c8} + 9 | j | {a9,b9,c9} + 10 | k | {a10,b10,c10} +(3 rows) + +-- close the second named persistent connection +SELECT dblink_disconnect('myconn2'); + dblink_disconnect +------------------- + OK +(1 row) + +-- open a cursor incorrectly +SELECT dblink_open('myconn','rmt_foo_cursor','SELECT * FROM foobar',false); +NOTICE: relation "foobar" does not exist +CONTEXT: while opening cursor "rmt_foo_cursor" on dblink connection named "myconn" + dblink_open +------------- + ERROR +(1 row) + +-- reset remote transaction state +SELECT dblink_exec('myconn','ABORT'); + dblink_exec +------------- + ROLLBACK +(1 row) + +-- test opening cursor in a transaction +SELECT dblink_exec('myconn','BEGIN'); + dblink_exec +------------- + BEGIN +(1 row) + +-- an open transaction will prevent dblink_open() from opening its own +SELECT dblink_open('myconn','rmt_foo_cursor','SELECT * FROM foo'); + dblink_open +------------- + OK +(1 row) + +-- this should not commit the transaction because the client opened it +SELECT dblink_close('myconn','rmt_foo_cursor'); + dblink_close +-------------- + OK +(1 row) + +-- this should succeed because we have an open transaction +SELECT dblink_exec('myconn','DECLARE xact_test CURSOR FOR SELECT * FROM foo'); + dblink_exec +---------------- + DECLARE CURSOR +(1 row) + +-- commit remote transaction +SELECT dblink_exec('myconn','COMMIT'); + dblink_exec +------------- + COMMIT +(1 row) + +-- test automatic transactions for multiple cursor opens +SELECT dblink_open('myconn','rmt_foo_cursor','SELECT * FROM foo'); + dblink_open +------------- + OK +(1 row) + +-- the second cursor +SELECT dblink_open('myconn','rmt_foo_cursor2','SELECT * FROM foo'); + dblink_open +------------- + OK +(1 row) + +-- this should not commit the transaction +SELECT dblink_close('myconn','rmt_foo_cursor2'); + dblink_close +-------------- + OK +(1 row) + +-- this should succeed because we have an open transaction +SELECT dblink_exec('myconn','DECLARE xact_test CURSOR FOR SELECT * FROM foo'); + dblink_exec +---------------- + DECLARE CURSOR +(1 row) + +-- this should commit the transaction +SELECT dblink_close('myconn','rmt_foo_cursor'); + dblink_close +-------------- + OK +(1 row) + +-- this should fail because there is no open transaction +SELECT dblink_exec('myconn','DECLARE xact_test CURSOR FOR SELECT * FROM foo'); +ERROR: DECLARE CURSOR can only be used in transaction blocks +CONTEXT: while executing command on dblink connection named "myconn" +-- reset remote transaction state +SELECT dblink_exec('myconn','ABORT'); + dblink_exec +------------- + ROLLBACK +(1 row) + +-- open a cursor +SELECT dblink_open('myconn','rmt_foo_cursor','SELECT * FROM foo'); + dblink_open +------------- + OK +(1 row) + +-- fetch some data +SELECT * +FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]); + a | b | c +---+---+------------ + 0 | a | {a0,b0,c0} + 1 | b | {a1,b1,c1} + 2 | c | {a2,b2,c2} + 3 | d | {a3,b3,c3} +(4 rows) + +SELECT * +FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]); + a | b | c +---+---+------------ + 4 | e | {a4,b4,c4} + 5 | f | {a5,b5,c5} + 6 | g | {a6,b6,c6} + 7 | h | {a7,b7,c7} +(4 rows) + +-- this one only finds three rows left +SELECT * +FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]); + a | b | c +----+---+--------------- + 8 | i | {a8,b8,c8} + 9 | j | {a9,b9,c9} + 10 | k | {a10,b10,c10} +(3 rows) + +-- fetch some data incorrectly +SELECT * +FROM dblink_fetch('myconn','rmt_foobar_cursor',4,false) AS t(a int, b text, c text[]); +NOTICE: cursor "rmt_foobar_cursor" does not exist +CONTEXT: while fetching from cursor "rmt_foobar_cursor" on dblink connection named "myconn" + a | b | c +---+---+--- +(0 rows) + +-- reset remote transaction state +SELECT dblink_exec('myconn','ABORT'); + dblink_exec +------------- + ROLLBACK +(1 row) + +-- should generate 'cursor "rmt_foo_cursor" not found' error +SELECT * +FROM dblink_fetch('myconn','rmt_foo_cursor',4) AS t(a int, b text, c text[]); +ERROR: cursor "rmt_foo_cursor" does not exist +CONTEXT: while fetching from cursor "rmt_foo_cursor" on dblink connection named "myconn" +-- close the named persistent connection +SELECT dblink_disconnect('myconn'); + dblink_disconnect +------------------- + OK +(1 row) + +-- should generate "missing "=" after "myconn" in connection info string" error +SELECT * +FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[]) +WHERE t.a > 7; +ERROR: could not establish connection +DETAIL: missing "=" after "myconn" in connection info string +-- create a named persistent connection +SELECT dblink_connect('myconn',connection_parameters()); + dblink_connect +---------------- + OK +(1 row) + +-- put more data into our table, using named persistent connection syntax +-- but truncate the actual return value so we can use diff to check for success +SELECT substr(dblink_exec('myconn','INSERT INTO foo VALUES(11,''l'',''{"a11","b11","c11"}'')'),1,6); + substr +-------- + INSERT +(1 row) + +-- let's see it +SELECT * +FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[]); + a | b | c +----+---+--------------- + 0 | a | {a0,b0,c0} + 1 | b | {a1,b1,c1} + 2 | c | {a2,b2,c2} + 3 | d | {a3,b3,c3} + 4 | e | {a4,b4,c4} + 5 | f | {a5,b5,c5} + 6 | g | {a6,b6,c6} + 7 | h | {a7,b7,c7} + 8 | i | {a8,b8,c8} + 9 | j | {a9,b9,c9} + 10 | k | {a10,b10,c10} + 11 | l | {a11,b11,c11} +(12 rows) + +-- change some data +SELECT dblink_exec('myconn','UPDATE foo SET f3[2] = ''b99'' WHERE f1 = 11'); + dblink_exec +------------- + UPDATE 1 +(1 row) + +-- let's see it +SELECT * +FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[]) +WHERE a = 11; + a | b | c +----+---+--------------- + 11 | l | {a11,b99,c11} +(1 row) + +-- delete some data +SELECT dblink_exec('myconn','DELETE FROM foo WHERE f1 = 11'); + dblink_exec +------------- + DELETE 1 +(1 row) + +-- let's see it +SELECT * +FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[]) +WHERE a = 11; + a | b | c +---+---+--- +(0 rows) + +-- close the named persistent connection +SELECT dblink_disconnect('myconn'); + dblink_disconnect +------------------- + OK +(1 row) + +-- close the named persistent connection again +-- should get 'connection "myconn" not available' error +SELECT dblink_disconnect('myconn'); +ERROR: connection "myconn" not available +-- test asynchronous queries +SELECT dblink_connect('dtest1', connection_parameters()); + dblink_connect +---------------- + OK +(1 row) + +SELECT * from + dblink_send_query('dtest1', 'select * from foo where f1 < 3') as t1; + t1 +---- + 1 +(1 row) + +SELECT dblink_connect('dtest2', connection_parameters()); + dblink_connect +---------------- + OK +(1 row) + +SELECT * from + dblink_send_query('dtest2', 'select * from foo where f1 > 2 and f1 < 7') as t1; + t1 +---- + 1 +(1 row) + +SELECT dblink_connect('dtest3', connection_parameters()); + dblink_connect +---------------- + OK +(1 row) + +SELECT * from + dblink_send_query('dtest3', 'select * from foo where f1 > 6') as t1; + t1 +---- + 1 +(1 row) + +CREATE TEMPORARY TABLE result AS +(SELECT * from dblink_get_result('dtest1') as t1(f1 int, f2 text, f3 text[])) +UNION +(SELECT * from dblink_get_result('dtest2') as t2(f1 int, f2 text, f3 text[])) +UNION +(SELECT * from dblink_get_result('dtest3') as t3(f1 int, f2 text, f3 text[])) +ORDER by f1; +-- dblink_get_connections returns an array with elements in a machine-dependent +-- ordering, so we must resort to unnesting and sorting for a stable result +create function unnest(anyarray) returns setof anyelement +language sql strict immutable as $$ +select $1[i] from generate_series(array_lower($1,1), array_upper($1,1)) as i +$$; +SELECT * FROM unnest(dblink_get_connections()) ORDER BY 1; + unnest +-------- + dtest1 + dtest2 + dtest3 +(3 rows) + +SELECT dblink_is_busy('dtest1'); + dblink_is_busy +---------------- + 0 +(1 row) + +SELECT dblink_disconnect('dtest1'); + dblink_disconnect +------------------- + OK +(1 row) + +SELECT dblink_disconnect('dtest2'); + dblink_disconnect +------------------- + OK +(1 row) + +SELECT dblink_disconnect('dtest3'); + dblink_disconnect +------------------- + OK +(1 row) + +SELECT * from result; + f1 | f2 | f3 +----+----+--------------- + 0 | a | {a0,b0,c0} + 1 | b | {a1,b1,c1} + 2 | c | {a2,b2,c2} + 3 | d | {a3,b3,c3} + 4 | e | {a4,b4,c4} + 5 | f | {a5,b5,c5} + 6 | g | {a6,b6,c6} + 7 | h | {a7,b7,c7} + 8 | i | {a8,b8,c8} + 9 | j | {a9,b9,c9} + 10 | k | {a10,b10,c10} +(11 rows) + +SELECT dblink_connect('dtest1', connection_parameters()); + dblink_connect +---------------- + OK +(1 row) + +SELECT * from + dblink_send_query('dtest1', 'select * from foo where f1 < 3') as t1; + t1 +---- + 1 +(1 row) + +SELECT dblink_cancel_query('dtest1'); + dblink_cancel_query +--------------------- + OK +(1 row) + +SELECT dblink_error_message('dtest1'); + dblink_error_message +---------------------- + OK +(1 row) + +SELECT dblink_disconnect('dtest1'); + dblink_disconnect +------------------- + OK +(1 row) + +-- test foreign data wrapper functionality +CREATE ROLE regress_dblink_user; +DO $d$ + BEGIN + EXECUTE $$CREATE SERVER fdtest FOREIGN DATA WRAPPER dblink_fdw + OPTIONS (dbname '$$||current_database()||$$', + port '$$||current_setting('port')||$$' + )$$; + END; +$d$; +CREATE USER MAPPING FOR public SERVER fdtest + OPTIONS (server 'localhost'); -- fail, can't specify server here +ERROR: invalid option "server" +HINT: Valid options in this context are: user, password, sslpassword +CREATE USER MAPPING FOR public SERVER fdtest OPTIONS (user :'USER'); +GRANT USAGE ON FOREIGN SERVER fdtest TO regress_dblink_user; +GRANT EXECUTE ON FUNCTION dblink_connect_u(text, text) TO regress_dblink_user; +SET SESSION AUTHORIZATION regress_dblink_user; +-- should fail +SELECT dblink_connect('myconn', 'fdtest'); +ERROR: password is required +DETAIL: Non-superusers must provide a password in the connection string. +-- should succeed +SELECT dblink_connect_u('myconn', 'fdtest'); + dblink_connect_u +------------------ + OK +(1 row) + +SELECT * FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[]); + a | b | c +----+---+--------------- + 0 | a | {a0,b0,c0} + 1 | b | {a1,b1,c1} + 2 | c | {a2,b2,c2} + 3 | d | {a3,b3,c3} + 4 | e | {a4,b4,c4} + 5 | f | {a5,b5,c5} + 6 | g | {a6,b6,c6} + 7 | h | {a7,b7,c7} + 8 | i | {a8,b8,c8} + 9 | j | {a9,b9,c9} + 10 | k | {a10,b10,c10} +(11 rows) + +\c - - +REVOKE USAGE ON FOREIGN SERVER fdtest FROM regress_dblink_user; +REVOKE EXECUTE ON FUNCTION dblink_connect_u(text, text) FROM regress_dblink_user; +DROP USER regress_dblink_user; +DROP USER MAPPING FOR public SERVER fdtest; +DROP SERVER fdtest; +-- should fail +ALTER FOREIGN DATA WRAPPER dblink_fdw OPTIONS (nonexistent 'fdw'); +ERROR: invalid option "nonexistent" +HINT: There are no valid options in this context. +-- test asynchronous notifications +SELECT dblink_connect(connection_parameters()); + dblink_connect +---------------- + OK +(1 row) + +--should return listen +SELECT dblink_exec('LISTEN regression'); + dblink_exec +------------- + LISTEN +(1 row) + +--should return listen +SELECT dblink_exec('LISTEN foobar'); + dblink_exec +------------- + LISTEN +(1 row) + +SELECT dblink_exec('NOTIFY regression'); + dblink_exec +------------- + NOTIFY +(1 row) + +SELECT dblink_exec('NOTIFY foobar'); + dblink_exec +------------- + NOTIFY +(1 row) + +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(); + notify_name | is_self_notify | extra +-------------+----------------+------- + regression | t | + foobar | t | +(2 rows) + +SELECT * from dblink_get_notify(); + notify_name | be_pid | extra +-------------+--------+------- +(0 rows) + +SELECT dblink_disconnect(); + dblink_disconnect +------------------- + OK +(1 row) + +-- test dropped columns in dblink_build_sql_insert, dblink_build_sql_update +CREATE TEMP TABLE test_dropped +( + col1 INT NOT NULL DEFAULT 111, + id SERIAL PRIMARY KEY, + col2 INT NOT NULL DEFAULT 112, + col2b INT NOT NULL DEFAULT 113 +); +INSERT INTO test_dropped VALUES(default); +ALTER TABLE test_dropped + DROP COLUMN col1, + DROP COLUMN col2, + ADD COLUMN col3 VARCHAR(10) NOT NULL DEFAULT 'foo', + ADD COLUMN col4 INT NOT NULL DEFAULT 42; +SELECT dblink_build_sql_insert('test_dropped', '1', 1, + ARRAY['1'::TEXT], ARRAY['2'::TEXT]); + dblink_build_sql_insert +--------------------------------------------------------------------------- + INSERT INTO test_dropped(id,col2b,col3,col4) VALUES('2','113','foo','42') +(1 row) + +SELECT dblink_build_sql_update('test_dropped', '1', 1, + ARRAY['1'::TEXT], ARRAY['2'::TEXT]); + dblink_build_sql_update +------------------------------------------------------------------------------------------- + UPDATE test_dropped SET id = '2', col2b = '113', col3 = 'foo', col4 = '42' WHERE id = '2' +(1 row) + +SELECT dblink_build_sql_delete('test_dropped', '1', 1, + ARRAY['2'::TEXT]); + dblink_build_sql_delete +----------------------------------------- + DELETE FROM test_dropped WHERE id = '2' +(1 row) + +-- test local mimicry of remote GUC values that affect datatype I/O +SET datestyle = ISO, MDY; +SET intervalstyle = postgres; +SET timezone = UTC; +SELECT dblink_connect('myconn',connection_parameters()); + dblink_connect +---------------- + OK +(1 row) + +SELECT dblink_exec('myconn', 'SET datestyle = GERMAN, DMY;'); + dblink_exec +------------- + SET +(1 row) + +-- single row synchronous case +SELECT * +FROM dblink('myconn', + 'SELECT * FROM (VALUES (''12.03.2013 00:00:00+00'')) t') + AS t(a timestamptz); + a +------------------------ + 2013-03-12 00:00:00+00 +(1 row) + +-- multi-row synchronous case +SELECT * +FROM dblink('myconn', + 'SELECT * FROM + (VALUES (''12.03.2013 00:00:00+00''), + (''12.03.2013 00:00:00+00'')) t') + AS t(a timestamptz); + a +------------------------ + 2013-03-12 00:00:00+00 + 2013-03-12 00:00:00+00 +(2 rows) + +-- single-row asynchronous case +SELECT * +FROM dblink_send_query('myconn', + 'SELECT * FROM + (VALUES (''12.03.2013 00:00:00+00'')) t'); + dblink_send_query +------------------- + 1 +(1 row) + +CREATE TEMPORARY TABLE result AS +(SELECT * from dblink_get_result('myconn') as t(t timestamptz)) +UNION ALL +(SELECT * from dblink_get_result('myconn') as t(t timestamptz)); +SELECT * FROM result; + t +------------------------ + 2013-03-12 00:00:00+00 +(1 row) + +DROP TABLE result; +-- multi-row asynchronous case +SELECT * +FROM dblink_send_query('myconn', + 'SELECT * FROM + (VALUES (''12.03.2013 00:00:00+00''), + (''12.03.2013 00:00:00+00'')) t'); + dblink_send_query +------------------- + 1 +(1 row) + +CREATE TEMPORARY TABLE result AS +(SELECT * from dblink_get_result('myconn') as t(t timestamptz)) +UNION ALL +(SELECT * from dblink_get_result('myconn') as t(t timestamptz)) +UNION ALL +(SELECT * from dblink_get_result('myconn') as t(t timestamptz)); +SELECT * FROM result; + t +------------------------ + 2013-03-12 00:00:00+00 + 2013-03-12 00:00:00+00 +(2 rows) + +DROP TABLE result; +-- Try an ambiguous interval +SELECT dblink_exec('myconn', 'SET intervalstyle = sql_standard;'); + dblink_exec +------------- + SET +(1 row) + +SELECT * +FROM dblink('myconn', + 'SELECT * FROM (VALUES (''-1 2:03:04'')) i') + AS i(i interval); + i +------------------- + -1 days -02:03:04 +(1 row) + +-- Try swapping to another format to ensure the GUCs are tracked +-- properly through a change. +CREATE TEMPORARY TABLE result (t timestamptz); +SELECT dblink_exec('myconn', 'SET datestyle = ISO, MDY;'); + dblink_exec +------------- + SET +(1 row) + +INSERT INTO result + SELECT * + FROM dblink('myconn', + 'SELECT * FROM (VALUES (''03.12.2013 00:00:00+00'')) t') + AS t(a timestamptz); +SELECT dblink_exec('myconn', 'SET datestyle = GERMAN, DMY;'); + dblink_exec +------------- + SET +(1 row) + +INSERT INTO result + SELECT * + FROM dblink('myconn', + 'SELECT * FROM (VALUES (''12.03.2013 00:00:00+00'')) t') + AS t(a timestamptz); +SELECT * FROM result; + t +------------------------ + 2013-03-12 00:00:00+00 + 2013-03-12 00:00:00+00 +(2 rows) + +DROP TABLE result; +-- Check error throwing in dblink_fetch +SELECT dblink_open('myconn','error_cursor', + 'SELECT * FROM (VALUES (''1''), (''not an int'')) AS t(text);'); + dblink_open +------------- + OK +(1 row) + +SELECT * +FROM dblink_fetch('myconn','error_cursor', 1) AS t(i int); + i +--- + 1 +(1 row) + +SELECT * +FROM dblink_fetch('myconn','error_cursor', 1) AS t(i int); +ERROR: invalid input syntax for type integer: "not an int" +-- Make sure that the local settings have retained their values in spite +-- of shenanigans on the connection. +SHOW datestyle; + DateStyle +----------- + ISO, MDY +(1 row) + +SHOW intervalstyle; + IntervalStyle +--------------- + postgres +(1 row) + +-- Clean up GUC-setting tests +SELECT dblink_disconnect('myconn'); + dblink_disconnect +------------------- + OK +(1 row) + +RESET datestyle; +RESET intervalstyle; +RESET timezone; |