Debian Codegraph @

doc/src/sgml/dblink.sgml

raw
1 <!-- doc/src/sgml/dblink.sgml -->
2
3 <sect1 id="dblink" xreflabel="dblink">
4 <title>dblink &mdash; connect to other PostgreSQL databases</title>
5
6 <indexterm zone="dblink">
7 <primary>dblink</primary>
8 </indexterm>
9
10 <para>
11 <filename>dblink</filename> is a module that supports connections to
12 other <productname>PostgreSQL</productname> databases from within a database
13 session.
14 </para>
15
16 <para>
17 <filename>dblink</filename> can report the following wait events under the wait
18 event type <literal>Extension</literal>.
19 </para>
20
21 <variablelist>
22 <varlistentry>
23 <term><literal>DblinkConnect</literal></term>
24 <listitem>
25 <para>
26 Waiting to establish a connection to a remote server.
27 </para>
28 </listitem>
29 </varlistentry>
30
31 <varlistentry>
32 <term><literal>DblinkGetConnect</literal></term>
33 <listitem>
34 <para>
35 Waiting to establish a connection to a remote server when it could not
36 be found in the list of already-opened connections.
37 </para>
38 </listitem>
39 </varlistentry>
40
41 <varlistentry>
42 <term><literal>DblinkGetResult</literal></term>
43 <listitem>
44 <para>
45 Waiting to receive the results of a query from a remote server.
46 </para>
47 </listitem>
48 </varlistentry>
49 </variablelist>
50
51 <para>
52 See also <xref linkend="postgres-fdw"/>, which provides roughly the same
53 functionality using a more modern and standards-compliant infrastructure.
54 </para>
55
56 <refentry id="contrib-dblink-connect">
57 <indexterm>
58 <primary>dblink_connect</primary>
59 </indexterm>
60
61 <refmeta>
62 <refentrytitle>dblink_connect</refentrytitle>
63 <manvolnum>3</manvolnum>
64 </refmeta>
65
66 <refnamediv>
67 <refname>dblink_connect</refname>
68 <refpurpose>opens a persistent connection to a remote database</refpurpose>
69 </refnamediv>
70
71 <refsynopsisdiv>
72 <synopsis>
73 dblink_connect(text connstr) returns text
74 dblink_connect(text connname, text connstr) returns text
75 </synopsis>
76 </refsynopsisdiv>
77
78 <refsect1>
79 <title>Description</title>
80
81 <para>
82 <function>dblink_connect()</function> establishes a connection to a remote
83 <productname>PostgreSQL</productname> database. The server and database to
84 be contacted are identified through a standard <application>libpq</application>
85 connection string. Optionally, a name can be assigned to the
86 connection. Multiple named connections can be open at once, but
87 only one unnamed connection is permitted at a time. The connection
88 will persist until closed or until the database session is ended.
89 </para>
90
91 <para>
92 The connection string may also be the name of an existing foreign
93 server. It is recommended to use the foreign-data wrapper
94 <literal>dblink_fdw</literal> when defining the foreign
95 server. See the example below, as well as
96 <xref linkend="sql-createserver"/> and
97 <xref linkend="sql-createusermapping"/>.
98 </para>
99
100 </refsect1>
101
102 <refsect1>
103 <title>Arguments</title>
104
105 <variablelist>
106 <varlistentry>
107 <term><parameter>connname</parameter></term>
108 <listitem>
109 <para>
110 The name to use for this connection; if omitted, an unnamed
111 connection is opened, replacing any existing unnamed connection.
112 </para>
113 </listitem>
114 </varlistentry>
115
116 <varlistentry>
117 <term><parameter>connstr</parameter></term>
118 <listitem>
119 <para><application>libpq</application>-style connection info string, for example
120 <literal>hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres
121 password=mypasswd options=-csearch_path=</literal>.
122 For details see <xref linkend="libpq-connstring"/>.
123 Alternatively, the name of a foreign server.
124 </para>
125 </listitem>
126 </varlistentry>
127 </variablelist>
128 </refsect1>
129
130 <refsect1>
131 <title>Return Value</title>
132
133 <para>
134 Returns status, which is always <literal>OK</literal> (since any error
135 causes the function to throw an error instead of returning).
136 </para>
137 </refsect1>
138
139 <refsect1>
140 <title>Notes</title>
141
142 <para>
143 If untrusted users have access to a database that has not adopted a
144 <link linkend="ddl-schemas-patterns">secure schema usage pattern</link>,
145 begin each session by removing publicly-writable schemas from
146 <varname>search_path</varname>. One could, for example,
147 add <literal>options=-csearch_path=</literal> to
148 <parameter>connstr</parameter>. This consideration is not specific
149 to <filename>dblink</filename>; it applies to every interface for
150 executing arbitrary SQL commands.
151 </para>
152
153 <para>
154 The foreign-data wrapper <filename>dblink_fdw</filename> has an additional
155 Boolean option <literal>use_scram_passthrough</literal> that controls
156 whether <filename>dblink</filename> will use the SCRAM pass-through
157 authentication to connect to the remote database. It can be specified
158 for a foreign server or a user mapping. A user mapping setting overrides
159 the foreign server setting. With SCRAM pass-through authentication,
160 <filename>dblink</filename> uses SCRAM-hashed secrets instead of plain-text
161 user passwords to connect to the remote server. This avoids storing
162 plain-text user passwords in PostgreSQL system catalogs.
163 See the documentation of the equivalent <link
164 linkend="postgres-fdw-option-use-scram-passthrough"><literal>use_scram_passthrough</literal></link>
165 option of postgres_fdw for further details and restrictions.
166 </para>
167
168 <para>
169 Only superusers may use <function>dblink_connect</function> to create
170 connections that use neither password authentication, SCRAM pass-through,
171 nor GSSAPI-authentication.
172 If non-superusers need this capability, use
173 <function>dblink_connect_u</function> instead.
174 </para>
175
176 <para>
177 It is unwise to choose connection names that contain equal signs,
178 as this opens a risk of confusion with connection info strings
179 in other <filename>dblink</filename> functions.
180 </para>
181 </refsect1>
182
183 <refsect1>
184 <title>Examples</title>
185
186 <screen>
187 SELECT dblink_connect('dbname=postgres options=-csearch_path=');
188 dblink_connect
189 ----------------
190 OK
191 (1 row)
192
193 SELECT dblink_connect('myconn', 'dbname=postgres options=-csearch_path=');
194 dblink_connect
195 ----------------
196 OK
197 (1 row)
198
199 -- FOREIGN DATA WRAPPER functionality
200 -- Note: local connections that don't use SCRAM pass-through require password
201 -- authentication for this to work properly. Otherwise, you will receive
202 -- the following error from dblink_connect():
203 -- ERROR: password is required
204 -- DETAIL: Non-superuser cannot connect if the server does not request a password.
205 -- HINT: Target server's authentication method must be changed.
206
207 CREATE SERVER fdtest FOREIGN DATA WRAPPER dblink_fdw OPTIONS (hostaddr '127.0.0.1', dbname 'contrib_regression');
208
209 CREATE USER regress_dblink_user WITH PASSWORD 'secret';
210 CREATE USER MAPPING FOR regress_dblink_user SERVER fdtest OPTIONS (user 'regress_dblink_user', password 'secret');
211 GRANT USAGE ON FOREIGN SERVER fdtest TO regress_dblink_user;
212 GRANT SELECT ON TABLE foo TO regress_dblink_user;
213
214 \set ORIGINAL_USER :USER
215 \c - regress_dblink_user
216 SELECT dblink_connect('myconn', 'fdtest');
217 dblink_connect
218 ----------------
219 OK
220 (1 row)
221
222 SELECT * FROM dblink('myconn', 'SELECT * FROM foo') AS t(a int, b text, c text[]);
223 a | b | c
224 ----+---+---------------
225 0 | a | {a0,b0,c0}
226 1 | b | {a1,b1,c1}
227 2 | c | {a2,b2,c2}
228 3 | d | {a3,b3,c3}
229 4 | e | {a4,b4,c4}
230 5 | f | {a5,b5,c5}
231 6 | g | {a6,b6,c6}
232 7 | h | {a7,b7,c7}
233 8 | i | {a8,b8,c8}
234 9 | j | {a9,b9,c9}
235 10 | k | {a10,b10,c10}
236 (11 rows)
237
238 \c - :ORIGINAL_USER
239 REVOKE USAGE ON FOREIGN SERVER fdtest FROM regress_dblink_user;
240 REVOKE SELECT ON TABLE foo FROM regress_dblink_user;
241 DROP USER MAPPING FOR regress_dblink_user SERVER fdtest;
242 DROP USER regress_dblink_user;
243 DROP SERVER fdtest;
244 </screen>
245 </refsect1>
246 </refentry>
247
248 <refentry id="contrib-dblink-connect-u">
249 <indexterm>
250 <primary>dblink_connect_u</primary>
251 </indexterm>
252
253 <refmeta>
254 <refentrytitle>dblink_connect_u</refentrytitle>
255 <manvolnum>3</manvolnum>
256 </refmeta>
257
258 <refnamediv>
259 <refname>dblink_connect_u</refname>
260 <refpurpose>opens a persistent connection to a remote database, insecurely</refpurpose>
261 </refnamediv>
262
263 <refsynopsisdiv>
264 <synopsis>
265 dblink_connect_u(text connstr) returns text
266 dblink_connect_u(text connname, text connstr) returns text
267 </synopsis>
268 </refsynopsisdiv>
269
270 <refsect1>
271 <title>Description</title>
272
273 <para>
274 <function>dblink_connect_u()</function> is identical to
275 <function>dblink_connect()</function>, except that it will allow non-superusers
276 to connect using any authentication method.
277 </para>
278
279 <para>
280 If the remote server selects an authentication method that does not
281 involve a password, then impersonation and subsequent escalation of
282 privileges can occur, because the session will appear to have
283 originated from the user as which the local <productname>PostgreSQL</productname>
284 server runs. Also, even if the remote server does demand a password,
285 it is possible for the password to be supplied from the server
286 environment, such as a <filename>~/.pgpass</filename> file belonging to the
287 server's user. This opens not only a risk of impersonation, but the
288 possibility of exposing a password to an untrustworthy remote server.
289 Therefore, <function>dblink_connect_u()</function> is initially
290 installed with all privileges revoked from <literal>PUBLIC</literal>,
291 making it un-callable except by superusers. In some situations
292 it may be appropriate to grant <literal>EXECUTE</literal> permission for
293 <function>dblink_connect_u()</function> to specific users who are considered
294 trustworthy, but this should be done with care. It is also recommended
295 that any <filename>~/.pgpass</filename> file belonging to the server's user
296 <emphasis>not</emphasis> contain any records specifying a wildcard host name.
297 </para>
298
299 <para>
300 For further details see <function>dblink_connect()</function>.
301 </para>
302 </refsect1>
303 </refentry>
304
305 <refentry id="contrib-dblink-disconnect">
306 <indexterm>
307 <primary>dblink_disconnect</primary>
308 </indexterm>
309
310 <refmeta>
311 <refentrytitle>dblink_disconnect</refentrytitle>
312 <manvolnum>3</manvolnum>
313 </refmeta>
314
315 <refnamediv>
316 <refname>dblink_disconnect</refname>
317 <refpurpose>closes a persistent connection to a remote database</refpurpose>
318 </refnamediv>
319
320 <refsynopsisdiv>
321 <synopsis>
322 dblink_disconnect() returns text
323 dblink_disconnect(text connname) returns text
324 </synopsis>
325 </refsynopsisdiv>
326
327 <refsect1>
328 <title>Description</title>
329
330 <para>
331 <function>dblink_disconnect()</function> closes a connection previously opened
332 by <function>dblink_connect()</function>. The form with no arguments closes
333 an unnamed connection.
334 </para>
335 </refsect1>
336
337 <refsect1>
338 <title>Arguments</title>
339
340 <variablelist>
341 <varlistentry>
342 <term><parameter>connname</parameter></term>
343 <listitem>
344 <para>
345 The name of a named connection to be closed.
346 </para>
347 </listitem>
348 </varlistentry>
349 </variablelist>
350 </refsect1>
351
352 <refsect1>
353 <title>Return Value</title>
354
355 <para>
356 Returns status, which is always <literal>OK</literal> (since any error
357 causes the function to throw an error instead of returning).
358 </para>
359 </refsect1>
360
361 <refsect1>
362 <title>Examples</title>
363
364 <screen>
365 SELECT dblink_disconnect();
366 dblink_disconnect
367 -------------------
368 OK
369 (1 row)
370
371 SELECT dblink_disconnect('myconn');
372 dblink_disconnect
373 -------------------
374 OK
375 (1 row)
376 </screen>
377 </refsect1>
378 </refentry>
379
380 <refentry id="contrib-dblink-function">
381 <indexterm>
382 <primary>dblink</primary>
383 </indexterm>
384
385 <refmeta>
386 <refentrytitle>dblink</refentrytitle>
387 <manvolnum>3</manvolnum>
388 </refmeta>
389
390 <refnamediv>
391 <refname>dblink</refname>
392 <refpurpose>executes a query in a remote database</refpurpose>
393 </refnamediv>
394
395 <refsynopsisdiv>
396 <synopsis>
397 dblink(text connname, text sql [, bool fail_on_error]) returns setof record
398 dblink(text connstr, text sql [, bool fail_on_error]) returns setof record
399 dblink(text sql [, bool fail_on_error]) returns setof record
400 </synopsis>
401 </refsynopsisdiv>
402
403 <refsect1>
404 <title>Description</title>
405
406 <para>
407 <function>dblink</function> executes a query (usually a <command>SELECT</command>,
408 but it can be any SQL statement that returns rows) in a remote database.
409 </para>
410
411 <para>
412 When two <type>text</type> arguments are given, the first one is first
413 looked up as a persistent connection's name; if found, the command
414 is executed on that connection. If not found, the first argument
415 is treated as a connection info string as for <function>dblink_connect</function>,
416 and the indicated connection is made just for the duration of this command.
417 </para>
418 </refsect1>
419
420 <refsect1>
421 <title>Arguments</title>
422
423 <variablelist>
424 <varlistentry>
425 <term><parameter>connname</parameter></term>
426 <listitem>
427 <para>
428 Name of the connection to use; omit this parameter to use the
429 unnamed connection.
430 </para>
431 </listitem>
432 </varlistentry>
433
434 <varlistentry>
435 <term><parameter>connstr</parameter></term>
436 <listitem>
437 <para>
438 A connection info string, as previously described for
439 <function>dblink_connect</function>.
440 </para>
441 </listitem>
442 </varlistentry>
443
444 <varlistentry>
445 <term><parameter>sql</parameter></term>
446 <listitem>
447 <para>
448 The SQL query that you wish to execute in the remote database,
449 for example <literal>select * from foo</literal>.
450 </para>
451 </listitem>
452 </varlistentry>
453
454 <varlistentry>
455 <term><parameter>fail_on_error</parameter></term>
456 <listitem>
457 <para>
458 If true (the default when omitted) then an error thrown on the
459 remote side of the connection causes an error to also be thrown
460 locally. If false, the remote error is locally reported as a NOTICE,
461 and the function returns no rows.
462 </para>
463 </listitem>
464 </varlistentry>
465 </variablelist>
466 </refsect1>
467
468 <refsect1>
469 <title>Return Value</title>
470
471 <para>
472 The function returns the row(s) produced by the query. Since
473 <function>dblink</function> can be used with any query, it is declared
474 to return <type>record</type>, rather than specifying any particular
475 set of columns. This means that you must specify the expected
476 set of columns in the calling query &mdash; otherwise
477 <productname>PostgreSQL</productname> would not know what to expect.
478 Here is an example:
479
480 <programlisting>
481 SELECT *
482 FROM dblink('dbname=mydb options=-csearch_path=',
483 'select proname, prosrc from pg_proc')
484 AS t1(proname name, prosrc text)
485 WHERE proname LIKE 'bytea%';
486 </programlisting>
487
488 The <quote>alias</quote> part of the <literal>FROM</literal> clause must
489 specify the column names and types that the function will return.
490 (Specifying column names in an alias is actually standard SQL
491 syntax, but specifying column types is a <productname>PostgreSQL</productname>
492 extension.) This allows the system to understand what
493 <literal>*</literal> should expand to, and what <structname>proname</structname>
494 in the <literal>WHERE</literal> clause refers to, in advance of trying
495 to execute the function. At run time, an error will be thrown
496 if the actual query result from the remote database does not
497 have the same number of columns shown in the <literal>FROM</literal> clause.
498 The column names need not match, however, and <function>dblink</function>
499 does not insist on exact type matches either. It will succeed
500 so long as the returned data strings are valid input for the
501 column type declared in the <literal>FROM</literal> clause.
502 </para>
503 </refsect1>
504
505 <refsect1>
506 <title>Notes</title>
507
508 <para>
509 A convenient way to use <function>dblink</function> with predetermined
510 queries is to create a view.
511 This allows the column type information to be buried in the view,
512 instead of having to spell it out in every query. For example,
513
514 <programlisting>
515 CREATE VIEW myremote_pg_proc AS
516 SELECT *
517 FROM dblink('dbname=postgres options=-csearch_path=',
518 'select proname, prosrc from pg_proc')
519 AS t1(proname name, prosrc text);
520
521 SELECT * FROM myremote_pg_proc WHERE proname LIKE 'bytea%';
522 </programlisting></para>
523 </refsect1>
524
525 <refsect1>
526 <title>Examples</title>
527
528 <screen>
529 SELECT * FROM dblink('dbname=postgres options=-csearch_path=',
530 'select proname, prosrc from pg_proc')
531 AS t1(proname name, prosrc text) WHERE proname LIKE 'bytea%';
532 proname | prosrc
533 ------------+------------
534 byteacat | byteacat
535 byteaeq | byteaeq
536 bytealt | bytealt
537 byteale | byteale
538 byteagt | byteagt
539 byteage | byteage
540 byteane | byteane
541 byteacmp | byteacmp
542 bytealike | bytealike
543 byteanlike | byteanlike
544 byteain | byteain
545 byteaout | byteaout
546 (12 rows)
547
548 SELECT dblink_connect('dbname=postgres options=-csearch_path=');
549 dblink_connect
550 ----------------
551 OK
552 (1 row)
553
554 SELECT * FROM dblink('select proname, prosrc from pg_proc')
555 AS t1(proname name, prosrc text) WHERE proname LIKE 'bytea%';
556 proname | prosrc
557 ------------+------------
558 byteacat | byteacat
559 byteaeq | byteaeq
560 bytealt | bytealt
561 byteale | byteale
562 byteagt | byteagt
563 byteage | byteage
564 byteane | byteane
565 byteacmp | byteacmp
566 bytealike | bytealike
567 byteanlike | byteanlike
568 byteain | byteain
569 byteaout | byteaout
570 (12 rows)
571
572 SELECT dblink_connect('myconn', 'dbname=regression options=-csearch_path=');
573 dblink_connect
574 ----------------
575 OK
576 (1 row)
577
578 SELECT * FROM dblink('myconn', 'select proname, prosrc from pg_proc')
579 AS t1(proname name, prosrc text) WHERE proname LIKE 'bytea%';
580 proname | prosrc
581 ------------+------------
582 bytearecv | bytearecv
583 byteasend | byteasend
584 byteale | byteale
585 byteagt | byteagt
586 byteage | byteage
587 byteane | byteane
588 byteacmp | byteacmp
589 bytealike | bytealike
590 byteanlike | byteanlike
591 byteacat | byteacat
592 byteaeq | byteaeq
593 bytealt | bytealt
594 byteain | byteain
595 byteaout | byteaout
596 (14 rows)
597 </screen>
598 </refsect1>
599 </refentry>
600
601 <refentry id="contrib-dblink-exec">
602 <indexterm>
603 <primary>dblink_exec</primary>
604 </indexterm>
605
606 <refmeta>
607 <refentrytitle>dblink_exec</refentrytitle>
608 <manvolnum>3</manvolnum>
609 </refmeta>
610
611 <refnamediv>
612 <refname>dblink_exec</refname>
613 <refpurpose>executes a command in a remote database</refpurpose>
614 </refnamediv>
615
616 <refsynopsisdiv>
617 <synopsis>
618 dblink_exec(text connname, text sql [, bool fail_on_error]) returns text
619 dblink_exec(text connstr, text sql [, bool fail_on_error]) returns text
620 dblink_exec(text sql [, bool fail_on_error]) returns text
621 </synopsis>
622 </refsynopsisdiv>
623
624 <refsect1>
625 <title>Description</title>
626
627 <para>
628 <function>dblink_exec</function> executes a command (that is, any SQL statement
629 that doesn't return rows) in a remote database.
630 </para>
631
632 <para>
633 When two <type>text</type> arguments are given, the first one is first
634 looked up as a persistent connection's name; if found, the command
635 is executed on that connection. If not found, the first argument
636 is treated as a connection info string as for <function>dblink_connect</function>,
637 and the indicated connection is made just for the duration of this command.
638 </para>
639 </refsect1>
640
641 <refsect1>
642 <title>Arguments</title>
643
644 <variablelist>
645 <varlistentry>
646 <term><parameter>connname</parameter></term>
647 <listitem>
648 <para>
649 Name of the connection to use; omit this parameter to use the
650 unnamed connection.
651 </para>
652 </listitem>
653 </varlistentry>
654
655 <varlistentry>
656 <term><parameter>connstr</parameter></term>
657 <listitem>
658 <para>
659 A connection info string, as previously described for
660 <function>dblink_connect</function>.
661 </para>
662 </listitem>
663 </varlistentry>
664
665 <varlistentry>
666 <term><parameter>sql</parameter></term>
667 <listitem>
668 <para>
669 The SQL command that you wish to execute in the remote database,
670 for example
671 <literal>insert into foo values(0, 'a', '{"a0","b0","c0"}')</literal>.
672 </para>
673 </listitem>
674 </varlistentry>
675
676 <varlistentry>
677 <term><parameter>fail_on_error</parameter></term>
678 <listitem>
679 <para>
680 If true (the default when omitted) then an error thrown on the
681 remote side of the connection causes an error to also be thrown
682 locally. If false, the remote error is locally reported as a NOTICE,
683 and the function's return value is set to <literal>ERROR</literal>.
684 </para>
685 </listitem>
686 </varlistentry>
687 </variablelist>
688 </refsect1>
689
690 <refsect1>
691 <title>Return Value</title>
692
693 <para>
694 Returns status, either the command's status string or <literal>ERROR</literal>.
695 </para>
696 </refsect1>
697
698 <refsect1>
699 <title>Examples</title>
700
701 <screen>
702 SELECT dblink_connect('dbname=dblink_test_standby');
703 dblink_connect
704 ----------------
705 OK
706 (1 row)
707
708 SELECT dblink_exec('insert into foo values(21, ''z'', ''{"a0","b0","c0"}'');');
709 dblink_exec
710 -----------------
711 INSERT 943366 1
712 (1 row)
713
714 SELECT dblink_connect('myconn', 'dbname=regression');
715 dblink_connect
716 ----------------
717 OK
718 (1 row)
719
720 SELECT dblink_exec('myconn', 'insert into foo values(21, ''z'', ''{"a0","b0","c0"}'');');
721 dblink_exec
722 ------------------
723 INSERT 6432584 1
724 (1 row)
725
726 SELECT dblink_exec('myconn', 'insert into pg_class values (''foo'')',false);
727 NOTICE: sql error
728 DETAIL: ERROR: null value in column "relnamespace" violates not-null constraint
729
730 dblink_exec
731 -------------
732 ERROR
733 (1 row)
734 </screen>
735 </refsect1>
736 </refentry>
737
738 <refentry id="contrib-dblink-open">
739 <indexterm>
740 <primary>dblink_open</primary>
741 </indexterm>
742
743 <refmeta>
744 <refentrytitle>dblink_open</refentrytitle>
745 <manvolnum>3</manvolnum>
746 </refmeta>
747
748 <refnamediv>
749 <refname>dblink_open</refname>
750 <refpurpose>opens a cursor in a remote database</refpurpose>
751 </refnamediv>
752
753 <refsynopsisdiv>
754 <synopsis>
755 dblink_open(text cursorname, text sql [, bool fail_on_error]) returns text
756 dblink_open(text connname, text cursorname, text sql [, bool fail_on_error]) returns text
757 </synopsis>
758 </refsynopsisdiv>
759
760 <refsect1>
761 <title>Description</title>
762
763 <para>
764 <function>dblink_open()</function> opens a cursor in a remote database.
765 The cursor can subsequently be manipulated with
766 <function>dblink_fetch()</function> and <function>dblink_close()</function>.
767 </para>
768 </refsect1>
769
770 <refsect1>
771 <title>Arguments</title>
772
773 <variablelist>
774 <varlistentry>
775 <term><parameter>connname</parameter></term>
776 <listitem>
777 <para>
778 Name of the connection to use; omit this parameter to use the
779 unnamed connection.
780 </para>
781 </listitem>
782 </varlistentry>
783
784 <varlistentry>
785 <term><parameter>cursorname</parameter></term>
786 <listitem>
787 <para>
788 The name to assign to this cursor.
789 </para>
790 </listitem>
791 </varlistentry>
792
793 <varlistentry>
794 <term><parameter>sql</parameter></term>
795 <listitem>
796 <para>
797 The <command>SELECT</command> statement that you wish to execute in the remote
798 database, for example <literal>select * from pg_class</literal>.
799 </para>
800 </listitem>
801 </varlistentry>
802
803 <varlistentry>
804 <term><parameter>fail_on_error</parameter></term>
805 <listitem>
806 <para>
807 If true (the default when omitted) then an error thrown on the
808 remote side of the connection causes an error to also be thrown
809 locally. If false, the remote error is locally reported as a NOTICE,
810 and the function's return value is set to <literal>ERROR</literal>.
811 </para>
812 </listitem>
813 </varlistentry>
814 </variablelist>
815 </refsect1>
816
817 <refsect1>
818 <title>Return Value</title>
819
820 <para>
821 Returns status, either <literal>OK</literal> or <literal>ERROR</literal>.
822 </para>
823 </refsect1>
824
825 <refsect1>
826 <title>Notes</title>
827
828 <para>
829 Since a cursor can only persist within a transaction,
830 <function>dblink_open</function> starts an explicit transaction block
831 (<command>BEGIN</command>) on the remote side, if the remote side was
832 not already within a transaction. This transaction will be
833 closed again when the matching <function>dblink_close</function> is
834 executed. Note that if
835 you use <function>dblink_exec</function> to change data between
836 <function>dblink_open</function> and <function>dblink_close</function>,
837 and then an error occurs or you use <function>dblink_disconnect</function> before
838 <function>dblink_close</function>, your change <emphasis>will be
839 lost</emphasis> because the transaction will be aborted.
840 </para>
841 </refsect1>
842
843 <refsect1>
844 <title>Examples</title>
845
846 <screen>
847 SELECT dblink_connect('dbname=postgres options=-csearch_path=');
848 dblink_connect
849 ----------------
850 OK
851 (1 row)
852
853 SELECT dblink_open('foo', 'select proname, prosrc from pg_proc');
854 dblink_open
855 -------------
856 OK
857 (1 row)
858 </screen>
859 </refsect1>
860 </refentry>
861
862 <refentry id="contrib-dblink-fetch">
863 <indexterm>
864 <primary>dblink_fetch</primary>
865 </indexterm>
866
867 <refmeta>
868 <refentrytitle>dblink_fetch</refentrytitle>
869 <manvolnum>3</manvolnum>
870 </refmeta>
871
872 <refnamediv>
873 <refname>dblink_fetch</refname>
874 <refpurpose>returns rows from an open cursor in a remote database</refpurpose>
875 </refnamediv>
876
877 <refsynopsisdiv>
878 <synopsis>
879 dblink_fetch(text cursorname, int howmany [, bool fail_on_error]) returns setof record
880 dblink_fetch(text connname, text cursorname, int howmany [, bool fail_on_error]) returns setof record
881 </synopsis>
882 </refsynopsisdiv>
883
884 <refsect1>
885 <title>Description</title>
886
887 <para>
888 <function>dblink_fetch</function> fetches rows from a cursor previously
889 established by <function>dblink_open</function>.
890 </para>
891 </refsect1>
892
893 <refsect1>
894 <title>Arguments</title>
895
896 <variablelist>
897 <varlistentry>
898 <term><parameter>connname</parameter></term>
899 <listitem>
900 <para>
901 Name of the connection to use; omit this parameter to use the
902 unnamed connection.
903 </para>
904 </listitem>
905 </varlistentry>
906
907 <varlistentry>
908 <term><parameter>cursorname</parameter></term>
909 <listitem>
910 <para>
911 The name of the cursor to fetch from.
912 </para>
913 </listitem>
914 </varlistentry>
915
916 <varlistentry>
917 <term><parameter>howmany</parameter></term>
918 <listitem>
919 <para>
920 The maximum number of rows to retrieve. The next <parameter>howmany</parameter>
921 rows are fetched, starting at the current cursor position, moving
922 forward. Once the cursor has reached its end, no more rows are produced.
923 </para>
924 </listitem>
925 </varlistentry>
926
927 <varlistentry>
928 <term><parameter>fail_on_error</parameter></term>
929 <listitem>
930 <para>
931 If true (the default when omitted) then an error thrown on the
932 remote side of the connection causes an error to also be thrown
933 locally. If false, the remote error is locally reported as a NOTICE,
934 and the function returns no rows.
935 </para>
936 </listitem>
937 </varlistentry>
938 </variablelist>
939 </refsect1>
940
941 <refsect1>
942 <title>Return Value</title>
943
944 <para>
945 The function returns the row(s) fetched from the cursor. To use this
946 function, you will need to specify the expected set of columns,
947 as previously discussed for <function>dblink</function>.
948 </para>
949 </refsect1>
950
951 <refsect1>
952 <title>Notes</title>
953
954 <para>
955 On a mismatch between the number of return columns specified in the
956 <literal>FROM</literal> clause, and the actual number of columns returned by the
957 remote cursor, an error will be thrown. In this event, the remote cursor
958 is still advanced by as many rows as it would have been if the error had
959 not occurred. The same is true for any other error occurring in the local
960 query after the remote <command>FETCH</command> has been done.
961 </para>
962 </refsect1>
963
964 <refsect1>
965 <title>Examples</title>
966
967 <screen>
968 SELECT dblink_connect('dbname=postgres options=-csearch_path=');
969 dblink_connect
970 ----------------
971 OK
972 (1 row)
973
974 SELECT dblink_open('foo', 'select proname, prosrc from pg_proc where proname like ''bytea%''');
975 dblink_open
976 -------------
977 OK
978 (1 row)
979
980 SELECT * FROM dblink_fetch('foo', 5) AS (funcname name, source text);
981 funcname | source
982 ----------+----------
983 byteacat | byteacat
984 byteacmp | byteacmp
985 byteaeq | byteaeq
986 byteage | byteage
987 byteagt | byteagt
988 (5 rows)
989
990 SELECT * FROM dblink_fetch('foo', 5) AS (funcname name, source text);
991 funcname | source
992 -----------+-----------
993 byteain | byteain
994 byteale | byteale
995 bytealike | bytealike
996 bytealt | bytealt
997 byteane | byteane
998 (5 rows)
999
1000 SELECT * FROM dblink_fetch('foo', 5) AS (funcname name, source text);
1001 funcname | source
1002 ------------+------------
1003 byteanlike | byteanlike
1004 byteaout | byteaout
1005 (2 rows)
1006
1007 SELECT * FROM dblink_fetch('foo', 5) AS (funcname name, source text);
1008 funcname | source
1009 ----------+--------
1010 (0 rows)
1011 </screen>
1012 </refsect1>
1013 </refentry>
1014
1015 <refentry id="contrib-dblink-close">
1016 <indexterm>
1017 <primary>dblink_close</primary>
1018 </indexterm>
1019
1020 <refmeta>
1021 <refentrytitle>dblink_close</refentrytitle>
1022 <manvolnum>3</manvolnum>
1023 </refmeta>
1024
1025 <refnamediv>
1026 <refname>dblink_close</refname>
1027 <refpurpose>closes a cursor in a remote database</refpurpose>
1028 </refnamediv>
1029
1030 <refsynopsisdiv>
1031 <synopsis>
1032 dblink_close(text cursorname [, bool fail_on_error]) returns text
1033 dblink_close(text connname, text cursorname [, bool fail_on_error]) returns text
1034 </synopsis>
1035 </refsynopsisdiv>
1036
1037 <refsect1>
1038 <title>Description</title>
1039
1040 <para>
1041 <function>dblink_close</function> closes a cursor previously opened with
1042 <function>dblink_open</function>.
1043 </para>
1044 </refsect1>
1045
1046 <refsect1>
1047 <title>Arguments</title>
1048
1049 <variablelist>
1050 <varlistentry>
1051 <term><parameter>connname</parameter></term>
1052 <listitem>
1053 <para>
1054 Name of the connection to use; omit this parameter to use the
1055 unnamed connection.
1056 </para>
1057 </listitem>
1058 </varlistentry>
1059
1060 <varlistentry>
1061 <term><parameter>cursorname</parameter></term>
1062 <listitem>
1063 <para>
1064 The name of the cursor to close.
1065 </para>
1066 </listitem>
1067 </varlistentry>
1068
1069 <varlistentry>
1070 <term><parameter>fail_on_error</parameter></term>
1071 <listitem>
1072 <para>
1073 If true (the default when omitted) then an error thrown on the
1074 remote side of the connection causes an error to also be thrown
1075 locally. If false, the remote error is locally reported as a NOTICE,
1076 and the function's return value is set to <literal>ERROR</literal>.
1077 </para>
1078 </listitem>
1079 </varlistentry>
1080 </variablelist>
1081 </refsect1>
1082
1083 <refsect1>
1084 <title>Return Value</title>
1085
1086 <para>
1087 Returns status, either <literal>OK</literal> or <literal>ERROR</literal>.
1088 </para>
1089 </refsect1>
1090
1091 <refsect1>
1092 <title>Notes</title>
1093
1094 <para>
1095 If <function>dblink_open</function> started an explicit transaction block,
1096 and this is the last remaining open cursor in this connection,
1097 <function>dblink_close</function> will issue the matching <command>COMMIT</command>.
1098 </para>
1099 </refsect1>
1100
1101 <refsect1>
1102 <title>Examples</title>
1103
1104 <screen>
1105 SELECT dblink_connect('dbname=postgres options=-csearch_path=');
1106 dblink_connect
1107 ----------------
1108 OK
1109 (1 row)
1110
1111 SELECT dblink_open('foo', 'select proname, prosrc from pg_proc');
1112 dblink_open
1113 -------------
1114 OK
1115 (1 row)
1116
1117 SELECT dblink_close('foo');
1118 dblink_close
1119 --------------
1120 OK
1121 (1 row)
1122 </screen>
1123 </refsect1>
1124 </refentry>
1125
1126 <refentry id="contrib-dblink-get-connections">
1127 <indexterm>
1128 <primary>dblink_get_connections</primary>
1129 </indexterm>
1130
1131 <refmeta>
1132 <refentrytitle>dblink_get_connections</refentrytitle>
1133 <manvolnum>3</manvolnum>
1134 </refmeta>
1135
1136 <refnamediv>
1137 <refname>dblink_get_connections</refname>
1138 <refpurpose>returns the names of all open named dblink connections</refpurpose>
1139 </refnamediv>
1140
1141 <refsynopsisdiv>
1142 <synopsis>
1143 dblink_get_connections() returns text[]
1144 </synopsis>
1145 </refsynopsisdiv>
1146
1147 <refsect1>
1148 <title>Description</title>
1149
1150 <para>
1151 <function>dblink_get_connections</function> returns an array of the names
1152 of all open named <filename>dblink</filename> connections.
1153 </para>
1154 </refsect1>
1155
1156 <refsect1>
1157 <title>Return Value</title>
1158
1159 <para>Returns a text array of connection names, or NULL if none.</para>
1160 </refsect1>
1161
1162 <refsect1>
1163 <title>Examples</title>
1164
1165 <programlisting>
1166 SELECT dblink_get_connections();
1167 </programlisting>
1168 </refsect1>
1169 </refentry>
1170
1171 <refentry id="contrib-dblink-error-message">
1172 <indexterm>
1173 <primary>dblink_error_message</primary>
1174 </indexterm>
1175
1176 <refmeta>
1177 <refentrytitle>dblink_error_message</refentrytitle>
1178 <manvolnum>3</manvolnum>
1179 </refmeta>
1180
1181 <refnamediv>
1182 <refname>dblink_error_message</refname>
1183 <refpurpose>gets last error message on the named connection</refpurpose>
1184 </refnamediv>
1185
1186 <refsynopsisdiv>
1187 <synopsis>
1188 dblink_error_message(text connname) returns text
1189 </synopsis>
1190 </refsynopsisdiv>
1191
1192 <refsect1>
1193 <title>Description</title>
1194
1195 <para>
1196 <function>dblink_error_message</function> fetches the most recent remote
1197 error message for a given connection.
1198 </para>
1199 </refsect1>
1200
1201 <refsect1>
1202 <title>Arguments</title>
1203
1204 <variablelist>
1205 <varlistentry>
1206 <term><parameter>connname</parameter></term>
1207 <listitem>
1208 <para>
1209 Name of the connection to use.
1210 </para>
1211 </listitem>
1212 </varlistentry>
1213 </variablelist>
1214 </refsect1>
1215
1216 <refsect1>
1217 <title>Return Value</title>
1218
1219 <para>
1220 Returns last error message, or <literal>OK</literal> if there has been
1221 no error in this connection.
1222 </para>
1223 </refsect1>
1224
1225 <refsect1>
1226 <title>Notes</title>
1227
1228 <para>
1229 When asynchronous queries are initiated by
1230 <function>dblink_send_query</function>, the error message associated with
1231 the connection might not get updated until the server's response message
1232 is consumed. This typically means that <function>dblink_is_busy</function>
1233 or <function>dblink_get_result</function> should be called prior to
1234 <function>dblink_error_message</function>, so that any error generated by
1235 the asynchronous query will be visible.
1236 </para>
1237 </refsect1>
1238
1239 <refsect1>
1240 <title>Examples</title>
1241
1242 <programlisting>
1243 SELECT dblink_error_message('dtest1');
1244 </programlisting>
1245 </refsect1>
1246 </refentry>
1247
1248 <refentry id="contrib-dblink-send-query">
1249 <indexterm>
1250 <primary>dblink_send_query</primary>
1251 </indexterm>
1252
1253 <refmeta>
1254 <refentrytitle>dblink_send_query</refentrytitle>
1255 <manvolnum>3</manvolnum>
1256 </refmeta>
1257
1258 <refnamediv>
1259 <refname>dblink_send_query</refname>
1260 <refpurpose>sends an async query to a remote database</refpurpose>
1261 </refnamediv>
1262
1263 <refsynopsisdiv>
1264 <synopsis>
1265 dblink_send_query(text connname, text sql) returns int
1266 </synopsis>
1267 </refsynopsisdiv>
1268
1269 <refsect1>
1270 <title>Description</title>
1271
1272 <para>
1273 <function>dblink_send_query</function> sends a query to be executed
1274 asynchronously, that is, without immediately waiting for the result.
1275 There must not be an async query already in progress on the
1276 connection.
1277 </para>
1278
1279 <para>
1280 After successfully dispatching an async query, completion status
1281 can be checked with <function>dblink_is_busy</function>, and the results
1282 are ultimately collected with <function>dblink_get_result</function>.
1283 It is also possible to attempt to cancel an active async query
1284 using <function>dblink_cancel_query</function>.
1285 </para>
1286 </refsect1>
1287
1288 <refsect1>
1289 <title>Arguments</title>
1290
1291 <variablelist>
1292 <varlistentry>
1293 <term><parameter>connname</parameter></term>
1294 <listitem>
1295 <para>
1296 Name of the connection to use.
1297 </para>
1298 </listitem>
1299 </varlistentry>
1300
1301 <varlistentry>
1302 <term><parameter>sql</parameter></term>
1303 <listitem>
1304 <para>
1305 The SQL statement that you wish to execute in the remote database,
1306 for example <literal>select * from pg_class</literal>.
1307 </para>
1308 </listitem>
1309 </varlistentry>
1310 </variablelist>
1311 </refsect1>
1312
1313 <refsect1>
1314 <title>Return Value</title>
1315
1316 <para>
1317 Returns 1 if the query was successfully dispatched, 0 otherwise.
1318 </para>
1319 </refsect1>
1320
1321 <refsect1>
1322 <title>Examples</title>
1323
1324 <programlisting>
1325 SELECT dblink_send_query('dtest1', 'SELECT * FROM foo WHERE f1 &lt; 3');
1326 </programlisting>
1327 </refsect1>
1328 </refentry>
1329
1330 <refentry id="contrib-dblink-is-busy">
1331 <indexterm>
1332 <primary>dblink_is_busy</primary>
1333 </indexterm>
1334
1335 <refmeta>
1336 <refentrytitle>dblink_is_busy</refentrytitle>
1337 <manvolnum>3</manvolnum>
1338 </refmeta>
1339
1340 <refnamediv>
1341 <refname>dblink_is_busy</refname>
1342 <refpurpose>checks if connection is busy with an async query</refpurpose>
1343 </refnamediv>
1344
1345 <refsynopsisdiv>
1346 <synopsis>
1347 dblink_is_busy(text connname) returns int
1348 </synopsis>
1349 </refsynopsisdiv>
1350
1351 <refsect1>
1352 <title>Description</title>
1353
1354 <para>
1355 <function>dblink_is_busy</function> tests whether an async query is in progress.
1356 </para>
1357 </refsect1>
1358
1359 <refsect1>
1360 <title>Arguments</title>
1361
1362 <variablelist>
1363 <varlistentry>
1364 <term><parameter>connname</parameter></term>
1365 <listitem>
1366 <para>
1367 Name of the connection to check.
1368 </para>
1369 </listitem>
1370 </varlistentry>
1371 </variablelist>
1372 </refsect1>
1373
1374 <refsect1>
1375 <title>Return Value</title>
1376
1377 <para>
1378 Returns 1 if connection is busy, 0 if it is not busy.
1379 If this function returns 0, it is guaranteed that
1380 <function>dblink_get_result</function> will not block.
1381 </para>
1382 </refsect1>
1383
1384 <refsect1>
1385 <title>Examples</title>
1386
1387 <programlisting>
1388 SELECT dblink_is_busy('dtest1');
1389 </programlisting>
1390 </refsect1>
1391 </refentry>
1392
1393 <refentry id="contrib-dblink-get-notify">
1394 <indexterm>
1395 <primary>dblink_get_notify</primary>
1396 </indexterm>
1397
1398 <refmeta>
1399 <refentrytitle>dblink_get_notify</refentrytitle>
1400 <manvolnum>3</manvolnum>
1401 </refmeta>
1402
1403 <refnamediv>
1404 <refname>dblink_get_notify</refname>
1405 <refpurpose>retrieve async notifications on a connection</refpurpose>
1406 </refnamediv>
1407
1408 <refsynopsisdiv>
1409 <synopsis>
1410 dblink_get_notify() returns setof (notify_name text, be_pid int, extra text)
1411 dblink_get_notify(text connname) returns setof (notify_name text, be_pid int, extra text)
1412 </synopsis>
1413 </refsynopsisdiv>
1414
1415 <refsect1>
1416 <title>Description</title>
1417
1418 <para>
1419 <function>dblink_get_notify</function> retrieves notifications on either
1420 the unnamed connection, or on a named connection if specified.
1421 To receive notifications via dblink, <function>LISTEN</function> must
1422 first be issued, using <function>dblink_exec</function>.
1423 For details see <xref linkend="sql-listen"/> and <xref linkend="sql-notify"/>.
1424 </para>
1425
1426 </refsect1>
1427
1428 <refsect1>
1429 <title>Arguments</title>
1430
1431 <variablelist>
1432 <varlistentry>
1433 <term><parameter>connname</parameter></term>
1434 <listitem>
1435 <para>
1436 The name of a named connection to get notifications on.
1437 </para>
1438 </listitem>
1439 </varlistentry>
1440 </variablelist>
1441 </refsect1>
1442
1443 <refsect1>
1444 <title>Return Value</title>
1445 <para>Returns <type>setof (notify_name text, be_pid int, extra text)</type>, or an empty set if none.</para>
1446 </refsect1>
1447
1448 <refsect1>
1449 <title>Examples</title>
1450
1451 <screen>
1452 SELECT dblink_exec('LISTEN virtual');
1453 dblink_exec
1454 -------------
1455 LISTEN
1456 (1 row)
1457
1458 SELECT * FROM dblink_get_notify();
1459 notify_name | be_pid | extra
1460 -------------+--------+-------
1461 (0 rows)
1462
1463 NOTIFY virtual;
1464 NOTIFY
1465
1466 SELECT * FROM dblink_get_notify();
1467 notify_name | be_pid | extra
1468 -------------+--------+-------
1469 virtual | 1229 |
1470 (1 row)
1471 </screen>
1472 </refsect1>
1473 </refentry>
1474
1475 <refentry id="contrib-dblink-get-result">
1476 <indexterm>
1477 <primary>dblink_get_result</primary>
1478 </indexterm>
1479
1480 <refmeta>
1481 <refentrytitle>dblink_get_result</refentrytitle>
1482 <manvolnum>3</manvolnum>
1483 </refmeta>
1484
1485 <refnamediv>
1486 <refname>dblink_get_result</refname>
1487 <refpurpose>gets an async query result</refpurpose>
1488 </refnamediv>
1489
1490 <refsynopsisdiv>
1491 <synopsis>
1492 dblink_get_result(text connname [, bool fail_on_error]) returns setof record
1493 </synopsis>
1494 </refsynopsisdiv>
1495
1496 <refsect1>
1497 <title>Description</title>
1498
1499 <para>
1500 <function>dblink_get_result</function> collects the results of an
1501 asynchronous query previously sent with <function>dblink_send_query</function>.
1502 If the query is not already completed, <function>dblink_get_result</function>
1503 will wait until it is.
1504 </para>
1505 </refsect1>
1506
1507 <refsect1>
1508 <title>Arguments</title>
1509
1510 <variablelist>
1511 <varlistentry>
1512 <term><parameter>connname</parameter></term>
1513 <listitem>
1514 <para>
1515 Name of the connection to use.
1516 </para>
1517 </listitem>
1518 </varlistentry>
1519
1520 <varlistentry>
1521 <term><parameter>fail_on_error</parameter></term>
1522 <listitem>
1523 <para>
1524 If true (the default when omitted) then an error thrown on the
1525 remote side of the connection causes an error to also be thrown
1526 locally. If false, the remote error is locally reported as a NOTICE,
1527 and the function returns no rows.
1528 </para>
1529 </listitem>
1530 </varlistentry>
1531 </variablelist>
1532 </refsect1>
1533
1534 <refsect1>
1535 <title>Return Value</title>
1536
1537 <para>
1538 For an async query (that is, an SQL statement returning rows),
1539 the function returns the row(s) produced by the query. To use this
1540 function, you will need to specify the expected set of columns,
1541 as previously discussed for <function>dblink</function>.
1542 </para>
1543
1544 <para>
1545 For an async command (that is, an SQL statement not returning rows),
1546 the function returns a single row with a single text column containing
1547 the command's status string. It is still necessary to specify that
1548 the result will have a single text column in the calling <literal>FROM</literal>
1549 clause.
1550 </para>
1551 </refsect1>
1552
1553 <refsect1>
1554 <title>Notes</title>
1555
1556 <para>
1557 This function <emphasis>must</emphasis> be called if
1558 <function>dblink_send_query</function> returned 1.
1559 It must be called once for each query
1560 sent, and one additional time to obtain an empty set result,
1561 before the connection can be used again.
1562 </para>
1563
1564 <para>
1565 When using <function>dblink_send_query</function> and
1566 <function>dblink_get_result</function>, <application>dblink</application> fetches the entire
1567 remote query result before returning any of it to the local query
1568 processor. If the query returns a large number of rows, this can result
1569 in transient memory bloat in the local session. It may be better to open
1570 such a query as a cursor with <function>dblink_open</function> and then fetch a
1571 manageable number of rows at a time. Alternatively, use plain
1572 <function>dblink()</function>, which avoids memory bloat by spooling large result
1573 sets to disk.
1574 </para>
1575 </refsect1>
1576
1577 <refsect1>
1578 <title>Examples</title>
1579
1580 <screen>
1581 contrib_regression=# SELECT dblink_connect('dtest1', 'dbname=contrib_regression');
1582 dblink_connect
1583 ----------------
1584 OK
1585 (1 row)
1586
1587 contrib_regression=# SELECT * FROM
1588 contrib_regression-# dblink_send_query('dtest1', 'select * from foo where f1 &lt; 3') AS t1;
1589 t1
1590 ----
1591 1
1592 (1 row)
1593
1594 contrib_regression=# SELECT * FROM dblink_get_result('dtest1') AS t1(f1 int, f2 text, f3 text[]);
1595 f1 | f2 | f3
1596 ----+----+------------
1597 0 | a | {a0,b0,c0}
1598 1 | b | {a1,b1,c1}
1599 2 | c | {a2,b2,c2}
1600 (3 rows)
1601
1602 contrib_regression=# SELECT * FROM dblink_get_result('dtest1') AS t1(f1 int, f2 text, f3 text[]);
1603 f1 | f2 | f3
1604 ----+----+----
1605 (0 rows)
1606
1607 contrib_regression=# SELECT * FROM
1608 contrib_regression-# dblink_send_query('dtest1', 'select * from foo where f1 &lt; 3; select * from foo where f1 &gt; 6') AS t1;
1609 t1
1610 ----
1611 1
1612 (1 row)
1613
1614 contrib_regression=# SELECT * FROM dblink_get_result('dtest1') AS t1(f1 int, f2 text, f3 text[]);
1615 f1 | f2 | f3
1616 ----+----+------------
1617 0 | a | {a0,b0,c0}
1618 1 | b | {a1,b1,c1}
1619 2 | c | {a2,b2,c2}
1620 (3 rows)
1621
1622 contrib_regression=# SELECT * FROM dblink_get_result('dtest1') AS t1(f1 int, f2 text, f3 text[]);
1623 f1 | f2 | f3
1624 ----+----+---------------
1625 7 | h | {a7,b7,c7}
1626 8 | i | {a8,b8,c8}
1627 9 | j | {a9,b9,c9}
1628 10 | k | {a10,b10,c10}
1629 (4 rows)
1630
1631 contrib_regression=# SELECT * FROM dblink_get_result('dtest1') AS t1(f1 int, f2 text, f3 text[]);
1632 f1 | f2 | f3
1633 ----+----+----
1634 (0 rows)
1635 </screen>
1636 </refsect1>
1637 </refentry>
1638
1639 <refentry id="contrib-dblink-cancel-query">
1640 <indexterm>
1641 <primary>dblink_cancel_query</primary>
1642 </indexterm>
1643
1644 <refmeta>
1645 <refentrytitle>dblink_cancel_query</refentrytitle>
1646 <manvolnum>3</manvolnum>
1647 </refmeta>
1648
1649 <refnamediv>
1650 <refname>dblink_cancel_query</refname>
1651 <refpurpose>cancels any active query on the named connection</refpurpose>
1652 </refnamediv>
1653
1654 <refsynopsisdiv>
1655 <synopsis>
1656 dblink_cancel_query(text connname) returns text
1657 </synopsis>
1658 </refsynopsisdiv>
1659
1660 <refsect1>
1661 <title>Description</title>
1662
1663 <para>
1664 <function>dblink_cancel_query</function> attempts to cancel any query that
1665 is in progress on the named connection. Note that this is not
1666 certain to succeed (since, for example, the remote query might
1667 already have finished). A cancel request simply improves the
1668 odds that the query will fail soon. You must still complete the
1669 normal query protocol, for example by calling
1670 <function>dblink_get_result</function>.
1671 </para>
1672 </refsect1>
1673
1674 <refsect1>
1675 <title>Arguments</title>
1676
1677 <variablelist>
1678 <varlistentry>
1679 <term><parameter>connname</parameter></term>
1680 <listitem>
1681 <para>
1682 Name of the connection to use.
1683 </para>
1684 </listitem>
1685 </varlistentry>
1686 </variablelist>
1687 </refsect1>
1688
1689 <refsect1>
1690 <title>Return Value</title>
1691
1692 <para>
1693 Returns <literal>OK</literal> if the cancel request has been sent, or
1694 the text of an error message on failure.
1695 </para>
1696 </refsect1>
1697
1698 <refsect1>
1699 <title>Examples</title>
1700
1701 <programlisting>
1702 SELECT dblink_cancel_query('dtest1');
1703 </programlisting>
1704 </refsect1>
1705 </refentry>
1706
1707 <refentry id="contrib-dblink-get-pkey">
1708 <indexterm>
1709 <primary>dblink_get_pkey</primary>
1710 </indexterm>
1711
1712 <refmeta>
1713 <refentrytitle>dblink_get_pkey</refentrytitle>
1714 <manvolnum>3</manvolnum>
1715 </refmeta>
1716
1717 <refnamediv>
1718 <refname>dblink_get_pkey</refname>
1719 <refpurpose>returns the positions and field names of a relation's
1720 primary key fields
1721 </refpurpose>
1722 </refnamediv>
1723
1724 <refsynopsisdiv>
1725 <synopsis>
1726 dblink_get_pkey(text relname) returns setof dblink_pkey_results
1727 </synopsis>
1728 </refsynopsisdiv>
1729
1730 <refsect1>
1731 <title>Description</title>
1732
1733 <para>
1734 <function>dblink_get_pkey</function> provides information about the primary
1735 key of a relation in the local database. This is sometimes useful
1736 in generating queries to be sent to remote databases.
1737 </para>
1738 </refsect1>
1739
1740 <refsect1>
1741 <title>Arguments</title>
1742
1743 <variablelist>
1744 <varlistentry>
1745 <term><parameter>relname</parameter></term>
1746 <listitem>
1747 <para>
1748 Name of a local relation, for example <literal>foo</literal> or
1749 <literal>myschema.mytab</literal>. Include double quotes if the
1750 name is mixed-case or contains special characters, for
1751 example <literal>"FooBar"</literal>; without quotes, the string
1752 will be folded to lower case.
1753 </para>
1754 </listitem>
1755 </varlistentry>
1756 </variablelist>
1757 </refsect1>
1758
1759 <refsect1>
1760 <title>Return Value</title>
1761
1762 <para>
1763 Returns one row for each primary key field, or no rows if the relation
1764 has no primary key. The result row type is defined as
1765
1766 <programlisting>
1767 CREATE TYPE dblink_pkey_results AS (position int, colname text);
1768 </programlisting>
1769
1770 The <literal>position</literal> column simply runs from 1 to <replaceable>N</replaceable>;
1771 it is the number of the field within the primary key, not the number
1772 within the table's columns.
1773 </para>
1774 </refsect1>
1775
1776 <refsect1>
1777 <title>Examples</title>
1778
1779 <screen>
1780 CREATE TABLE foobar (
1781 f1 int,
1782 f2 int,
1783 f3 int,
1784 PRIMARY KEY (f1, f2, f3)
1785 );
1786 CREATE TABLE
1787
1788 SELECT * FROM dblink_get_pkey('foobar');
1789 position | colname
1790 ----------+---------
1791 1 | f1
1792 2 | f2
1793 3 | f3
1794 (3 rows)
1795 </screen>
1796 </refsect1>
1797 </refentry>
1798
1799 <refentry id="contrib-dblink-build-sql-insert">
1800 <indexterm>
1801 <primary>dblink_build_sql_insert</primary>
1802 </indexterm>
1803
1804 <refmeta>
1805 <refentrytitle>dblink_build_sql_insert</refentrytitle>
1806 <manvolnum>3</manvolnum>
1807 </refmeta>
1808
1809 <refnamediv>
1810 <refname>dblink_build_sql_insert</refname>
1811 <refpurpose>
1812 builds an INSERT statement using a local tuple, replacing the
1813 primary key field values with alternative supplied values
1814 </refpurpose>
1815 </refnamediv>
1816
1817 <refsynopsisdiv>
1818 <synopsis>
1819 dblink_build_sql_insert(text relname,
1820 int2vector primary_key_attnums,
1821 integer num_primary_key_atts,
1822 text[] src_pk_att_vals_array,
1823 text[] tgt_pk_att_vals_array) returns text
1824 </synopsis>
1825 </refsynopsisdiv>
1826
1827 <refsect1>
1828 <title>Description</title>
1829
1830 <para>
1831 <function>dblink_build_sql_insert</function> can be useful in doing selective
1832 replication of a local table to a remote database. It selects a row
1833 from the local table based on primary key, and then builds an SQL
1834 <command>INSERT</command> command that will duplicate that row, but with
1835 the primary key values replaced by the values in the last argument.
1836 (To make an exact copy of the row, just specify the same values for
1837 the last two arguments.)
1838 </para>
1839 </refsect1>
1840
1841 <refsect1>
1842 <title>Arguments</title>
1843
1844 <variablelist>
1845 <varlistentry>
1846 <term><parameter>relname</parameter></term>
1847 <listitem>
1848 <para>
1849 Name of a local relation, for example <literal>foo</literal> or
1850 <literal>myschema.mytab</literal>. Include double quotes if the
1851 name is mixed-case or contains special characters, for
1852 example <literal>"FooBar"</literal>; without quotes, the string
1853 will be folded to lower case.
1854 </para>
1855 </listitem>
1856 </varlistentry>
1857
1858 <varlistentry>
1859 <term><parameter>primary_key_attnums</parameter></term>
1860 <listitem>
1861 <para>
1862 Attribute numbers (1-based) of the primary key fields,
1863 for example <literal>1 2</literal>.
1864 </para>
1865 </listitem>
1866 </varlistentry>
1867
1868 <varlistentry>
1869 <term><parameter>num_primary_key_atts</parameter></term>
1870 <listitem>
1871 <para>
1872 The number of primary key fields.
1873 </para>
1874 </listitem>
1875 </varlistentry>
1876
1877 <varlistentry>
1878 <term><parameter>src_pk_att_vals_array</parameter></term>
1879 <listitem>
1880 <para>
1881 Values of the primary key fields to be used to look up the
1882 local tuple. Each field is represented in text form.
1883 An error is thrown if there is no local row with these
1884 primary key values.
1885 </para>
1886 </listitem>
1887 </varlistentry>
1888
1889 <varlistentry>
1890 <term><parameter>tgt_pk_att_vals_array</parameter></term>
1891 <listitem>
1892 <para>
1893 Values of the primary key fields to be placed in the resulting
1894 <command>INSERT</command> command. Each field is represented in text form.
1895 </para>
1896 </listitem>
1897 </varlistentry>
1898 </variablelist>
1899 </refsect1>
1900
1901 <refsect1>
1902 <title>Return Value</title>
1903
1904 <para>Returns the requested SQL statement as text.</para>
1905 </refsect1>
1906
1907 <refsect1>
1908 <title>Notes</title>
1909
1910 <para>
1911 As of <productname>PostgreSQL</productname> 9.0, the attribute numbers in
1912 <parameter>primary_key_attnums</parameter> are interpreted as logical
1913 column numbers, corresponding to the column's position in
1914 <literal>SELECT * FROM relname</literal>. Previous versions interpreted the
1915 numbers as physical column positions. There is a difference if any
1916 column(s) to the left of the indicated column have been dropped during
1917 the lifetime of the table.
1918 </para>
1919 </refsect1>
1920
1921 <refsect1>
1922 <title>Examples</title>
1923
1924 <screen>
1925 SELECT dblink_build_sql_insert('foo', '1 2', 2, '{"1", "a"}', '{"1", "b''a"}');
1926 dblink_build_sql_insert
1927 --------------------------------------------------
1928 INSERT INTO foo(f1,f2,f3) VALUES('1','b''a','1')
1929 (1 row)
1930 </screen>
1931 </refsect1>
1932 </refentry>
1933
1934 <refentry id="contrib-dblink-build-sql-delete">
1935 <indexterm>
1936 <primary>dblink_build_sql_delete</primary>
1937 </indexterm>
1938
1939 <refmeta>
1940 <refentrytitle>dblink_build_sql_delete</refentrytitle>
1941 <manvolnum>3</manvolnum>
1942 </refmeta>
1943
1944 <refnamediv>
1945 <refname>dblink_build_sql_delete</refname>
1946 <refpurpose>builds a DELETE statement using supplied values for primary
1947 key field values
1948 </refpurpose>
1949 </refnamediv>
1950
1951 <refsynopsisdiv>
1952 <synopsis>
1953 dblink_build_sql_delete(text relname,
1954 int2vector primary_key_attnums,
1955 integer num_primary_key_atts,
1956 text[] tgt_pk_att_vals_array) returns text
1957 </synopsis>
1958 </refsynopsisdiv>
1959
1960 <refsect1>
1961 <title>Description</title>
1962
1963 <para>
1964 <function>dblink_build_sql_delete</function> can be useful in doing selective
1965 replication of a local table to a remote database. It builds an SQL
1966 <command>DELETE</command> command that will delete the row with the given
1967 primary key values.
1968 </para>
1969 </refsect1>
1970
1971 <refsect1>
1972 <title>Arguments</title>
1973
1974 <variablelist>
1975 <varlistentry>
1976 <term><parameter>relname</parameter></term>
1977 <listitem>
1978 <para>
1979 Name of a local relation, for example <literal>foo</literal> or
1980 <literal>myschema.mytab</literal>. Include double quotes if the
1981 name is mixed-case or contains special characters, for
1982 example <literal>"FooBar"</literal>; without quotes, the string
1983 will be folded to lower case.
1984 </para>
1985 </listitem>
1986 </varlistentry>
1987
1988 <varlistentry>
1989 <term><parameter>primary_key_attnums</parameter></term>
1990 <listitem>
1991 <para>
1992 Attribute numbers (1-based) of the primary key fields,
1993 for example <literal>1 2</literal>.
1994 </para>
1995 </listitem>
1996 </varlistentry>
1997
1998 <varlistentry>
1999 <term><parameter>num_primary_key_atts</parameter></term>
2000 <listitem>
2001 <para>
2002 The number of primary key fields.
2003 </para>
2004 </listitem>
2005 </varlistentry>
2006
2007 <varlistentry>
2008 <term><parameter>tgt_pk_att_vals_array</parameter></term>
2009 <listitem>
2010 <para>
2011 Values of the primary key fields to be used in the resulting
2012 <command>DELETE</command> command. Each field is represented in text form.
2013 </para>
2014 </listitem>
2015 </varlistentry>
2016 </variablelist>
2017 </refsect1>
2018
2019 <refsect1>
2020 <title>Return Value</title>
2021
2022 <para>Returns the requested SQL statement as text.</para>
2023 </refsect1>
2024
2025 <refsect1>
2026 <title>Notes</title>
2027
2028 <para>
2029 As of <productname>PostgreSQL</productname> 9.0, the attribute numbers in
2030 <parameter>primary_key_attnums</parameter> are interpreted as logical
2031 column numbers, corresponding to the column's position in
2032 <literal>SELECT * FROM relname</literal>. Previous versions interpreted the
2033 numbers as physical column positions. There is a difference if any
2034 column(s) to the left of the indicated column have been dropped during
2035 the lifetime of the table.
2036 </para>
2037 </refsect1>
2038
2039 <refsect1>
2040 <title>Examples</title>
2041
2042 <screen>
2043 SELECT dblink_build_sql_delete('"MyFoo"', '1 2', 2, '{"1", "b"}');
2044 dblink_build_sql_delete
2045 ---------------------------------------------
2046 DELETE FROM "MyFoo" WHERE f1='1' AND f2='b'
2047 (1 row)
2048 </screen>
2049 </refsect1>
2050 </refentry>
2051
2052 <refentry id="contrib-dblink-build-sql-update">
2053 <indexterm>
2054 <primary>dblink_build_sql_update</primary>
2055 </indexterm>
2056
2057 <refmeta>
2058 <refentrytitle>dblink_build_sql_update</refentrytitle>
2059 <manvolnum>3</manvolnum>
2060 </refmeta>
2061
2062 <refnamediv>
2063 <refname>dblink_build_sql_update</refname>
2064 <refpurpose>builds an UPDATE statement using a local tuple, replacing
2065 the primary key field values with alternative supplied values
2066 </refpurpose>
2067 </refnamediv>
2068
2069 <refsynopsisdiv>
2070 <synopsis>
2071 dblink_build_sql_update(text relname,
2072 int2vector primary_key_attnums,
2073 integer num_primary_key_atts,
2074 text[] src_pk_att_vals_array,
2075 text[] tgt_pk_att_vals_array) returns text
2076 </synopsis>
2077 </refsynopsisdiv>
2078
2079 <refsect1>
2080 <title>Description</title>
2081
2082 <para>
2083 <function>dblink_build_sql_update</function> can be useful in doing selective
2084 replication of a local table to a remote database. It selects a row
2085 from the local table based on primary key, and then builds an SQL
2086 <command>UPDATE</command> command that will duplicate that row, but with
2087 the primary key values replaced by the values in the last argument.
2088 (To make an exact copy of the row, just specify the same values for
2089 the last two arguments.) The <command>UPDATE</command> command always assigns
2090 all fields of the row &mdash; the main difference between this and
2091 <function>dblink_build_sql_insert</function> is that it's assumed that
2092 the target row already exists in the remote table.
2093 </para>
2094 </refsect1>
2095
2096 <refsect1>
2097 <title>Arguments</title>
2098
2099 <variablelist>
2100 <varlistentry>
2101 <term><parameter>relname</parameter></term>
2102 <listitem>
2103 <para>
2104 Name of a local relation, for example <literal>foo</literal> or
2105 <literal>myschema.mytab</literal>. Include double quotes if the
2106 name is mixed-case or contains special characters, for
2107 example <literal>"FooBar"</literal>; without quotes, the string
2108 will be folded to lower case.
2109 </para>
2110 </listitem>
2111 </varlistentry>
2112
2113 <varlistentry>
2114 <term><parameter>primary_key_attnums</parameter></term>
2115 <listitem>
2116 <para>
2117 Attribute numbers (1-based) of the primary key fields,
2118 for example <literal>1 2</literal>.
2119 </para>
2120 </listitem>
2121 </varlistentry>
2122
2123 <varlistentry>
2124 <term><parameter>num_primary_key_atts</parameter></term>
2125 <listitem>
2126 <para>
2127 The number of primary key fields.
2128 </para>
2129 </listitem>
2130 </varlistentry>
2131
2132 <varlistentry>
2133 <term><parameter>src_pk_att_vals_array</parameter></term>
2134 <listitem>
2135 <para>
2136 Values of the primary key fields to be used to look up the
2137 local tuple. Each field is represented in text form.
2138 An error is thrown if there is no local row with these
2139 primary key values.
2140 </para>
2141 </listitem>
2142 </varlistentry>
2143
2144 <varlistentry>
2145 <term><parameter>tgt_pk_att_vals_array</parameter></term>
2146 <listitem>
2147 <para>
2148 Values of the primary key fields to be placed in the resulting
2149 <command>UPDATE</command> command. Each field is represented in text form.
2150 </para>
2151 </listitem>
2152 </varlistentry>
2153 </variablelist>
2154 </refsect1>
2155
2156 <refsect1>
2157 <title>Return Value</title>
2158
2159 <para>Returns the requested SQL statement as text.</para>
2160 </refsect1>
2161
2162 <refsect1>
2163 <title>Notes</title>
2164
2165 <para>
2166 As of <productname>PostgreSQL</productname> 9.0, the attribute numbers in
2167 <parameter>primary_key_attnums</parameter> are interpreted as logical
2168 column numbers, corresponding to the column's position in
2169 <literal>SELECT * FROM relname</literal>. Previous versions interpreted the
2170 numbers as physical column positions. There is a difference if any
2171 column(s) to the left of the indicated column have been dropped during
2172 the lifetime of the table.
2173 </para>
2174 </refsect1>
2175
2176 <refsect1>
2177 <title>Examples</title>
2178
2179 <screen>
2180 SELECT dblink_build_sql_update('foo', '1 2', 2, '{"1", "a"}', '{"1", "b"}');
2181 dblink_build_sql_update
2182 -------------------------------------------------------------
2183 UPDATE foo SET f1='1',f2='b',f3='1' WHERE f1='1' AND f2='b'
2184 (1 row)
2185 </screen>
2186 </refsect1>
2187 </refentry>
2188
2189 </sect1>