doc/src/sgml/dblink.sgml

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