summaryrefslogtreecommitdiffstats
path: root/doc/src/sgml/html/contrib-dblink-function.html
blob: 418c92198d0605f1b9e7fb22050257317e29cf04 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!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">Up</a></td><th width="60%" align="center">F.12. dblink</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 15.7 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.21.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">
dblink(text connname, text sql [, bool fail_on_error]) returns setof record
dblink(text connstr, text sql [, bool fail_on_error]) returns setof record
dblink(text sql [, bool fail_on_error]) returns setof record
</pre></div><div class="refsect1" id="id-1.11.7.21.8.5"><h2>Description</h2><p>
    <code class="function">dblink</code> executes a query (usually a <code class="command">SELECT</code>,
    but it can be any SQL statement that returns rows) in a remote database.
   </p><p>
    When two <code class="type">text</code> arguments are given, the first one is first
    looked up as a persistent connection's name; if found, the command
    is executed on that connection.  If not found, the first argument
    is treated as a connection info string as for <code class="function">dblink_connect</code>,
    and the indicated connection is made just for the duration of this command.
   </p></div><div class="refsect1" id="id-1.11.7.21.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>
       Name of the connection to use; omit this parameter to use the
       unnamed connection.
      </p></dd><dt><span class="term"><em class="parameter"><code>connstr</code></em></span></dt><dd><p>
       A connection info string, as previously described for
       <code class="function">dblink_connect</code>.
      </p></dd><dt><span class="term"><em class="parameter"><code>sql</code></em></span></dt><dd><p>
       The SQL query that you wish to execute in the remote database,
       for example <code class="literal">select * from foo</code>.
      </p></dd><dt><span class="term"><em class="parameter"><code>fail_on_error</code></em></span></dt><dd><p>
       If true (the default when omitted) then an error thrown on the
       remote side of the connection causes an error to also be thrown
       locally. If false, the remote error is locally reported as a NOTICE,
       and the function returns no rows.
      </p></dd></dl></div></div><div class="refsect1" id="id-1.11.7.21.8.7"><h2>Return Value</h2><p>
    The function returns the row(s) produced by the query.  Since
    <code class="function">dblink</code> can be used with any query, it is declared
    to return <code class="type">record</code>, rather than specifying any particular
    set of columns.  This means that you must specify the expected
    set of columns in the calling query — otherwise
    <span class="productname">PostgreSQL</span> would not know what to expect.
    Here is an example:

</p><pre class="programlisting">
SELECT *
    FROM dblink('dbname=mydb options=-csearch_path=',
                'select proname, prosrc from pg_proc')
      AS t1(proname name, prosrc text)
    WHERE proname LIKE 'bytea%';
</pre><p>

    The <span class="quote"><span class="quote">alias</span></span> part of the <code class="literal">FROM</code> clause must
    specify the column names and types that the function will return.
    (Specifying column names in an alias is actually standard SQL
    syntax, but specifying column types is a <span class="productname">PostgreSQL</span>
    extension.)  This allows the system to understand what
    <code class="literal">*</code> should expand to, and what <code class="structname">proname</code>
    in the <code class="literal">WHERE</code> clause refers to, in advance of trying
    to execute the function.  At run time, an error will be thrown
    if the actual query result from the remote database does not
    have the same number of columns shown in the <code class="literal">FROM</code> clause.
    The column names need not match, however, and <code class="function">dblink</code>
    does not insist on exact type matches either.  It will succeed
    so long as the returned data strings are valid input for the
    column type declared in the <code class="literal">FROM</code> clause.
   </p></div><div class="refsect1" id="id-1.11.7.21.8.8"><h2>Notes</h2><p>
    A convenient way to use <code class="function">dblink</code> with predetermined
    queries is to create a view.
    This allows the column type information to be buried in the view,
    instead of having to spell it out in every query.  For example,

</p><pre class="programlisting">
CREATE VIEW myremote_pg_proc AS
  SELECT *
    FROM dblink('dbname=postgres options=-csearch_path=',
                'select proname, prosrc from pg_proc')
    AS t1(proname name, prosrc text);

SELECT * FROM myremote_pg_proc WHERE proname LIKE 'bytea%';
</pre></div><div class="refsect1" id="id-1.11.7.21.8.9"><h2>Examples</h2><pre class="screen">
SELECT * FROM dblink('dbname=postgres options=-csearch_path=',
                     'select proname, prosrc from pg_proc')
  AS t1(proname name, prosrc text) WHERE proname LIKE 'bytea%';
  proname   |   prosrc
------------+------------
 byteacat   | byteacat
 byteaeq    | byteaeq
 bytealt    | bytealt
 byteale    | byteale
 byteagt    | byteagt
 byteage    | byteage
 byteane    | byteane
 byteacmp   | byteacmp
 bytealike  | bytealike
 byteanlike | byteanlike
 byteain    | byteain
 byteaout   | byteaout
(12 rows)

SELECT dblink_connect('dbname=postgres options=-csearch_path=');
 dblink_connect
----------------
 OK
(1 row)

SELECT * FROM dblink('select proname, prosrc from pg_proc')
  AS t1(proname name, prosrc text) WHERE proname LIKE 'bytea%';
  proname   |   prosrc
------------+------------
 byteacat   | byteacat
 byteaeq    | byteaeq
 bytealt    | bytealt
 byteale    | byteale
 byteagt    | byteagt
 byteage    | byteage
 byteane    | byteane
 byteacmp   | byteacmp
 bytealike  | bytealike
 byteanlike | byteanlike
 byteain    | byteain
 byteaout   | byteaout
(12 rows)

SELECT dblink_connect('myconn', 'dbname=regression options=-csearch_path=');
 dblink_connect
----------------
 OK
(1 row)

SELECT * FROM dblink('myconn', 'select proname, prosrc from pg_proc')
  AS t1(proname name, prosrc text) WHERE proname LIKE 'bytea%';
  proname   |   prosrc
------------+------------
 bytearecv  | bytearecv
 byteasend  | byteasend
 byteale    | byteale
 byteagt    | byteagt
 byteage    | byteage
 byteane    | byteane
 byteacmp   | byteacmp
 bytealike  | bytealike
 byteanlike | byteanlike
 byteacat   | byteacat
 byteaeq    | byteaeq
 bytealt    | bytealt
 byteain    | byteain
 byteaout   | byteaout
(14 rows)
</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">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 15.7 Documentation">Home</a></td><td width="40%" align="right" valign="top"> dblink_exec</td></tr></table></div></body></html>