Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commita02780f

Browse files
committed
Add missing dblink files.
1 parent81631ac commita02780f

File tree

9 files changed

+903
-0
lines changed

9 files changed

+903
-0
lines changed
Lines changed: 202 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,202 @@
1+
\connect dblink_test_slave
2+
create table foo(f1 int, f2 text, f3 text[], primary key (f1,f2));
3+
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'foo_pkey' for table 'foo'
4+
insert into foo values(0,'a','{"a0","b0","c0"}');
5+
insert into foo values(1,'b','{"a1","b1","c1"}');
6+
insert into foo values(2,'c','{"a2","b2","c2"}');
7+
insert into foo values(3,'d','{"a3","b3","c3"}');
8+
insert into foo values(4,'e','{"a4","b4","c4"}');
9+
insert into foo values(5,'f','{"a5","b5","c5"}');
10+
insert into foo values(6,'g','{"a6","b6","c6"}');
11+
insert into foo values(7,'h','{"a7","b7","c7"}');
12+
insert into foo values(8,'i','{"a8","b8","c8"}');
13+
insert into foo values(9,'j','{"a9","b9","c9"}');
14+
\connect dblink_test_master
15+
-- regular old dblink
16+
select * from dblink('dbname=dblink_test_slave','select * from foo') as t(a int, b text, c text[]) where t.a > 7;
17+
a | b | c
18+
---+---+------------
19+
8 | i | {a8,b8,c8}
20+
9 | j | {a9,b9,c9}
21+
(2 rows)
22+
23+
-- should generate "no connection available" error
24+
select * from dblink('select * from foo') as t(a int, b text, c text[]) where t.a > 7;
25+
ERROR: dblink: no connection available
26+
-- create a persistent connection
27+
select dblink_connect('dbname=dblink_test_slave');
28+
dblink_connect
29+
----------------
30+
OK
31+
(1 row)
32+
33+
-- use the persistent connection
34+
select * from dblink('select * from foo') as t(a int, b text, c text[]) where t.a > 7;
35+
a | b | c
36+
---+---+------------
37+
8 | i | {a8,b8,c8}
38+
9 | j | {a9,b9,c9}
39+
(2 rows)
40+
41+
-- open a cursor
42+
select dblink_open('rmt_foo_cursor','select * from foo');
43+
dblink_open
44+
-------------
45+
OK
46+
(1 row)
47+
48+
-- fetch some data
49+
select * from dblink_fetch('rmt_foo_cursor',4) as t(a int, b text, c text[]);
50+
a | b | c
51+
---+---+------------
52+
0 | a | {a0,b0,c0}
53+
1 | b | {a1,b1,c1}
54+
2 | c | {a2,b2,c2}
55+
3 | d | {a3,b3,c3}
56+
(4 rows)
57+
58+
select * from dblink_fetch('rmt_foo_cursor',4) as t(a int, b text, c text[]);
59+
a | b | c
60+
---+---+------------
61+
4 | e | {a4,b4,c4}
62+
5 | f | {a5,b5,c5}
63+
6 | g | {a6,b6,c6}
64+
7 | h | {a7,b7,c7}
65+
(4 rows)
66+
67+
-- this one only finds two rows left
68+
select * from dblink_fetch('rmt_foo_cursor',4) as t(a int, b text, c text[]);
69+
a | b | c
70+
---+---+------------
71+
8 | i | {a8,b8,c8}
72+
9 | j | {a9,b9,c9}
73+
(2 rows)
74+
75+
-- close the cursor
76+
select dblink_close('rmt_foo_cursor');
77+
dblink_close
78+
--------------
79+
OK
80+
(1 row)
81+
82+
-- should generate "cursor rmt_foo_cursor does not exist" error
83+
select * from dblink_fetch('rmt_foo_cursor',4) as t(a int, b text, c text[]);
84+
ERROR: dblink_fetch: cursor rmt_foo_cursor does not exist
85+
-- close the persistent connection
86+
select dblink_disconnect();
87+
dblink_disconnect
88+
-------------------
89+
OK
90+
(1 row)
91+
92+
-- should generate "no connection available" error
93+
select * from dblink('select * from foo') as t(a int, b text, c text[]) where t.a > 7;
94+
ERROR: dblink: no connection available
95+
-- put more data into our slave table, first using arbitrary connection syntax
96+
-- but truncate the actual return value so we can use diff to check for success
97+
select substr(dblink_exec('dbname=dblink_test_slave','insert into foo values(10,''k'',''{"a10","b10","c10"}'')'),1,6);
98+
substr
99+
--------
100+
INSERT
101+
(1 row)
102+
103+
-- create a persistent connection
104+
select dblink_connect('dbname=dblink_test_slave');
105+
dblink_connect
106+
----------------
107+
OK
108+
(1 row)
109+
110+
-- put more data into our slave table, using persistent connection syntax
111+
-- but truncate the actual return value so we can use diff to check for success
112+
select substr(dblink_exec('insert into foo values(11,''l'',''{"a11","b11","c11"}'')'),1,6);
113+
substr
114+
--------
115+
INSERT
116+
(1 row)
117+
118+
-- let's see it
119+
select * from dblink('select * from foo') as t(a int, b text, c text[]);
120+
a | b | c
121+
----+---+---------------
122+
0 | a | {a0,b0,c0}
123+
1 | b | {a1,b1,c1}
124+
2 | c | {a2,b2,c2}
125+
3 | d | {a3,b3,c3}
126+
4 | e | {a4,b4,c4}
127+
5 | f | {a5,b5,c5}
128+
6 | g | {a6,b6,c6}
129+
7 | h | {a7,b7,c7}
130+
8 | i | {a8,b8,c8}
131+
9 | j | {a9,b9,c9}
132+
10 | k | {a10,b10,c10}
133+
11 | l | {a11,b11,c11}
134+
(12 rows)
135+
136+
-- change some data
137+
select dblink_exec('update foo set f3[2] = ''b99'' where f1 = 11');
138+
dblink_exec
139+
-------------
140+
UPDATE 1
141+
(1 row)
142+
143+
-- let's see it
144+
select * from dblink('select * from foo') as t(a int, b text, c text[]) where a = 11;
145+
a | b | c
146+
----+---+---------------
147+
11 | l | {a11,b99,c11}
148+
(1 row)
149+
150+
-- delete some data
151+
select dblink_exec('delete from foo where f1 = 11');
152+
dblink_exec
153+
-------------
154+
DELETE 1
155+
(1 row)
156+
157+
-- let's see it
158+
select * from dblink('select * from foo') as t(a int, b text, c text[]) where a = 11;
159+
a | b | c
160+
---+---+---
161+
(0 rows)
162+
163+
-- misc utilities
164+
\connect dblink_test_slave
165+
-- show the currently executing query
166+
select 'hello' as hello, dblink_current_query() as query;
167+
hello | query
168+
-------+-----------------------------------------------------------
169+
hello | select 'hello' as hello, dblink_current_query() as query;
170+
(1 row)
171+
172+
-- list the primary key fields
173+
select * from dblink_get_pkey('foo');
174+
position | colname
175+
----------+---------
176+
1 | f1
177+
2 | f2
178+
(2 rows)
179+
180+
-- build an insert statement based on a local tuple,
181+
-- replacing the primary key values with new ones
182+
select dblink_build_sql_insert('foo','1 2',2,'{"0", "a"}','{"99", "xyz"}');
183+
dblink_build_sql_insert
184+
-----------------------------------------------------------
185+
INSERT INTO foo(f1,f2,f3) VALUES('99','xyz','{a0,b0,c0}')
186+
(1 row)
187+
188+
-- build an update statement based on a local tuple,
189+
-- replacing the primary key values with new ones
190+
select dblink_build_sql_update('foo','1 2',2,'{"0", "a"}','{"99", "xyz"}');
191+
dblink_build_sql_update
192+
----------------------------------------------------------------------------------------
193+
UPDATE foo SET f1 = '99', f2 = 'xyz', f3 = '{a0,b0,c0}' WHERE f1 = '99' AND f2 = 'xyz'
194+
(1 row)
195+
196+
-- build a delete statement based on a local tuple,
197+
select dblink_build_sql_delete('foo','1 2',2,'{"0", "a"}');
198+
dblink_build_sql_delete
199+
---------------------------------------------
200+
DELETE FROM foo WHERE f1 = '0' AND f2 = 'a'
201+
(1 row)
202+

‎contrib/dblink/dblink.test.sql

Lines changed: 95 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,95 @@
1+
\connect dblink_test_slave
2+
3+
createtablefoo(f1int, f2text, f3text[],primary key (f1,f2));
4+
insert into foovalues(0,'a','{"a0","b0","c0"}');
5+
insert into foovalues(1,'b','{"a1","b1","c1"}');
6+
insert into foovalues(2,'c','{"a2","b2","c2"}');
7+
insert into foovalues(3,'d','{"a3","b3","c3"}');
8+
insert into foovalues(4,'e','{"a4","b4","c4"}');
9+
insert into foovalues(5,'f','{"a5","b5","c5"}');
10+
insert into foovalues(6,'g','{"a6","b6","c6"}');
11+
insert into foovalues(7,'h','{"a7","b7","c7"}');
12+
insert into foovalues(8,'i','{"a8","b8","c8"}');
13+
insert into foovalues(9,'j','{"a9","b9","c9"}');
14+
15+
\connect dblink_test_master
16+
17+
-- regular old dblink
18+
select*from dblink('dbname=dblink_test_slave','select * from foo')as t(aint, btext, ctext[])wheret.a>7;
19+
20+
-- should generate "no connection available" error
21+
select*from dblink('select * from foo')as t(aint, btext, ctext[])wheret.a>7;
22+
23+
-- create a persistent connection
24+
select dblink_connect('dbname=dblink_test_slave');
25+
26+
-- use the persistent connection
27+
select*from dblink('select * from foo')as t(aint, btext, ctext[])wheret.a>7;
28+
29+
-- open a cursor
30+
select dblink_open('rmt_foo_cursor','select * from foo');
31+
32+
-- fetch some data
33+
select*from dblink_fetch('rmt_foo_cursor',4)as t(aint, btext, ctext[]);
34+
select*from dblink_fetch('rmt_foo_cursor',4)as t(aint, btext, ctext[]);
35+
36+
-- this one only finds two rows left
37+
select*from dblink_fetch('rmt_foo_cursor',4)as t(aint, btext, ctext[]);
38+
39+
-- close the cursor
40+
select dblink_close('rmt_foo_cursor');
41+
42+
-- should generate "cursor rmt_foo_cursor does not exist" error
43+
select*from dblink_fetch('rmt_foo_cursor',4)as t(aint, btext, ctext[]);
44+
45+
-- close the persistent connection
46+
select dblink_disconnect();
47+
48+
-- should generate "no connection available" error
49+
select*from dblink('select * from foo')as t(aint, btext, ctext[])wheret.a>7;
50+
51+
-- put more data into our slave table, first using arbitrary connection syntax
52+
-- but truncate the actual return value so we can use diff to check for success
53+
select substr(dblink_exec('dbname=dblink_test_slave','insert into foo values(10,''k'',''{"a10","b10","c10"}'')'),1,6);
54+
55+
-- create a persistent connection
56+
select dblink_connect('dbname=dblink_test_slave');
57+
58+
-- put more data into our slave table, using persistent connection syntax
59+
-- but truncate the actual return value so we can use diff to check for success
60+
select substr(dblink_exec('insert into foo values(11,''l'',''{"a11","b11","c11"}'')'),1,6);
61+
62+
-- let's see it
63+
select*from dblink('select * from foo')as t(aint, btext, ctext[]);
64+
65+
-- change some data
66+
select dblink_exec('update foo set f3[2] =''b99'' where f1 = 11');
67+
68+
-- let's see it
69+
select*from dblink('select * from foo')as t(aint, btext, ctext[])where a=11;
70+
71+
-- delete some data
72+
select dblink_exec('delete from foo where f1 = 11');
73+
74+
-- let's see it
75+
select*from dblink('select * from foo')as t(aint, btext, ctext[])where a=11;
76+
77+
-- misc utilities
78+
\connect dblink_test_slave
79+
80+
-- show the currently executing query
81+
select'hello'as hello, dblink_current_query()as query;
82+
83+
-- list the primary key fields
84+
select*from dblink_get_pkey('foo');
85+
86+
-- build an insert statement based on a local tuple,
87+
-- replacing the primary key values with new ones
88+
select dblink_build_sql_insert('foo','1 2',2,'{"0", "a"}','{"99", "xyz"}');
89+
90+
-- build an update statement based on a local tuple,
91+
-- replacing the primary key values with new ones
92+
select dblink_build_sql_update('foo','1 2',2,'{"0", "a"}','{"99", "xyz"}');
93+
94+
-- build a delete statement based on a local tuple,
95+
select dblink_build_sql_delete('foo','1 2',2,'{"0", "a"}');

‎contrib/dblink/dblink_check.sh

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,15 @@
1+
#!/bin/sh
2+
3+
dropdb -U postgres dblink_test_master
4+
createdb -U postgres dblink_test_master
5+
psql -U postgres dblink_test_master<`pwd`/dblink.sql
6+
7+
dropdb -U postgres dblink_test_slave
8+
createdb -U postgres dblink_test_slave
9+
psql -U postgres dblink_test_slave<`pwd`/dblink.sql
10+
11+
psql -eaq -U postgres template1<`pwd`/dblink.test.sql> dblink.test.out2>&1
12+
diff -c ./dblink.test.expected.out`pwd`/dblink.test.out> dblink.test.diff
13+
ls -l dblink.test.diff
14+
15+

‎contrib/dblink/doc/connection

Lines changed: 53 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,53 @@
1+
==================================================================
2+
Name
3+
4+
dblink_connect -- Opens a persistent connection to a remote database
5+
6+
Synopsis
7+
8+
dblink_connect(text connstr)
9+
10+
Inputs
11+
12+
connstr
13+
14+
standard libpq format connection string,
15+
e.g. "hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres password=mypasswd"
16+
17+
Outputs
18+
19+
Returns status = "OK"
20+
21+
Example usage
22+
23+
test=# select dblink_connect('dbname=template1');
24+
dblink_connect
25+
----------------
26+
OK
27+
(1 row)
28+
29+
==================================================================
30+
Name
31+
32+
dblink_disconnect -- Closes the persistent connection to a remote database
33+
34+
Synopsis
35+
36+
dblink_disconnect()
37+
38+
Inputs
39+
40+
none
41+
42+
Outputs
43+
44+
Returns status = "OK"
45+
46+
Example usage
47+
48+
test=# select dblink_disconnect();
49+
dblink_disconnect
50+
-------------------
51+
OK
52+
(1 row)
53+

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp