-- 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

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

---+---+------------

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

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

(1 row)

-- 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

(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

(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

SET search_path= public;

CREATE OR REPLACEFUNCTIONconditional_drop()

RETURNStextAS'

' LANGUAGE'plpgsql';

SELECT conditional_drop();

CREATEDATABASEregression_slave;

\connect regression_slave

\set ECHO none

SELECT dblink_build_sql_delete('foo','1 2',2,'{"0", "a"}');

\connect regression

\set ECHO none

SET autocommit TO'on';

\idblink.sql

\set ECHO all

FROM dblink('dbname=regression_slave','SELECT * FROM foo')AS t(aint, btext, ctext[])

FROM dblink('dbname=regression','SELECT * FROM foo')AS t(aint, btext, ctext[])

WHEREt.a>7;

WHEREt.a>7;

SELECT dblink_connect('dbname=regression_slave');

SELECT dblink_connect('dbname=regression');

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);

SELECT dblink_connect('dbname=regression_slave');

SELECT dblink_connect('dbname=regression');

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);

FROM dblink('SELECT * FROM foo')AS t(aint, btext, ctext[]);

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');

FROM dblink('SELECT * FROM foo')AS t(aint, btext, ctext[])

WHERE a=11;

SELECT dblink_exec('DELETE FROM foo WHERE f1 = 11');

SELECT dblink_exec('SET autocommit TO''on'';DELETE FROM foo WHERE f1 = 11');