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
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
|
$PostgreSQL: pgsql/contrib/dblink/doc/misc,v 1.5 2007/11/07 12:24:23 petere Exp $
==================================================================
Name
dblink_current_query -- returns the current query string
Synopsis
dblink_current_query () RETURNS text
Inputs
None
Outputs
Returns text -- a copy of the currently executing query
Example usage
test=# select dblink_current_query() from (select dblink('dbname=postgres','select oid, proname from pg_proc where proname = ''byteacat''') as f1) as t1;
dblink_current_query
-----------------------------------------------------------------------------------------------------------------------------------------------------
select dblink_current_query() from (select dblink('dbname=postgres','select oid, proname from pg_proc where proname = ''byteacat''') as f1) as t1;
(1 row)
==================================================================
Name
dblink_get_pkey -- returns the position and field names of a relation's
primary key fields
Synopsis
dblink_get_pkey(text relname) RETURNS setof dblink_pkey_results
Inputs
relname
any relation name;
e.g. 'foobar'
Outputs
Returns setof dblink_pkey_results -- one row for each primary key field,
in order of position in the key. dblink_pkey_results is defined as follows:
CREATE TYPE dblink_pkey_results AS (position int4, colname text);
Example usage
test=# select * from dblink_get_pkey('foobar');
position | colname
----------+---------
1 | f1
2 | f2
3 | f3
4 | f4
5 | f5
==================================================================
Name
dblink_build_sql_insert -- builds an insert statement using a local
tuple, replacing the selection key field
values with alternative supplied values
dblink_build_sql_delete -- builds a delete statement using supplied
values for selection key field values
dblink_build_sql_update -- builds an update statement using a local
tuple, replacing the selection key field
values with alternative supplied values
Synopsis
dblink_build_sql_insert(text relname
,int2vector primary_key_attnums
,int2 num_primary_key_atts
,_text src_pk_att_vals_array
,_text tgt_pk_att_vals_array) RETURNS text
dblink_build_sql_delete(text relname
,int2vector primary_key_attnums
,int2 num_primary_key_atts
,_text tgt_pk_att_vals_array) RETURNS text
dblink_build_sql_update(text relname
,int2vector primary_key_attnums
,int2 num_primary_key_atts
,_text src_pk_att_vals_array
,_text tgt_pk_att_vals_array) RETURNS text
Inputs
relname
any relation name;
e.g. 'foobar'
primary_key_attnums
vector of primary key attnums (1 based, see pg_index.indkey);
e.g. '1 2'
num_primary_key_atts
number of primary key attnums in the vector; e.g. 2
src_pk_att_vals_array
array of primary key values, used to look up the local matching
tuple, the values of which are then used to construct the SQL
statement
tgt_pk_att_vals_array
array of primary key values, used to replace the local tuple
values in the SQL statement
Outputs
Returns text -- requested SQL statement
Example usage
test=# select dblink_build_sql_insert('foo','1 2',2,'{"1", "a"}','{"1", "b''a"}');
dblink_build_sql_insert
--------------------------------------------------
INSERT INTO foo(f1,f2,f3) VALUES('1','b''a','1')
(1 row)
test=# select dblink_build_sql_delete('MyFoo','1 2',2,'{"1", "b"}');
dblink_build_sql_delete
---------------------------------------------
DELETE FROM "MyFoo" WHERE f1='1' AND f2='b'
(1 row)
test=# select dblink_build_sql_update('foo','1 2',2,'{"1", "a"}','{"1", "b"}');
dblink_build_sql_update
-------------------------------------------------------------
UPDATE foo SET f1='1',f2='b',f3='1' WHERE f1='1' AND f2='b'
(1 row)
==================================================================
Name
dblink_get_connections -- returns a text array of all active named
dblink connections
Synopsis
dblink_get_connections() RETURNS text[]
Inputs
none
Outputs
Returns text array of all active named dblink connections
Example usage
SELECT dblink_get_connections();
==================================================================
Name
dblink_is_busy -- checks to see if named connection is busy
with an async query
Synopsis
dblink_is_busy(text connname) RETURNS int
Inputs
connname
The specific connection name to use.
Outputs
Returns 1 if connection is busy, 0 if it is not busy.
If this function returns 0, it is guaranteed that dblink_get_result
will not block.
Example usage
SELECT dblink_is_busy('dtest1');
==================================================================
Name
dblink_cancel_query -- cancels any active query on the named connection
Synopsis
dblink_cancel_query(text connname) RETURNS text
Inputs
connname
The specific connection name to use.
Outputs
Returns "OK" on success, or an error message on failure.
Example usage
SELECT dblink_cancel_query('dtest1');
==================================================================
Name
dblink_error_message -- gets last error message on the named connection
Synopsis
dblink_error_message(text connname) RETURNS text
Inputs
connname
The specific connection name to use.
Outputs
Returns last error message.
Example usage
SELECT dblink_error_message('dtest1');
|
