diff options
| -rw-r--r-- | contrib/dblink/expected/dblink.out | 49 | ||||
| -rw-r--r-- | contrib/dblink/sql/dblink.sql | 53 |
2 files changed, 21 insertions, 81 deletions
diff --git a/contrib/dblink/expected/dblink.out b/contrib/dblink/expected/dblink.out index 1f69ab1656b..3d801ebdbbe 100644 --- a/contrib/dblink/expected/dblink.out +++ b/contrib/dblink/expected/dblink.out @@ -1,31 +1,9 @@ +-- Adjust this setting to control where the objects get created. +SET search_path = public; -- --- First, create a slave database and define the functions and test data +-- Define the functions and test data -- therein. -- --- This initial hackery is to allow successive runs without failures. --- --- Adjust this setting to control where the objects get created. -SET search_path = public; -CREATE OR REPLACE FUNCTION conditional_drop() -RETURNS text AS ' -DECLARE - dbname text; -BEGIN - SELECT INTO dbname datname FROM pg_database WHERE datname = ''regression_slave''; - IF FOUND THEN - DROP DATABASE regression_slave; - END IF; - RETURN ''OK''; -END; -' LANGUAGE 'plpgsql'; -SELECT conditional_drop(); - conditional_drop ------------------- - OK -(1 row) - -CREATE DATABASE regression_slave; -\connect regression_slave -- Turn off echoing so that expected file does not depend on -- contents of dblink.sql. \set ECHO none @@ -81,16 +59,9 @@ SELECT dblink_build_sql_delete('foo','1 2',2,'{"0", "a"}'); DELETE FROM foo WHERE f1 = '0' AND f2 = 'a' (1 row) --- --- Connect back to the regression database and define the functions. --- Turn off echoing so that expected file does not depend on --- contents of dblink.sql. --- -\connect regression -\set ECHO none -- regular old dblink SELECT * -FROM dblink('dbname=regression_slave','SELECT * FROM foo') AS t(a int, b text, c text[]) +FROM dblink('dbname=regression','SELECT * FROM foo') AS t(a int, b text, c text[]) WHERE t.a > 7; a | b | c ---+---+------------ @@ -104,7 +75,7 @@ FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[]) WHERE t.a > 7; ERROR: dblink: no connection available -- create a persistent connection -SELECT dblink_connect('dbname=regression_slave'); +SELECT dblink_connect('dbname=regression'); dblink_connect ---------------- OK @@ -182,14 +153,14 @@ WHERE t.a > 7; ERROR: dblink: no connection available -- put more data into our slave 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('dbname=regression_slave','SET autocommit TO ''on'';INSERT INTO foo VALUES (10,''k'',''{"a10","b10","c10"}'')'),1,6); +SELECT substr(dblink_exec('dbname=regression','SET autocommit TO ''on'';INSERT INTO foo VALUES(10,''k'',''{"a10","b10","c10"}'')'),1,6); substr -------- INSERT (1 row) -- create a persistent connection -SELECT dblink_connect('dbname=regression_slave'); +SELECT dblink_connect('dbname=regression'); dblink_connect ---------------- OK @@ -197,7 +168,7 @@ SELECT dblink_connect('dbname=regression_slave'); -- put more data into our slave 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); +SELECT substr(dblink_exec('SET autocommit TO ''on'';INSERT INTO foo VALUES(11,''l'',''{"a11","b11","c11"}'')'),1,6); substr -------- INSERT @@ -223,7 +194,7 @@ FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[]); (12 rows) -- change some data -SELECT dblink_exec('UPDATE foo SET f3[2] = ''b99'' WHERE f1 = 11'); +SELECT dblink_exec('SET autocommit TO ''on'';UPDATE foo SET f3[2] = ''b99'' WHERE f1 = 11'); dblink_exec ------------- UPDATE 1 @@ -239,7 +210,7 @@ WHERE a = 11; (1 row) -- delete some data -SELECT dblink_exec('DELETE FROM foo WHERE f1 = 11'); +SELECT dblink_exec('SET autocommit TO ''on'';DELETE FROM foo WHERE f1 = 11'); dblink_exec ------------- DELETE 1 diff --git a/contrib/dblink/sql/dblink.sql b/contrib/dblink/sql/dblink.sql index 3e66467ed47..542a42eda0f 100644 --- a/contrib/dblink/sql/dblink.sql +++ b/contrib/dblink/sql/dblink.sql @@ -1,30 +1,10 @@ --- --- First, create a slave database and define the functions and test data --- therein. --- --- This initial hackery is to allow successive runs without failures. --- - -- Adjust this setting to control where the objects get created. SET search_path = public; -CREATE OR REPLACE FUNCTION conditional_drop() -RETURNS text AS ' -DECLARE - dbname text; -BEGIN - SELECT INTO dbname datname FROM pg_database WHERE datname = ''regression_slave''; - IF FOUND THEN - DROP DATABASE regression_slave; - END IF; - RETURN ''OK''; -END; -' LANGUAGE 'plpgsql'; -SELECT conditional_drop(); - -CREATE DATABASE regression_slave; -\connect regression_slave - +-- +-- Define the functions and test data +-- therein. +-- -- Turn off echoing so that expected file does not depend on -- contents of dblink.sql. \set ECHO none @@ -64,20 +44,9 @@ SELECT dblink_build_sql_update('foo','1 2',2,'{"0", "a"}','{"99", "xyz"}'); -- build a delete statement based on a local tuple, SELECT dblink_build_sql_delete('foo','1 2',2,'{"0", "a"}'); --- --- Connect back to the regression database and define the functions. --- Turn off echoing so that expected file does not depend on --- contents of dblink.sql. --- -\connect regression -\set ECHO none -SET autocommit TO 'on'; -\i dblink.sql -\set ECHO all - -- regular old dblink SELECT * -FROM dblink('dbname=regression_slave','SELECT * FROM foo') AS t(a int, b text, c text[]) +FROM dblink('dbname=regression','SELECT * FROM foo') AS t(a int, b text, c text[]) WHERE t.a > 7; -- should generate "no connection available" error @@ -86,7 +55,7 @@ FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[]) WHERE t.a > 7; -- create a persistent connection -SELECT dblink_connect('dbname=regression_slave'); +SELECT dblink_connect('dbname=regression'); -- use the persistent connection SELECT * @@ -124,21 +93,21 @@ WHERE t.a > 7; -- put more data into our slave 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('dbname=regression_slave','SET autocommit TO ''on'';INSERT INTO foo VALUES(10,''k'',''{"a10","b10","c10"}'')'),1,6); +SELECT substr(dblink_exec('dbname=regression','SET autocommit TO ''on'';INSERT INTO foo VALUES(10,''k'',''{"a10","b10","c10"}'')'),1,6); -- create a persistent connection -SELECT dblink_connect('dbname=regression_slave'); +SELECT dblink_connect('dbname=regression'); -- put more data into our slave 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); +SELECT substr(dblink_exec('SET autocommit TO ''on'';INSERT INTO foo VALUES(11,''l'',''{"a11","b11","c11"}'')'),1,6); -- let's see it SELECT * FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[]); -- change some data -SELECT dblink_exec('UPDATE foo SET f3[2] = ''b99'' WHERE f1 = 11'); +SELECT dblink_exec('SET autocommit TO ''on'';UPDATE foo SET f3[2] = ''b99'' WHERE f1 = 11'); -- let's see it SELECT * @@ -146,7 +115,7 @@ FROM dblink('SELECT * FROM foo') AS t(a int, b text, c text[]) WHERE a = 11; -- delete some data -SELECT dblink_exec('DELETE FROM foo WHERE f1 = 11'); +SELECT dblink_exec('SET autocommit TO ''on'';DELETE FROM foo WHERE f1 = 11'); -- let's see it SELECT * |