1
<!-- doc/src/sgml/dblink.sgml -->
6
<indexterm zone="dblink">
7
<primary>dblink</primary>
11
<filename>dblink</> is a module which supports connections to
12
other <productname>PostgreSQL</> databases from within a database
16
<refentry id="CONTRIB-DBLINK-CONNECT">
18
<refentrytitle>dblink_connect</refentrytitle>
19
<manvolnum>3</manvolnum>
23
<refname>dblink_connect</refname>
24
<refpurpose>opens a persistent connection to a remote database</refpurpose>
29
dblink_connect(text connstr) returns text
30
dblink_connect(text connname, text connstr) returns text
35
<title>Description</title>
38
<function>dblink_connect()</> establishes a connection to a remote
39
<productname>PostgreSQL</> database. The server and database to
40
be contacted are identified through a standard <application>libpq</>
41
connection string. Optionally, a name can be assigned to the
42
connection. Multiple named connections can be open at once, but
43
only one unnamed connection is permitted at a time. The connection
44
will persist until closed or until the database session is ended.
48
The connection string may also be the name of an existing foreign
49
server. It is recommended to use
50
the <function>postgresql_fdw_validator</function> when defining
51
the corresponding foreign-data wrapper. See the example below, as
52
well as the following:
53
<simplelist type="inline">
54
<member><xref linkend="sql-createforeigndatawrapper"></member>
55
<member><xref linkend="sql-createserver"></member>
56
<member><xref linkend="sql-createusermapping"></member>
63
<title>Arguments</title>
67
<term><parameter>conname</parameter></term>
70
The name to use for this connection; if omitted, an unnamed
71
connection is opened, replacing any existing unnamed connection.
77
<term><parameter>connstr</parameter></term>
80
<application>libpq</>-style connection info string, for example
81
<literal>hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres
83
For details see <function>PQconnectdb</> in
84
<xref linkend="libpq-connect">.
92
<title>Return Value</title>
95
Returns status, which is always <literal>OK</> (since any error
96
causes the function to throw an error instead of returning).
104
Only superusers may use <function>dblink_connect</> to create
105
non-password-authenticated connections. If non-superusers need this
106
capability, use <function>dblink_connect_u</> instead.
110
It is unwise to choose connection names that contain equal signs,
111
as this opens a risk of confusion with connection info strings
112
in other <filename>dblink</> functions.
117
<title>Example</title>
120
SELECT dblink_connect('dbname=postgres');
126
SELECT dblink_connect('myconn', 'dbname=postgres');
132
-- FOREIGN DATA WRAPPER functionality
133
-- Note: local connection must require password authentication for this to work properly
134
-- Otherwise, you will receive the following error from dblink_connect():
135
-- ----------------------------------------------------------------------
136
-- ERROR: password is required
137
-- DETAIL: Non-superuser cannot connect if the server does not request a password.
138
-- HINT: Target server's authentication method must be changed.
139
CREATE USER dblink_regression_test WITH PASSWORD 'secret';
140
CREATE FOREIGN DATA WRAPPER postgresql VALIDATOR postgresql_fdw_validator;
141
CREATE SERVER fdtest FOREIGN DATA WRAPPER postgresql OPTIONS (hostaddr '127.0.0.1', dbname 'contrib_regression');
143
CREATE USER MAPPING FOR dblink_regression_test SERVER fdtest OPTIONS (user 'dblink_regression_test', password 'secret');
144
GRANT USAGE ON FOREIGN SERVER fdtest TO dblink_regression_test;
145
GRANT SELECT ON TABLE foo TO dblink_regression_test;
147
\set ORIGINAL_USER :USER
148
\c - dblink_regression_test
149
SELECT dblink_connect('myconn', 'fdtest');
155
SELECT * FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[]);
157
----+---+---------------
168
10 | k | {a10,b10,c10}
172
REVOKE USAGE ON FOREIGN SERVER fdtest FROM dblink_regression_test;
173
REVOKE SELECT ON TABLE foo FROM dblink_regression_test;
174
DROP USER MAPPING FOR dblink_regression_test SERVER fdtest;
175
DROP USER dblink_regression_test;
177
DROP FOREIGN DATA WRAPPER postgresql;
182
<refentry id="CONTRIB-DBLINK-CONNECT-U">
184
<refentrytitle>dblink_connect_u</refentrytitle>
185
<manvolnum>3</manvolnum>
189
<refname>dblink_connect_u</refname>
190
<refpurpose>opens a persistent connection to a remote database, insecurely</refpurpose>
195
dblink_connect_u(text connstr) returns text
196
dblink_connect_u(text connname, text connstr) returns text
201
<title>Description</title>
204
<function>dblink_connect_u()</> is identical to
205
<function>dblink_connect()</>, except that it will allow non-superusers
206
to connect using any authentication method.
210
If the remote server selects an authentication method that does not
211
involve a password, then impersonation and subsequent escalation of
212
privileges can occur, because the session will appear to have
213
originated from the user as which the local <productname>PostgreSQL</>
214
server runs. Also, even if the remote server does demand a password,
215
it is possible for the password to be supplied from the server
216
environment, such as a <filename>~/.pgpass</> file belonging to the
217
server's user. This opens not only a risk of impersonation, but the
218
possibility of exposing a password to an untrustworthy remote server.
219
Therefore, <function>dblink_connect_u()</> is initially
220
installed with all privileges revoked from <literal>PUBLIC</>,
221
making it un-callable except by superusers. In some situations
222
it may be appropriate to grant <literal>EXECUTE</> permission for
223
<function>dblink_connect_u()</> to specific users who are considered
224
trustworthy, but this should be done with care. It is also recommended
225
that any <filename>~/.pgpass</> file belonging to the server's user
226
<emphasis>not</> contain any records specifying a wildcard host name.
230
For further details see <function>dblink_connect()</>.
235
<refentry id="CONTRIB-DBLINK-DISCONNECT">
237
<refentrytitle>dblink_disconnect</refentrytitle>
238
<manvolnum>3</manvolnum>
242
<refname>dblink_disconnect</refname>
243
<refpurpose>closes a persistent connection to a remote database</refpurpose>
248
dblink_disconnect() returns text
249
dblink_disconnect(text connname) returns text
254
<title>Description</title>
257
<function>dblink_disconnect()</> closes a connection previously opened
258
by <function>dblink_connect()</>. The form with no arguments closes
259
an unnamed connection.
264
<title>Arguments</title>
268
<term><parameter>conname</parameter></term>
271
The name of a named connection to be closed.
279
<title>Return Value</title>
282
Returns status, which is always <literal>OK</> (since any error
283
causes the function to throw an error instead of returning).
288
<title>Example</title>
291
SELECT dblink_disconnect();
297
SELECT dblink_disconnect('myconn');
306
<refentry id="CONTRIB-DBLINK-FUNCTION">
308
<refentrytitle>dblink</refentrytitle>
309
<manvolnum>3</manvolnum>
313
<refname>dblink</refname>
314
<refpurpose>executes a query in a remote database</refpurpose>
319
dblink(text connname, text sql [, bool fail_on_error]) returns setof record
320
dblink(text connstr, text sql [, bool fail_on_error]) returns setof record
321
dblink(text sql [, bool fail_on_error]) returns setof record
326
<title>Description</title>
329
<function>dblink</> executes a query (usually a <command>SELECT</>,
330
but it can be any SQL statement that returns rows) in a remote database.
334
When two <type>text</> arguments are given, the first one is first
335
looked up as a persistent connection's name; if found, the command
336
is executed on that connection. If not found, the first argument
337
is treated as a connection info string as for <function>dblink_connect</>,
338
and the indicated connection is made just for the duration of this command.
343
<title>Arguments</title>
347
<term><parameter>conname</parameter></term>
350
Name of the connection to use; omit this parameter to use the
357
<term><parameter>connstr</parameter></term>
360
A connection info string, as previously described for
361
<function>dblink_connect</>.
367
<term><parameter>sql</parameter></term>
370
The SQL query that you wish to execute in the remote database,
371
for example <literal>select * from foo</>.
377
<term><parameter>fail_on_error</parameter></term>
380
If true (the default when omitted) then an error thrown on the
381
remote side of the connection causes an error to also be thrown
382
locally. If false, the remote error is locally reported as a NOTICE,
383
and the function returns no rows.
391
<title>Return Value</title>
394
The function returns the row(s) produced by the query. Since
395
<function>dblink</> can be used with any query, it is declared
396
to return <type>record</>, rather than specifying any particular
397
set of columns. This means that you must specify the expected
398
set of columns in the calling query — otherwise
399
<productname>PostgreSQL</> would not know what to expect.
404
FROM dblink('dbname=mydb', 'select proname, prosrc from pg_proc')
405
AS t1(proname name, prosrc text)
406
WHERE proname LIKE 'bytea%';
409
The <quote>alias</> part of the <literal>FROM</> clause must
410
specify the column names and types that the function will return.
411
(Specifying column names in an alias is actually standard SQL
412
syntax, but specifying column types is a <productname>PostgreSQL</>
413
extension.) This allows the system to understand what
414
<literal>*</> should expand to, and what <structname>proname</>
415
in the <literal>WHERE</> clause refers to, in advance of trying
416
to execute the function. At run time, an error will be thrown
417
if the actual query result from the remote database does not
418
have the same number of columns shown in the <literal>FROM</> clause.
419
The column names need not match, however, and <function>dblink</>
420
does not insist on exact type matches either. It will succeed
421
so long as the returned data strings are valid input for the
422
column type declared in the <literal>FROM</> clause.
430
<function>dblink</> fetches the entire remote query result before
431
returning any of it to the local system. If the query is expected
432
to return a large number of rows, it's better to open it as a cursor
433
with <function>dblink_open</> and then fetch a manageable number
438
A convenient way to use <function>dblink</> with predetermined
439
queries is to create a view.
440
This allows the column type information to be buried in the view,
441
instead of having to spell it out in every query. For example,
444
CREATE VIEW myremote_pg_proc AS
446
FROM dblink('dbname=postgres', 'select proname, prosrc from pg_proc')
447
AS t1(proname name, prosrc text);
449
SELECT * FROM myremote_pg_proc WHERE proname LIKE 'bytea%';
455
<title>Example</title>
458
SELECT * FROM dblink('dbname=postgres', 'select proname, prosrc from pg_proc')
459
AS t1(proname name, prosrc text) WHERE proname LIKE 'bytea%';
461
------------+------------
470
bytealike | bytealike
471
byteanlike | byteanlike
476
SELECT dblink_connect('dbname=postgres');
482
SELECT * FROM dblink('select proname, prosrc from pg_proc')
483
AS t1(proname name, prosrc text) WHERE proname LIKE 'bytea%';
485
------------+------------
494
bytealike | bytealike
495
byteanlike | byteanlike
500
SELECT dblink_connect('myconn', 'dbname=regression');
506
SELECT * FROM dblink('myconn', 'select proname, prosrc from pg_proc')
507
AS t1(proname name, prosrc text) WHERE proname LIKE 'bytea%';
509
------------+------------
510
bytearecv | bytearecv
511
byteasend | byteasend
517
bytealike | bytealike
518
byteanlike | byteanlike
529
<refentry id="CONTRIB-DBLINK-EXEC">
531
<refentrytitle>dblink_exec</refentrytitle>
532
<manvolnum>3</manvolnum>
536
<refname>dblink_exec</refname>
537
<refpurpose>executes a command in a remote database</refpurpose>
542
dblink_exec(text connname, text sql [, bool fail_on_error]) returns text
543
dblink_exec(text connstr, text sql [, bool fail_on_error]) returns text
544
dblink_exec(text sql [, bool fail_on_error]) returns text
549
<title>Description</title>
552
<function>dblink_exec</> executes a command (that is, any SQL statement
553
that doesn't return rows) in a remote database.
557
When two <type>text</> arguments are given, the first one is first
558
looked up as a persistent connection's name; if found, the command
559
is executed on that connection. If not found, the first argument
560
is treated as a connection info string as for <function>dblink_connect</>,
561
and the indicated connection is made just for the duration of this command.
566
<title>Arguments</title>
570
<term><parameter>conname</parameter></term>
573
Name of the connection to use; omit this parameter to use the
580
<term><parameter>connstr</parameter></term>
583
A connection info string, as previously described for
584
<function>dblink_connect</>.
590
<term><parameter>sql</parameter></term>
593
The SQL command that you wish to execute in the remote database,
595
<literal>insert into foo values(0,'a','{"a0","b0","c0"}')</>.
601
<term><parameter>fail_on_error</parameter></term>
604
If true (the default when omitted) then an error thrown on the
605
remote side of the connection causes an error to also be thrown
606
locally. If false, the remote error is locally reported as a NOTICE,
607
and the function's return value is set to <literal>ERROR</>.
615
<title>Return Value</title>
618
Returns status, either the command's status string or <literal>ERROR</>.
623
<title>Example</title>
626
SELECT dblink_connect('dbname=dblink_test_standby');
632
SELECT dblink_exec('insert into foo values(21,''z'',''{"a0","b0","c0"}'');');
638
SELECT dblink_connect('myconn', 'dbname=regression');
644
SELECT dblink_exec('myconn', 'insert into foo values(21,''z'',''{"a0","b0","c0"}'');');
650
SELECT dblink_exec('myconn', 'insert into pg_class values (''foo'')',false);
652
DETAIL: ERROR: null value in column "relnamespace" violates not-null constraint
662
<refentry id="CONTRIB-DBLINK-OPEN">
664
<refentrytitle>dblink_open</refentrytitle>
665
<manvolnum>3</manvolnum>
669
<refname>dblink_open</refname>
670
<refpurpose>opens a cursor in a remote database</refpurpose>
675
dblink_open(text cursorname, text sql [, bool fail_on_error]) returns text
676
dblink_open(text connname, text cursorname, text sql [, bool fail_on_error]) returns text
681
<title>Description</title>
684
<function>dblink_open()</> opens a cursor in a remote database.
685
The cursor can subsequently be manipulated with
686
<function>dblink_fetch()</> and <function>dblink_close()</>.
691
<title>Arguments</title>
695
<term><parameter>conname</parameter></term>
698
Name of the connection to use; omit this parameter to use the
705
<term><parameter>cursorname</parameter></term>
708
The name to assign to this cursor.
714
<term><parameter>sql</parameter></term>
717
The <command>SELECT</> statement that you wish to execute in the remote
718
database, for example <literal>select * from pg_class</>.
724
<term><parameter>fail_on_error</parameter></term>
727
If true (the default when omitted) then an error thrown on the
728
remote side of the connection causes an error to also be thrown
729
locally. If false, the remote error is locally reported as a NOTICE,
730
and the function's return value is set to <literal>ERROR</>.
738
<title>Return Value</title>
741
Returns status, either <literal>OK</> or <literal>ERROR</>.
749
Since a cursor can only persist within a transaction,
750
<function>dblink_open</> starts an explicit transaction block
751
(<command>BEGIN</>) on the remote side, if the remote side was
752
not already within a transaction. This transaction will be
753
closed again when the matching <function>dblink_close</> is
754
executed. Note that if
755
you use <function>dblink_exec</> to change data between
756
<function>dblink_open</> and <function>dblink_close</>,
757
and then an error occurs or you use <function>dblink_disconnect</> before
758
<function>dblink_close</>, your change <emphasis>will be
759
lost</> because the transaction will be aborted.
764
<title>Example</title>
767
SELECT dblink_connect('dbname=postgres');
773
SELECT dblink_open('foo', 'select proname, prosrc from pg_proc');
782
<refentry id="CONTRIB-DBLINK-FETCH">
784
<refentrytitle>dblink_fetch</refentrytitle>
785
<manvolnum>3</manvolnum>
789
<refname>dblink_fetch</refname>
790
<refpurpose>returns rows from an open cursor in a remote database</refpurpose>
795
dblink_fetch(text cursorname, int howmany [, bool fail_on_error]) returns setof record
796
dblink_fetch(text connname, text cursorname, int howmany [, bool fail_on_error]) returns setof record
801
<title>Description</title>
804
<function>dblink_fetch</> fetches rows from a cursor previously
805
established by <function>dblink_open</>.
810
<title>Arguments</title>
814
<term><parameter>conname</parameter></term>
817
Name of the connection to use; omit this parameter to use the
824
<term><parameter>cursorname</parameter></term>
827
The name of the cursor to fetch from.
833
<term><parameter>howmany</parameter></term>
836
The maximum number of rows to retrieve. The next <parameter>howmany</>
837
rows are fetched, starting at the current cursor position, moving
838
forward. Once the cursor has reached its end, no more rows are produced.
844
<term><parameter>fail_on_error</parameter></term>
847
If true (the default when omitted) then an error thrown on the
848
remote side of the connection causes an error to also be thrown
849
locally. If false, the remote error is locally reported as a NOTICE,
850
and the function returns no rows.
858
<title>Return Value</title>
861
The function returns the row(s) fetched from the cursor. To use this
862
function, you will need to specify the expected set of columns,
863
as previously discussed for <function>dblink</>.
871
On a mismatch between the number of return columns specified in the
872
<literal>FROM</> clause, and the actual number of columns returned by the
873
remote cursor, an error will be thrown. In this event, the remote cursor
874
is still advanced by as many rows as it would have been if the error had
875
not occurred. The same is true for any other error occurring in the local
876
query after the remote <command>FETCH</> has been done.
881
<title>Example</title>
884
SELECT dblink_connect('dbname=postgres');
890
SELECT dblink_open('foo', 'select proname, prosrc from pg_proc where proname like ''bytea%''');
896
SELECT * FROM dblink_fetch('foo', 5) AS (funcname name, source text);
898
----------+----------
906
SELECT * FROM dblink_fetch('foo', 5) AS (funcname name, source text);
908
-----------+-----------
911
bytealike | bytealike
916
SELECT * FROM dblink_fetch('foo', 5) AS (funcname name, source text);
918
------------+------------
919
byteanlike | byteanlike
923
SELECT * FROM dblink_fetch('foo', 5) AS (funcname name, source text);
931
<refentry id="CONTRIB-DBLINK-CLOSE">
933
<refentrytitle>dblink_close</refentrytitle>
934
<manvolnum>3</manvolnum>
938
<refname>dblink_close</refname>
939
<refpurpose>closes a cursor in a remote database</refpurpose>
944
dblink_close(text cursorname [, bool fail_on_error]) returns text
945
dblink_close(text connname, text cursorname [, bool fail_on_error]) returns text
950
<title>Description</title>
953
<function>dblink_close</> closes a cursor previously opened with
954
<function>dblink_open</>.
959
<title>Arguments</title>
963
<term><parameter>conname</parameter></term>
966
Name of the connection to use; omit this parameter to use the
973
<term><parameter>cursorname</parameter></term>
976
The name of the cursor to close.
982
<term><parameter>fail_on_error</parameter></term>
985
If true (the default when omitted) then an error thrown on the
986
remote side of the connection causes an error to also be thrown
987
locally. If false, the remote error is locally reported as a NOTICE,
988
and the function's return value is set to <literal>ERROR</>.
996
<title>Return Value</title>
999
Returns status, either <literal>OK</> or <literal>ERROR</>.
1004
<title>Notes</title>
1007
If <function>dblink_open</> started an explicit transaction block,
1008
and this is the last remaining open cursor in this connection,
1009
<function>dblink_close</> will issue the matching <command>COMMIT</>.
1014
<title>Example</title>
1017
SELECT dblink_connect('dbname=postgres');
1023
SELECT dblink_open('foo', 'select proname, prosrc from pg_proc');
1029
SELECT dblink_close('foo');
1038
<refentry id="CONTRIB-DBLINK-GET-CONNECTIONS">
1040
<refentrytitle>dblink_get_connections</refentrytitle>
1041
<manvolnum>3</manvolnum>
1045
<refname>dblink_get_connections</refname>
1046
<refpurpose>returns the names of all open named dblink connections</refpurpose>
1051
dblink_get_connections() returns text[]
1056
<title>Description</title>
1059
<function>dblink_get_connections</> returns an array of the names
1060
of all open named <filename>dblink</> connections.
1065
<title>Return Value</title>
1067
<para>Returns a text array of connection names, or NULL if none.</para>
1071
<title>Example</title>
1074
SELECT dblink_get_connections();
1079
<refentry id="CONTRIB-DBLINK-ERROR-MESSAGE">
1081
<refentrytitle>dblink_error_message</refentrytitle>
1082
<manvolnum>3</manvolnum>
1086
<refname>dblink_error_message</refname>
1087
<refpurpose>gets last error message on the named connection</refpurpose>
1092
dblink_error_message(text connname) returns text
1097
<title>Description</title>
1100
<function>dblink_error_message</> fetches the most recent remote
1101
error message for a given connection.
1106
<title>Arguments</title>
1110
<term><parameter>conname</parameter></term>
1113
Name of the connection to use.
1121
<title>Return Value</title>
1124
Returns last error message, or an empty string if there has been
1125
no error in this connection.
1130
<title>Example</title>
1133
SELECT dblink_error_message('dtest1');
1138
<refentry id="CONTRIB-DBLINK-SEND-QUERY">
1140
<refentrytitle>dblink_send_query</refentrytitle>
1141
<manvolnum>3</manvolnum>
1145
<refname>dblink_send_query</refname>
1146
<refpurpose>sends an async query to a remote database</refpurpose>
1151
dblink_send_query(text connname, text sql) returns int
1156
<title>Description</title>
1159
<function>dblink_send_query</> sends a query to be executed
1160
asynchronously, that is, without immediately waiting for the result.
1161
There must not be an async query already in progress on the
1166
After successfully dispatching an async query, completion status
1167
can be checked with <function>dblink_is_busy</>, and the results
1168
are ultimately collected with <function>dblink_get_result</>.
1169
It is also possible to attempt to cancel an active async query
1170
using <function>dblink_cancel_query</>.
1175
<title>Arguments</title>
1179
<term><parameter>conname</parameter></term>
1182
Name of the connection to use.
1188
<term><parameter>sql</parameter></term>
1191
The SQL statement that you wish to execute in the remote database,
1192
for example <literal>select * from pg_class</>.
1200
<title>Return Value</title>
1203
Returns 1 if the query was successfully dispatched, 0 otherwise.
1208
<title>Example</title>
1211
SELECT dblink_send_query('dtest1', 'SELECT * FROM foo WHERE f1 < 3');
1216
<refentry id="CONTRIB-DBLINK-IS-BUSY">
1218
<refentrytitle>dblink_is_busy</refentrytitle>
1219
<manvolnum>3</manvolnum>
1223
<refname>dblink_is_busy</refname>
1224
<refpurpose>checks if connection is busy with an async query</refpurpose>
1229
dblink_is_busy(text connname) returns int
1234
<title>Description</title>
1237
<function>dblink_is_busy</> tests whether an async query is in progress.
1242
<title>Arguments</title>
1246
<term><parameter>conname</parameter></term>
1249
Name of the connection to check.
1257
<title>Return Value</title>
1260
Returns 1 if connection is busy, 0 if it is not busy.
1261
If this function returns 0, it is guaranteed that
1262
<function>dblink_get_result</> will not block.
1267
<title>Example</title>
1270
SELECT dblink_is_busy('dtest1');
1275
<refentry id="CONTRIB-DBLINK-GET-NOTIFY">
1277
<refentrytitle>dblink_get_notify</refentrytitle>
1278
<manvolnum>3</manvolnum>
1282
<refname>dblink_get_notify</refname>
1283
<refpurpose>retrieve async notifications on a connection</refpurpose>
1288
dblink_get_notify() returns setof (notify_name text, be_pid int, extra text)
1289
dblink_get_notify(text connname) returns setof (notify_name text, be_pid int, extra text)
1294
<title>Description</title>
1297
<function>dblink_get_notify</> retrieves notifications on either
1298
the unnamed connection, or on a named connection if specified.
1299
To receive notifications via dblink, <function>LISTEN</> must
1300
first be issued, using <function>dblink_exec</>.
1301
For details see <xref linkend="sql-listen"> and <xref linkend="sql-notify">.
1307
<title>Arguments</title>
1311
<term><parameter>conname</parameter></term>
1314
The name of a named connection to get notifications on.
1322
<title>Return Value</title>
1323
<para>Returns <type>setof (notify_name text, be_pid int, extra text)</type>, or an empty set if none.</para>
1327
<title>Example</title>
1330
SELECT dblink_exec('LISTEN virtual');
1336
SELECT * FROM dblink_get_notify();
1337
notify_name | be_pid | extra
1338
-------------+--------+-------
1344
SELECT * FROM dblink_get_notify();
1345
notify_name | be_pid | extra
1346
-------------+--------+-------
1353
<refentry id="CONTRIB-DBLINK-GET-RESULT">
1355
<refentrytitle>dblink_get_result</refentrytitle>
1356
<manvolnum>3</manvolnum>
1360
<refname>dblink_get_result</refname>
1361
<refpurpose>gets an async query result</refpurpose>
1366
dblink_get_result(text connname [, bool fail_on_error]) returns setof record
1371
<title>Description</title>
1374
<function>dblink_get_result</> collects the results of an
1375
asynchronous query previously sent with <function>dblink_send_query</>.
1376
If the query is not already completed, <function>dblink_get_result</>
1377
will wait until it is.
1382
<title>Arguments</title>
1386
<term><parameter>conname</parameter></term>
1389
Name of the connection to use.
1395
<term><parameter>fail_on_error</parameter></term>
1398
If true (the default when omitted) then an error thrown on the
1399
remote side of the connection causes an error to also be thrown
1400
locally. If false, the remote error is locally reported as a NOTICE,
1401
and the function returns no rows.
1409
<title>Return Value</title>
1412
For an async query (that is, a SQL statement returning rows),
1413
the function returns the row(s) produced by the query. To use this
1414
function, you will need to specify the expected set of columns,
1415
as previously discussed for <function>dblink</>.
1419
For an async command (that is, a SQL statement not returning rows),
1420
the function returns a single row with a single text column containing
1421
the command's status string. It is still necessary to specify that
1422
the result will have a single text column in the calling <literal>FROM</>
1428
<title>Notes</title>
1431
This function <emphasis>must</> be called if
1432
<function>dblink_send_query</> returned 1.
1433
It must be called once for each query
1434
sent, and one additional time to obtain an empty set result,
1435
before the connection can be used again.
1440
<title>Example</title>
1443
contrib_regression=# SELECT dblink_connect('dtest1', 'dbname=contrib_regression');
1449
contrib_regression=# SELECT * FROM
1450
contrib_regression-# dblink_send_query('dtest1', 'select * from foo where f1 < 3') AS t1;
1456
contrib_regression=# SELECT * FROM dblink_get_result('dtest1') AS t1(f1 int, f2 text, f3 text[]);
1458
----+----+------------
1464
contrib_regression=# SELECT * FROM dblink_get_result('dtest1') AS t1(f1 int, f2 text, f3 text[]);
1469
contrib_regression=# SELECT * FROM
1470
contrib_regression-# dblink_send_query('dtest1', 'select * from foo where f1 < 3; select * from foo where f1 > 6') AS t1;
1476
contrib_regression=# SELECT * FROM dblink_get_result('dtest1') AS t1(f1 int, f2 text, f3 text[]);
1478
----+----+------------
1484
contrib_regression=# SELECT * FROM dblink_get_result('dtest1') AS t1(f1 int, f2 text, f3 text[]);
1486
----+----+---------------
1490
10 | k | {a10,b10,c10}
1493
contrib_regression=# SELECT * FROM dblink_get_result('dtest1') AS t1(f1 int, f2 text, f3 text[]);
1501
<refentry id="CONTRIB-DBLINK-CANCEL-QUERY">
1503
<refentrytitle>dblink_cancel_query</refentrytitle>
1504
<manvolnum>3</manvolnum>
1508
<refname>dblink_cancel_query</refname>
1509
<refpurpose>cancels any active query on the named connection</refpurpose>
1514
dblink_cancel_query(text connname) returns text
1519
<title>Description</title>
1522
<function>dblink_cancel_query</> attempts to cancel any query that
1523
is in progress on the named connection. Note that this is not
1524
certain to succeed (since, for example, the remote query might
1525
already have finished). A cancel request simply improves the
1526
odds that the query will fail soon. You must still complete the
1527
normal query protocol, for example by calling
1528
<function>dblink_get_result</>.
1533
<title>Arguments</title>
1537
<term><parameter>conname</parameter></term>
1540
Name of the connection to use.
1548
<title>Return Value</title>
1551
Returns <literal>OK</> if the cancel request has been sent, or
1552
the text of an error message on failure.
1557
<title>Example</title>
1560
SELECT dblink_cancel_query('dtest1');
1565
<refentry id="CONTRIB-DBLINK-GET-PKEY">
1567
<refentrytitle>dblink_get_pkey</refentrytitle>
1568
<manvolnum>3</manvolnum>
1572
<refname>dblink_get_pkey</refname>
1573
<refpurpose>returns the positions and field names of a relation's
1580
dblink_get_pkey(text relname) returns setof dblink_pkey_results
1585
<title>Description</title>
1588
<function>dblink_get_pkey</> provides information about the primary
1589
key of a relation in the local database. This is sometimes useful
1590
in generating queries to be sent to remote databases.
1595
<title>Arguments</title>
1599
<term><parameter>relname</parameter></term>
1602
Name of a local relation, for example <literal>foo</> or
1603
<literal>myschema.mytab</>. Include double quotes if the
1604
name is mixed-case or contains special characters, for
1605
example <literal>"FooBar"</>; without quotes, the string
1606
will be folded to lower case.
1614
<title>Return Value</title>
1617
Returns one row for each primary key field, or no rows if the relation
1618
has no primary key. The result row type is defined as
1621
CREATE TYPE dblink_pkey_results AS (position int, colname text);
1624
The <literal>position</> column simply runs from 1 to <replaceable>N</>;
1625
it is the number of the field within the primary key, not the number
1626
within the table's columns.
1631
<title>Example</title>
1634
CREATE TABLE foobar (
1638
PRIMARY KEY (f1, f2, f3)
1642
SELECT * FROM dblink_get_pkey('foobar');
1644
----------+---------
1653
<refentry id="CONTRIB-DBLINK-BUILD-SQL-INSERT">
1655
<refentrytitle>dblink_build_sql_insert</refentrytitle>
1656
<manvolnum>3</manvolnum>
1660
<refname>dblink_build_sql_insert</refname>
1662
builds an INSERT statement using a local tuple, replacing the
1663
primary key field values with alternative supplied values
1669
dblink_build_sql_insert(text relname,
1670
int2vector primary_key_attnums,
1671
integer num_primary_key_atts,
1672
text[] src_pk_att_vals_array,
1673
text[] tgt_pk_att_vals_array) returns text
1678
<title>Description</title>
1681
<function>dblink_build_sql_insert</> can be useful in doing selective
1682
replication of a local table to a remote database. It selects a row
1683
from the local table based on primary key, and then builds a SQL
1684
<command>INSERT</> command that will duplicate that row, but with
1685
the primary key values replaced by the values in the last argument.
1686
(To make an exact copy of the row, just specify the same values for
1687
the last two arguments.)
1692
<title>Arguments</title>
1696
<term><parameter>relname</parameter></term>
1699
Name of a local relation, for example <literal>foo</> or
1700
<literal>myschema.mytab</>. Include double quotes if the
1701
name is mixed-case or contains special characters, for
1702
example <literal>"FooBar"</>; without quotes, the string
1703
will be folded to lower case.
1709
<term><parameter>primary_key_attnums</parameter></term>
1712
Attribute numbers (1-based) of the primary key fields,
1713
for example <literal>1 2</>.
1719
<term><parameter>num_primary_key_atts</parameter></term>
1722
The number of primary key fields.
1728
<term><parameter>src_pk_att_vals_array</parameter></term>
1731
Values of the primary key fields to be used to look up the
1732
local tuple. Each field is represented in text form.
1733
An error is thrown if there is no local row with these
1740
<term><parameter>tgt_pk_att_vals_array</parameter></term>
1743
Values of the primary key fields to be placed in the resulting
1744
<command>INSERT</> command. Each field is represented in text form.
1752
<title>Return Value</title>
1754
<para>Returns the requested SQL statement as text.</para>
1758
<title>Notes</title>
1761
As of <productname>PostgreSQL</> 9.0, the attribute numbers in
1762
<parameter>primary_key_attnums</parameter> are interpreted as logical
1763
column numbers, corresponding to the column's position in
1764
<literal>SELECT * FROM relname</>. Previous versions interpreted the
1765
numbers as physical column positions. There is a difference if any
1766
column(s) to the left of the indicated column have been dropped during
1767
the lifetime of the table.
1772
<title>Example</title>
1775
SELECT dblink_build_sql_insert('foo', '1 2', 2, '{"1", "a"}', '{"1", "b''a"}');
1776
dblink_build_sql_insert
1777
--------------------------------------------------
1778
INSERT INTO foo(f1,f2,f3) VALUES('1','b''a','1')
1784
<refentry id="CONTRIB-DBLINK-BUILD-SQL-DELETE">
1786
<refentrytitle>dblink_build_sql_delete</refentrytitle>
1787
<manvolnum>3</manvolnum>
1791
<refname>dblink_build_sql_delete</refname>
1792
<refpurpose>builds a DELETE statement using supplied values for primary
1799
dblink_build_sql_delete(text relname,
1800
int2vector primary_key_attnums,
1801
integer num_primary_key_atts,
1802
text[] tgt_pk_att_vals_array) returns text
1807
<title>Description</title>
1810
<function>dblink_build_sql_delete</> can be useful in doing selective
1811
replication of a local table to a remote database. It builds a SQL
1812
<command>DELETE</> command that will delete the row with the given
1818
<title>Arguments</title>
1822
<term><parameter>relname</parameter></term>
1825
Name of a local relation, for example <literal>foo</> or
1826
<literal>myschema.mytab</>. Include double quotes if the
1827
name is mixed-case or contains special characters, for
1828
example <literal>"FooBar"</>; without quotes, the string
1829
will be folded to lower case.
1835
<term><parameter>primary_key_attnums</parameter></term>
1838
Attribute numbers (1-based) of the primary key fields,
1839
for example <literal>1 2</>.
1845
<term><parameter>num_primary_key_atts</parameter></term>
1848
The number of primary key fields.
1854
<term><parameter>tgt_pk_att_vals_array</parameter></term>
1857
Values of the primary key fields to be used in the resulting
1858
<command>DELETE</> command. Each field is represented in text form.
1866
<title>Return Value</title>
1868
<para>Returns the requested SQL statement as text.</para>
1872
<title>Notes</title>
1875
As of <productname>PostgreSQL</> 9.0, the attribute numbers in
1876
<parameter>primary_key_attnums</parameter> are interpreted as logical
1877
column numbers, corresponding to the column's position in
1878
<literal>SELECT * FROM relname</>. Previous versions interpreted the
1879
numbers as physical column positions. There is a difference if any
1880
column(s) to the left of the indicated column have been dropped during
1881
the lifetime of the table.
1886
<title>Example</title>
1889
SELECT dblink_build_sql_delete('"MyFoo"', '1 2', 2, '{"1", "b"}');
1890
dblink_build_sql_delete
1891
---------------------------------------------
1892
DELETE FROM "MyFoo" WHERE f1='1' AND f2='b'
1898
<refentry id="CONTRIB-DBLINK-BUILD-SQL-UPDATE">
1900
<refentrytitle>dblink_build_sql_update</refentrytitle>
1901
<manvolnum>3</manvolnum>
1905
<refname>dblink_build_sql_update</refname>
1906
<refpurpose>builds an UPDATE statement using a local tuple, replacing
1907
the primary key field values with alternative supplied values
1913
dblink_build_sql_update(text relname,
1914
int2vector primary_key_attnums,
1915
integer num_primary_key_atts,
1916
text[] src_pk_att_vals_array,
1917
text[] tgt_pk_att_vals_array) returns text
1922
<title>Description</title>
1925
<function>dblink_build_sql_update</> can be useful in doing selective
1926
replication of a local table to a remote database. It selects a row
1927
from the local table based on primary key, and then builds a SQL
1928
<command>UPDATE</> command that will duplicate that row, but with
1929
the primary key values replaced by the values in the last argument.
1930
(To make an exact copy of the row, just specify the same values for
1931
the last two arguments.) The <command>UPDATE</> command always assigns
1932
all fields of the row — the main difference between this and
1933
<function>dblink_build_sql_insert</> is that it's assumed that
1934
the target row already exists in the remote table.
1939
<title>Arguments</title>
1943
<term><parameter>relname</parameter></term>
1946
Name of a local relation, for example <literal>foo</> or
1947
<literal>myschema.mytab</>. Include double quotes if the
1948
name is mixed-case or contains special characters, for
1949
example <literal>"FooBar"</>; without quotes, the string
1950
will be folded to lower case.
1956
<term><parameter>primary_key_attnums</parameter></term>
1959
Attribute numbers (1-based) of the primary key fields,
1960
for example <literal>1 2</>.
1966
<term><parameter>num_primary_key_atts</parameter></term>
1969
The number of primary key fields.
1975
<term><parameter>src_pk_att_vals_array</parameter></term>
1978
Values of the primary key fields to be used to look up the
1979
local tuple. Each field is represented in text form.
1980
An error is thrown if there is no local row with these
1987
<term><parameter>tgt_pk_att_vals_array</parameter></term>
1990
Values of the primary key fields to be placed in the resulting
1991
<command>UPDATE</> command. Each field is represented in text form.
1999
<title>Return Value</title>
2001
<para>Returns the requested SQL statement as text.</para>
2005
<title>Notes</title>
2008
As of <productname>PostgreSQL</> 9.0, the attribute numbers in
2009
<parameter>primary_key_attnums</parameter> are interpreted as logical
2010
column numbers, corresponding to the column's position in
2011
<literal>SELECT * FROM relname</>. Previous versions interpreted the
2012
numbers as physical column positions. There is a difference if any
2013
column(s) to the left of the indicated column have been dropped during
2014
the lifetime of the table.
2019
<title>Example</title>
2022
SELECT dblink_build_sql_update('foo', '1 2', 2, '{"1", "a"}', '{"1", "b"}');
2023
dblink_build_sql_update
2024
-------------------------------------------------------------
2025
UPDATE foo SET f1='1',f2='b',f3='1' WHERE f1='1' AND f2='b'
added
removed
doc/src/sgml/dblink.sgml
