codekingpro/portable-devtools

1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>dblink</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="contrib-dblink-disconnect.html" title="dblink_disconnect" /><link rel="next" href="contrib-dblink-exec.html" title="dblink_exec" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">dblink</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="contrib-dblink-disconnect.html" title="dblink_disconnect">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="dblink.html" title="F.12. dblink — connect to other PostgreSQL databases">Up</a></td><th width="60%" align="center">F.12. dblink — connect to other PostgreSQL databases</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="contrib-dblink-exec.html" title="dblink_exec">Next</a></td></tr></table><hr /></div><div class="refentry" id="CONTRIB-DBLINK-FUNCTION"><div class="titlepage"></div><a id="id-1.11.7.22.8.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">dblink</span></h2><p>dblink — executes a query in a remote database</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis">3dblink(text connname, text sql [, bool fail_on_error]) returns setof record4dblink(text connstr, text sql [, bool fail_on_error]) returns setof record5dblink(text sql [, bool fail_on_error]) returns setof record6</pre></div><div class="refsect1" id="id-1.11.7.22.8.5"><h2>Description</h2><p>7    <code class="function">dblink</code> executes a query (usually a <code class="command">SELECT</code>,8    but it can be any SQL statement that returns rows) in a remote database.9   </p><p>10    When two <code class="type">text</code> arguments are given, the first one is first11    looked up as a persistent connection's name; if found, the command12    is executed on that connection.  If not found, the first argument13    is treated as a connection info string as for <code class="function">dblink_connect</code>,14    and the indicated connection is made just for the duration of this command.15   </p></div><div class="refsect1" id="id-1.11.7.22.8.6"><h2>Arguments</h2><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="parameter"><code>connname</code></em></span></dt><dd><p>16       Name of the connection to use; omit this parameter to use the17       unnamed connection.18      </p></dd><dt><span class="term"><em class="parameter"><code>connstr</code></em></span></dt><dd><p>19       A connection info string, as previously described for20       <code class="function">dblink_connect</code>.21      </p></dd><dt><span class="term"><em class="parameter"><code>sql</code></em></span></dt><dd><p>22       The SQL query that you wish to execute in the remote database,23       for example <code class="literal">select * from foo</code>.24      </p></dd><dt><span class="term"><em class="parameter"><code>fail_on_error</code></em></span></dt><dd><p>25       If true (the default when omitted) then an error thrown on the26       remote side of the connection causes an error to also be thrown27       locally. If false, the remote error is locally reported as a NOTICE,28       and the function returns no rows.29      </p></dd></dl></div></div><div class="refsect1" id="id-1.11.7.22.8.7"><h2>Return Value</h2><p>30    The function returns the row(s) produced by the query.  Since31    <code class="function">dblink</code> can be used with any query, it is declared32    to return <code class="type">record</code>, rather than specifying any particular33    set of columns.  This means that you must specify the expected34    set of columns in the calling query — otherwise35    <span class="productname">PostgreSQL</span> would not know what to expect.36    Here is an example:37 38</p><pre class="programlisting">39SELECT *40    FROM dblink('dbname=mydb options=-csearch_path=',41                'select proname, prosrc from pg_proc')42      AS t1(proname name, prosrc text)43    WHERE proname LIKE 'bytea%';44</pre><p>45 46    The <span class="quote">“<span class="quote">alias</span>”</span> part of the <code class="literal">FROM</code> clause must47    specify the column names and types that the function will return.48    (Specifying column names in an alias is actually standard SQL49    syntax, but specifying column types is a <span class="productname">PostgreSQL</span>50    extension.)  This allows the system to understand what51    <code class="literal">*</code> should expand to, and what <code class="structname">proname</code>52    in the <code class="literal">WHERE</code> clause refers to, in advance of trying53    to execute the function.  At run time, an error will be thrown54    if the actual query result from the remote database does not55    have the same number of columns shown in the <code class="literal">FROM</code> clause.56    The column names need not match, however, and <code class="function">dblink</code>57    does not insist on exact type matches either.  It will succeed58    so long as the returned data strings are valid input for the59    column type declared in the <code class="literal">FROM</code> clause.60   </p></div><div class="refsect1" id="id-1.11.7.22.8.8"><h2>Notes</h2><p>61    A convenient way to use <code class="function">dblink</code> with predetermined62    queries is to create a view.63    This allows the column type information to be buried in the view,64    instead of having to spell it out in every query.  For example,65 66</p><pre class="programlisting">67CREATE VIEW myremote_pg_proc AS68  SELECT *69    FROM dblink('dbname=postgres options=-csearch_path=',70                'select proname, prosrc from pg_proc')71    AS t1(proname name, prosrc text);72 73SELECT * FROM myremote_pg_proc WHERE proname LIKE 'bytea%';74</pre></div><div class="refsect1" id="id-1.11.7.22.8.9"><h2>Examples</h2><pre class="screen">75SELECT * FROM dblink('dbname=postgres options=-csearch_path=',76                     'select proname, prosrc from pg_proc')77  AS t1(proname name, prosrc text) WHERE proname LIKE 'bytea%';78  proname   |   prosrc79------------+------------80 byteacat   | byteacat81 byteaeq    | byteaeq82 bytealt    | bytealt83 byteale    | byteale84 byteagt    | byteagt85 byteage    | byteage86 byteane    | byteane87 byteacmp   | byteacmp88 bytealike  | bytealike89 byteanlike | byteanlike90 byteain    | byteain91 byteaout   | byteaout92(12 rows)93 94SELECT dblink_connect('dbname=postgres options=-csearch_path=');95 dblink_connect96----------------97 OK98(1 row)99 100SELECT * FROM dblink('select proname, prosrc from pg_proc')101  AS t1(proname name, prosrc text) WHERE proname LIKE 'bytea%';102  proname   |   prosrc103------------+------------104 byteacat   | byteacat105 byteaeq    | byteaeq106 bytealt    | bytealt107 byteale    | byteale108 byteagt    | byteagt109 byteage    | byteage110 byteane    | byteane111 byteacmp   | byteacmp112 bytealike  | bytealike113 byteanlike | byteanlike114 byteain    | byteain115 byteaout   | byteaout116(12 rows)117 118SELECT dblink_connect('myconn', 'dbname=regression options=-csearch_path=');119 dblink_connect120----------------121 OK122(1 row)123 124SELECT * FROM dblink('myconn', 'select proname, prosrc from pg_proc')125  AS t1(proname name, prosrc text) WHERE proname LIKE 'bytea%';126  proname   |   prosrc127------------+------------128 bytearecv  | bytearecv129 byteasend  | byteasend130 byteale    | byteale131 byteagt    | byteagt132 byteage    | byteage133 byteane    | byteane134 byteacmp   | byteacmp135 bytealike  | bytealike136 byteanlike | byteanlike137 byteacat   | byteacat138 byteaeq    | byteaeq139 bytealt    | bytealt140 byteain    | byteain141 byteaout   | byteaout142(14 rows)143</pre></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="contrib-dblink-disconnect.html" title="dblink_disconnect">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="dblink.html" title="F.12. dblink — connect to other PostgreSQL databases">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="contrib-dblink-exec.html" title="dblink_exec">Next</a></td></tr><tr><td width="40%" align="left" valign="top">dblink_disconnect </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> dblink_exec</td></tr></table></div></body></html>