1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
|
==================================================================
Name
dblink_open -- Opens a cursor on a remote database
Synopsis
dblink_open(text cursorname, text sql [, bool fail_on_error])
dblink_open(text connname, text cursorname, text sql [, bool fail_on_error])
Inputs
connname
if three arguments are present, the first is taken as the specific
connection name to use; otherwise the unnamed connection is assumed
cursorname
a reference name for the cursor
sql
sql statement that you wish to execute on the remote host
e.g. "select * from pg_class"
fail_on_error
If true (default when not present) then an ERROR thrown on the remote side
of the connection causes an ERROR to also be thrown locally. If false, the
remote ERROR is locally treated as a NOTICE, and the return value is set
to 'ERROR'.
Outputs
Returns status = "OK"
Note
1) dblink_connect(text connstr) must be executed first
2) dblink_open starts an explicit transaction. If, after using dblink_open,
you use dblink_exec to change data, and then an error occurs or you use
dblink_disconnect without a dblink_close first, your change *will* be
lost. Also, using dblink_close explicitly ends the transaction and thus
effectively closes *all* open cursors.
Example usage
test=# select dblink_connect('dbname=postgres');
dblink_connect
----------------
OK
(1 row)
test=# select dblink_open('foo','select proname, prosrc from pg_proc');
dblink_open
-------------
OK
(1 row)
==================================================================
Name
dblink_fetch -- Returns a set from an open cursor on a remote database
Synopsis
dblink_fetch(text cursorname, int32 howmany [, bool fail_on_error])
dblink_fetch(text connname, text cursorname, int32 howmany [, bool fail_on_error])
Inputs
connname
if three arguments are present, the first is taken as the specific
connection name to use; otherwise the unnamed connection is assumed
cursorname
The reference name for the cursor
howmany
Maximum number of rows to retrieve. The next howmany rows are fetched,
starting at the current cursor position, moving forward. Once the cursor
has positioned to the end, no more rows are produced.
fail_on_error
If true (default when not present) then an ERROR thrown on the remote side
of the connection causes an ERROR to also be thrown locally. If false, the
remote ERROR is locally treated as a NOTICE, and no rows are returned.
Outputs
Returns setof record
Example usage
test=# select dblink_connect('dbname=postgres');
dblink_connect
----------------
OK
(1 row)
test=# select dblink_open('foo','select proname, prosrc from pg_proc where proname like ''bytea%''');
dblink_open
-------------
OK
(1 row)
test=# select * from dblink_fetch('foo',5) as (funcname name, source text);
funcname | source
----------+----------
byteacat | byteacat
byteacmp | byteacmp
byteaeq | byteaeq
byteage | byteage
byteagt | byteagt
(5 rows)
test=# select * from dblink_fetch('foo',5) as (funcname name, source text);
funcname | source
-----------+-----------
byteain | byteain
byteale | byteale
bytealike | bytealike
bytealt | bytealt
byteane | byteane
(5 rows)
test=# select * from dblink_fetch('foo',5) as (funcname name, source text);
funcname | source
------------+------------
byteanlike | byteanlike
byteaout | byteaout
(2 rows)
test=# select * from dblink_fetch('foo',5) as (funcname name, source text);
funcname | source
----------+--------
(0 rows)
==================================================================
Name
dblink_close -- Closes a cursor on a remote database
Synopsis
dblink_close(text cursorname [, bool fail_on_error])
dblink_close(text connname, text cursorname [, bool fail_on_error])
Inputs
connname
if two arguments are present, the first is taken as the specific
connection name to use; otherwise the unnamed connection is assumed
cursorname
a reference name for the cursor
fail_on_error
If true (default when not present) then an ERROR thrown on the remote side
of the connection causes an ERROR to also be thrown locally. If false, the
remote ERROR is locally treated as a NOTICE, and the return value is set
to 'ERROR'.
Outputs
Returns status = "OK"
Note
dblink_connect(text connstr) or dblink_connect(text connname, text connstr)
must be executed first.
Example usage
test=# select dblink_connect('dbname=postgres');
dblink_connect
----------------
OK
(1 row)
test=# select dblink_open('foo','select proname, prosrc from pg_proc');
dblink_open
-------------
OK
(1 row)
test=# select dblink_close('foo');
dblink_close
--------------
OK
(1 row)
select dblink_connect('myconn','dbname=regression');
dblink_connect
----------------
OK
(1 row)
select dblink_open('myconn','foo','select proname, prosrc from pg_proc');
dblink_open
-------------
OK
(1 row)
select dblink_close('myconn','foo');
dblink_close
--------------
OK
(1 row)
|
