DBLink插件

本文介绍了DBLink插件的相关内容。

简介

DBLink插件提供了以函数形式访问远程postgres数据库,它相比于外部表更加灵活,可以调用远程数据库中的函数、存储过程等特殊对象。

说明

如果仅仅是访问远程数据库中的表,还是推荐使用postgres_fdw等外部表,它的性能更加高效。

dblink_connect

打开一个到远程数据库的持久连接。

语法

dblink_connect(text connstr) 返回 text
dblink_connect(text connname, text connstr) 返回 text

描述

  • 创建一个到远程PostgreSQL数据库的连接。需要连接的服务器和数据库通过一个标准的libpq连接串来标识。可以选择将一个名字赋予该连接。多个命名的连接可以被一次打开,但是一次只允许一个未命名连接。连接将会持续直到被关闭或者数据库会话结束。

  • 连接串也可以是一个现存外部服务器的名字。在使用外部服务器时,推荐使用外部数据包装器dblink_fdw

参数说明

参数

说明

connname

连接的名称。如果未设置,将打开一个未命名连接并且替换掉任何现有的未命名连接。

connstr

libpq-风格的连接信息串。例如,channel_name=local dbname=mydb user=postgres password=mypasswd。此外,还可以是一个外部服务器的名字。PolarDB PostgreSQL版(兼容Oracle)不允许指定host、port关键字,请使用channel_name代替。

返回值

返回值为OK(如果出错则直接报错,不会有返回值)。

示例

SELECT dblink_connect('channel_name=localhost dbname=postgres');
 dblink_connect
----------------
 OK
(1 row)

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

-- FOREIGN DATA WRAPPER functionality
-- Note: local connection must require password authentication for this to work properly
--       Otherwise, you will receive the following error from dblink_connect():
--       ERROR:  password is required
--       DETAIL:  Non-superuser cannot connect if the server does not request a password.
--       HINT:  Target server's authentication method must be changed.

CREATE SERVER fdtest FOREIGN DATA WRAPPER dblink_fdw OPTIONS (channel_name 'localhost', dbname 'contrib_regression');

CREATE USER regress_dblink_user WITH PASSWORD 'secret';
CREATE USER MAPPING FOR regress_dblink_user SERVER fdtest OPTIONS (user 'regress_dblink_user', password 'secret');
GRANT USAGE ON FOREIGN SERVER fdtest TO regress_dblink_user;
GRANT SELECT ON TABLE foo TO regress_dblink_user;

\set ORIGINAL_USER :USER
\c - regress_dblink_user
SELECT dblink_connect('myconn', 'fdtest');
 dblink_connect 
----------------
 OK
(1 row)

SELECT * FROM dblink('myconn', 'SELECT * FROM foo') AS t(a int, b text, c text[]);
 a  | b |       c
----+---+---------------
  0 | a | {a0,b0,c0}
  1 | b | {a1,b1,c1}
  2 | c | {a2,b2,c2}
  3 | d | {a3,b3,c3}
  4 | e | {a4,b4,c4}
  5 | f | {a5,b5,c5}
  6 | g | {a6,b6,c6}
  7 | h | {a7,b7,c7}
  8 | i | {a8,b8,c8}
  9 | j | {a9,b9,c9}
 10 | k | {a10,b10,c10}
(11 rows)

\c - :ORIGINAL_USER
REVOKE USAGE ON FOREIGN SERVER fdtest FROM regress_dblink_user;
REVOKE SELECT ON TABLE foo FROM regress_dblink_user;
DROP USER MAPPING FOR regress_dblink_user SERVER fdtest;
DROP USER regress_dblink_user;
DROP SERVER fdtest;

dblink_disconnect

关闭一个到远程数据库的持久连接。

语法

dblink_disconnect() 返回 text
dblink_disconnect(text connname) 返回 text

描述

dblink_disconnect()关闭一个之前dblink_connect()打开的连接。不带参数的形式则关闭一个未命名连接。

参数说明

参数

说明

connname

被关闭的连接的名称。

返回值

返回值为OK(如果出错则直接报错,不会有返回值)。

示例

SELECT dblink_disconnect();
 dblink_disconnect
-------------------
 OK
(1 row)

SELECT dblink_disconnect('myconn');
 dblink_disconnect
-------------------
 OK
(1 row)

dblink

在一个远程数据库中执行一个查询。

语法

dblink(text connname, text sql [, bool fail_on_error]) 返回记录集
dblink(text connstr, text sql [, bool fail_on_error]) 返回记录集
dblink(text sql [, bool fail_on_error]) 返回记录集

描述

  • dblink在一个远程数据库中执行一个查询(通常是一个SELECT,但是也可以是任意返回行的SQL语句)。

  • 当给定两个text参数时,第一个参数被作为一个持久连接的名称进行查找,如果找到,该命令会在该连接上被执行。如果没有找到,第一个参数被视作一个用于dblink_connect的连接信息字符串,并且被指出的连接只是在该命令的持续期间被建立。

参数说明

参数

说明

connname

连接的名称。如果未设置,将使用未命名连接。

connstr

dblink_connect所描述的同一个连接信息字符串。

sql

在远程数据库中执行的SQL查询,例如,select * from foo

fail_on_error

如果为true(默认值),则在连接的远端抛出的一个错误也会导致本地抛出一个错误。如果为false,远程错误只在本地被报告为一个NOTICE,并且该函数不返回行。

返回值

该函数返回查询产生的行。因为dblink能与任何查询一起使用,因此返回record,而不是指定任意特定的列集合。表示您必须指定在调用的查询中所期待的列集合,否则PostgreSQL数据库无法获取结果。示例如下:

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子句的alias部分必须指定函数返回的列名及类型(在一个别名中指定列名实际上是标准SQL语法,但是指定列类型是一种PostgreSQL扩展)。这允许系统在尝试执行该函数之前就理解*将展开成什么,以及WHERE子句中的proname指的什么。在运行时,如果来自远程数据库的实际查询结果和FROM子句中显示的列数不同,将会抛出一个错误。不过,列名不需要匹配,并且dblink并不坚持精确地匹配类型。只要被返回的数据字符串是FROM子句中声明的列类型的合法输入,便会成功查询。

示例

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)

dblink_exec

在一个远程数据库中执行一条命令。

语法

dblink_exec(text connname, text sql [, bool fail_on_error]) returns text
dblink_exec(text connstr, text sql [, bool fail_on_error]) returns text
dblink_exec(text sql [, bool fail_on_error]) returns text

描述

  • dblink_exec在一个远程数据库中执行一条命令(即任何不返回行的SQL语句)。

  • 当给定两个text参数时,第一个参数被作为一个持久连接的名称进行查找,如果找到,该命令会在该连接上被执行。如果没有找到,第一个参数被视作一个用于dblink_connect的连接信息字符串,并且被指出的连接只是在该命令的持续期间被建立。

参数说明

参数

说明

connname

连接的名称。如果未设置,将使用未命名连接。

connstr

dblink_connect所描述的同一个连接信息字符串。

sql

在远程数据库中执行的SQL查询,例如,insert into foo values(0, 'a', '{"a0","b0","c0"}')

fail_on_error

  • 如果为true(默认值),则在连接的远端抛出的一个错误也会导致本地抛出一个错误。

  • 如果为false,远程错误只在本地被报告为一个NOTICE,并且该函数不返回行。

返回值

返回状态,可能是命令的状态字符串或ERROR。

示例

SELECT dblink_connect('dbname=dblink_test_standby');
 dblink_connect
----------------
 OK
(1 row)

SELECT dblink_exec('insert into foo values(21, ''z'', ''{"a0","b0","c0"}'');');
   dblink_exec
-----------------
 INSERT 943366 1
(1 row)

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

SELECT dblink_exec('myconn', 'insert into foo values(21, ''z'', ''{"a0","b0","c0"}'');');
   dblink_exec
------------------
 INSERT 6432584 1
(1 row)

SELECT dblink_exec('myconn', 'insert into pg_class values (''foo'')',false);
NOTICE:  sql error
DETAIL:  ERROR:  null value in column "relnamespace" violates not-null constraint

 dblink_exec
-------------
 ERROR
(1 row)

dblink_open

在一个远程数据库中打开一个游标。

语法

dblink_open(text cursorname, text sql [, bool fail_on_error]) 返回 text
dblink_open(text connname, text cursorname, text sql [, bool fail_on_error]) 返回 text

描述

  • dblink_open()在一个远程数据库中打开一个游标。该游标能够随后使用dblink_fetch()dblink_close()进行操作。

  • 因为一个游标只能在一个事务中持续,如果远端还没有在一个事务中,dblink_open会在远端开始一个显式事务块(BEGIN)。当匹配的dblink_close被执行时,该事务将再次被关闭。

    说明

    如果使用dblink_execdblink_opendblink_close之间改变数据,并且发生了一个错误或者在dblink_close之前使用了dblink_disconnect,您的更改将被丢失,因为事务被中止。

参数说明

参数

说明

connname

连接的名称。如果未设置,将使用未命名连接。

cursorname

游标的名称。

sql

在远程数据库中执行的SELECT查询,例如,select * from pg_class

fail_on_error

  • 如果为true(默认值),则在连接的远端抛出的一个错误也会导致本地抛出一个错误。

  • 如果为false,远程错误只在本地被报告为一个NOTICE,并且该函数不返回行。

返回值

返回状态,OK或者ERROR。

示例

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

SELECT dblink_open('foo', 'select proname, prosrc from pg_proc');
 dblink_open
-------------
 OK
(1 row)

dblink_fetch

从一个远程数据库打开的游标中返回行。

语法

dblink_fetch(text cursorname, int howmany [, bool fail_on_error]) 返回 record 集合
dblink_fetch(text connname, text cursorname, int howmany [, bool fail_on_error]) 返回 record 集合

描述

  • dblink_fetch从一个之前由dblink_open建立的游标中返回行。

  • 当FROM子句中指定的返回列的数量和远程游标返回的实际列数不匹配时,将抛出一个错误。在该事件中,游标仍被前进到错误没有发生时所在的行数。对于远程FETCH完成之后在本地查询中发生的任何其他错误,情况也是一样。

参数说明

参数

说明

connname

连接的名称。如果未设置,将使用未命名连接。

cursorname

取数据的游标名称。

howmany

需要检索的最大行数。从当前游标位置向前的接下来howmany个行会被取出。一旦该游标已经到达了它的末端,将不会产生更多行。

fail_on_error

  • 如果为true(默认值),则在连接的远端抛出的一个错误也会导致本地抛出一个错误。

  • 如果为false,远程错误只在本地被报告为一个NOTICE,并且该函数不返回行。

返回值

该函数返回从游标中取出的行。要使用该函数,您需要指定想要的列集合,例如dblink所述。

示例

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

SELECT dblink_open('foo', 'select proname, prosrc from pg_proc where proname like ''bytea%''');
 dblink_open
-------------
 OK
(1 row)

SELECT * FROM dblink_fetch('foo', 5) AS (funcname name, source text);
 funcname |  source
----------+----------
 byteacat | byteacat
 byteacmp | byteacmp
 byteaeq  | byteaeq
 byteage  | byteage
 byteagt  | byteagt
(5 rows)

SELECT * FROM dblink_fetch('foo', 5) AS (funcname name, source text);
 funcname  |  source
-----------+-----------
 byteain   | byteain
 byteale   | byteale
 bytealike | bytealike
 bytealt   | bytealt
 byteane   | byteane
(5 rows)

SELECT * FROM dblink_fetch('foo', 5) AS (funcname name, source text);
  funcname  |   source
------------+------------
 byteanlike | byteanlike
 byteaout   | byteaout
(2 rows)

SELECT * FROM dblink_fetch('foo', 5) AS (funcname name, source text);
 funcname | source
----------+--------
(0 rows)

dblink_close

关闭一个远程数据库中的游标。

语法

dblink_close(text cursorname [, bool fail_on_error]) 返回 text
dblink_close(text connname, text cursorname [, bool fail_on_error]) 返回 text

描述

  • dblink_close关闭一个之前由dblink_open打开的游标。

  • 如果dblink_open开始了一个显式事务块,并且是该连接中最后一个保持打开的游标,dblink_close将发出匹配的COMMIT

参数说明

参数

说明

connname

连接的名称。如果未设置,将使用未命名连接。

cursorname

需要关闭的游标名称。

fail_on_error

  • 如果为true(默认值),则在连接的远端抛出的一个错误也会导致本地抛出一个错误。

  • 如果为false,远程错误只在本地被报告为一个NOTICE,并且该函数的返回值被设置为ERROR。

返回值

返回状态,OK或者ERROR。

示例

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

SELECT dblink_open('foo', 'select proname, prosrc from pg_proc');
 dblink_open
-------------
 OK
(1 row)

SELECT dblink_close('foo');
 dblink_close
--------------
 OK
(1 row)

dblink_get_connections

返回所有打开的命名dblink连接的名称。

语法

dblink_get_connections() 返回 text[]

描述

dblink_get_connections返回一个数组,其中是所有打开的命名dblink连接的名称。

返回值

返回一个连接名称的文本数组,如果没有则为NULL。

示例

SELECT dblink_get_connections();

dblink_error_message

获取在命名连接上的最后一个错误消息。

语法

dblink_error_message(text connname) 返回 text

描述

dblink_error_message为一个给定连接取得最近的远程错误消息。

参数说明

参数

说明

connname

需要使用的连接名。

返回值

返回最后一个错误消息,如果该连接上没有错误则返回一个空字符串。

示例

SELECT dblink_error_message('dtest1');

dblink_send_query

发送一个异步查询到远程数据库。

语法

dblink_send_query(text connname, text sql) 返回 int

描述

  • dblink_send_query发送一个被异步执行的查询,也就是不需要立即等待结果。在该连接上不能存在正在处理中的异步查询。

  • 在成功发送一个异步查询后,可以使用dblink_is_busy检查完成状态,结果最终由dblink_get_result收集。也可以使用dblink_cancel_query尝试取消一个活动中的异步查询。

参数说明

参数

说明

connname

需要使用的连接名。

sql

在远程数据库中执行的SQL查询,例如,select * from pg_class

返回值

如果查询被成功发送则返回1,否则返回0。

示例

SELECT dblink_send_query('dtest1', 'SELECT * FROM foo WHERE f1 < 3');

dblink_is_busy

检查连接是否正在进行异步查询。

语法

dblink_is_busy(text connname) 返回 int

描述

dblink_is_busy检查连接是否正在进行异步查询。

参数说明

参数

说明

connname

需要检查的连接名。

返回值

如果连接正忙则返回1,否则返回0。如果该函数返回0,dblink_get_result则确保不会阻塞。

示例

SELECT dblink_is_busy('dtest1');

dblink_get_result

得到一个异步查询结果。

语法

dblink_get_result(text connname [, bool fail_on_error]) 返回 record 集合

描述

dblink_get_result收集dblink_send_query发送的异步查询的结果。如果该查询还没有完成,dblink_get_result将等待直到它完成。

参数说明

参数

说明

connname

需要使用的连接名称。

fail_on_error

  • 如果为true(默认值),则在连接的远端抛出的一个错误也会导致本地抛出一个错误。

  • 如果为false,远程错误只在本地被报告为一个NOTICE,并且该函数不返回行。

返回值

  • 对于一个异步查询(也就是一个返回行的SQL语句),该函数返回查询产生的行。要使用这个函数,您需要指定所期待的列集合,例如dblink所述。

  • 对于一个异步命令(也就是一个不返回行的SQL语句),该函数返回一个只有单个文本列的单行,其中包含了该命令的状态字符串。必须在调用的FROM子句中指定结果具有一个单一文本行。

  • 如果dblink_send_query返回1,dblink_get_result函数必须被调用。对每一个已发送的查询都必须调用一次该函数,并且在连接再次使用之前还要多调用一次来得到一个空结果集。

  • 当使用dblink_send_querydblink_get_result时,在将结果集中的任何一行返回给本地查询处理器之前,dblink将取得整个远程查询结果。如果该查询返回大量的行,这可能会导致本地会话中短暂的内存膨胀。最好将这样的一个查询用dblink_open打开成一个游标并且每次取得数量可管理的行。也可以使用简单的dblink(),它会避免缓冲大型结果集到磁盘上导致的内存膨胀。

示例

contrib_regression=# SELECT dblink_connect('dtest1', 'dbname=contrib_regression');
 dblink_connect
----------------
 OK
(1 row)

contrib_regression=# SELECT * FROM
contrib_regression-# dblink_send_query('dtest1', 'select * from foo where f1 < 3') AS t1;
 t1
----
  1
(1 row)

contrib_regression=# SELECT * FROM dblink_get_result('dtest1') AS t1(f1 int, f2 text, f3 text[]);
 f1 | f2 |     f3
----+----+------------
  0 | a  | {a0,b0,c0}
  1 | b  | {a1,b1,c1}
  2 | c  | {a2,b2,c2}
(3 rows)

contrib_regression=# SELECT * FROM dblink_get_result('dtest1') AS t1(f1 int, f2 text, f3 text[]);
 f1 | f2 | f3
----+----+----
(0 rows)

contrib_regression=# SELECT * FROM
contrib_regression-# dblink_send_query('dtest1', 'select * from foo where f1 < 3; select * from foo where f1 > 6') AS t1;
 t1
----
  1
(1 row)

contrib_regression=# SELECT * FROM dblink_get_result('dtest1') AS t1(f1 int, f2 text, f3 text[]);
 f1 | f2 |     f3
----+----+------------
  0 | a  | {a0,b0,c0}
  1 | b  | {a1,b1,c1}
  2 | c  | {a2,b2,c2}
(3 rows)

contrib_regression=# SELECT * FROM dblink_get_result('dtest1') AS t1(f1 int, f2 text, f3 text[]);
 f1 | f2 |      f3
----+----+---------------
  7 | h  | {a7,b7,c7}
  8 | i  | {a8,b8,c8}
  9 | j  | {a9,b9,c9}
 10 | k  | {a10,b10,c10}
(4 rows)

contrib_regression=# SELECT * FROM dblink_get_result('dtest1') AS t1(f1 int, f2 text, f3 text[]);
 f1 | f2 | f3
----+----+----
(0 rows)

dblink_cancel_query

在命名连接上取消任何活动查询。

语法

dblink_cancel_query(text connname) 返回 text

描述

dblink_cancel_query尝试取消命名连接上正在进行的任何查询。这不一定会成功(例如,远程查询可能已经结束)。一个取消请求仅仅提高了该查询将很快失败的机率。您仍必须完成通常的查询协议,例如,通过调用dblink_get_result

参数说明

参数

说明

connname

需要使用的连接名称。

返回值

如果取消请求已经被发送,则返回OK;如果失败,则返回一个错误消息的文本。

示例

SELECT dblink_cancel_query('dtest1');