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.5 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.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> dblink_exec</td></tr></table></div></body></html>
|
