Postgresql 中文操作指南

dblink

dblink — 在远程数据库中执行查询

dblink — executes a query in a remote database

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

Description

dblink 在远程数据库中执行查询（通常是 SELECT ，但它可以是返回行数的任何 SQL 语句）。

dblink executes a query (usually a SELECT, but it can be any SQL statement that returns rows) in a remote database.

当给定两个 text 参数时，第一个参数首先作为持久连接的名称进行查找；如果找到，该命令便在这种连接上执行。如果未找到，第一个参数将被视为 dblink_connect 的连接信息字符串，并且只是在此命令的持续时间内建立指示的连接。

When two text 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 dblink_connect, and the indicated connection is made just for the duration of this command.

Arguments

  • connname

    • Name of the connection to use; omit this parameter to use the unnamed connection.

  • connstr

    • A connection info string, as previously described for dblink_connect.

  • sql

    • The SQL query that you wish to execute in the remote database, for example select * from foo.

  • fail_on_error

    • 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.

Return Value

该函数返回查询产生的行。因为 dblink 可以与任何查询一起使用，所以声明它返回 record ，而不是指定任何特定的列集。这意味着您必须在调用查询中指定期望的列集——否则 PostgreSQL 不会知道会得到什么。这里有一个示例：

The function returns the row(s) produced by the query. Since dblink can be used with any query, it is declared to return record, rather than specifying any particular set of columns. This means that you must specify the expected set of columns in the calling query — otherwise PostgreSQL would not know what to expect. Here is an example:

SELECT *
    FROM dblink('dbname=mydb options=-csearch_path=',
                'select proname, prosrc from pg_proc')
      AS t1(proname name, prosrc text)
    WHERE proname LIKE 'bytea%';

FROM 子句的“别名”部分必须指定该函数将返回的列名称和类型。（在别名中指定列名称实际上是标准的 SQL 语法，但是指定列类型是 PostgreSQL 的扩展。）这样做能够让系统在尝试执行函数之前了解 * 应该扩展成什么，以及 WHERE 子句中的 proname 对应什么。在运行时，如果来自远程数据库的实际查询结果与 FROM 子句中显示的列数不同，将抛出错误。但是，列名称不必匹配，并且 dblink 也不要求完全匹配类型。只要返回的数据字符串是 FROM 子句中声明的列类型的有效输入，它就会成功。

The “alias” part of the FROM 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 PostgreSQL extension.) This allows the system to understand what * should expand to, and what proname in the WHERE 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 FROM clause. The column names need not match, however, and dblink 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 FROM clause.

Notes

通过使用预先确定的查询，可以创建视图。这允许将列类型信息埋在视图中，而不是在每次查询中都要拼写出来。例如：

A convenient way to use dblink 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,

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%';

Examples

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)