summaryrefslogtreecommitdiffstats
path: root/doc/src/sgml/html/contrib-dblink-function.html
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 12:15:05 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 12:15:05 +0000
commit46651ce6fe013220ed397add242004d764fc0153 (patch)
tree6e5299f990f88e60174a1d3ae6e48eedd2688b2b /doc/src/sgml/html/contrib-dblink-function.html
parentInitial commit. (diff)
downloadpostgresql-14-upstream.tar.xz
postgresql-14-upstream.zip
Adding upstream version 14.5.upstream/14.5upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'doc/src/sgml/html/contrib-dblink-function.html')
-rw-r--r--doc/src/sgml/html/contrib-dblink-function.html143
1 files changed, 143 insertions, 0 deletions
diff --git a/doc/src/sgml/html/contrib-dblink-function.html b/doc/src/sgml/html/contrib-dblink-function.html
new file mode 100644
index 0000000..2bc6b6f
--- /dev/null
+++ b/doc/src/sgml/html/contrib-dblink-function.html
@@ -0,0 +1,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 xmlns="http://www.w3.org/TR/xhtml1/transitional" 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.10. dblink">Up</a></td><th width="60%" align="center">F.10. dblink</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 14.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></hr></div><div class="refentry" id="CONTRIB-DBLINK-FUNCTION"><div class="titlepage"></div><a id="id-1.11.7.19.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.19.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.19.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.19.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.19.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.19.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 xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navfooter"><hr></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.10. 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 14.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> dblink_exec</td></tr></table></div></body></html> \ No newline at end of file
generated by cgit v1.2.3 (git 2.39.1) at 2024-12-15 10:08:49 +0000