在Postgres中使用dblink是否有任何快捷方式？ [英] Is there any shortcut for using dblink in Postgres?

问题描述

在Postgres中，可以使用 dblink 链接到其他数据库，但是语法非常冗长。例如，您可以执行以下操作：

In Postgres, you can link to your other databases using dblink, but the syntax is very verbose. For example you can do:

SELECT *
FROM dblink (
    'dbname=name port=1234 host=host user=user password=password',
    'select * from table'
) AS users([insert each column name and its type here]);

有什么方法可以更快地做到这一点？

Is there any way to do this faster? Maybe pre-define the connections?

我注意到Postgres有一个新的 create external table 函数，用于连接到MySQL数据库。它的语法比 dblink 更简单。

I noticed that Postgres has a new create foreign table function for connecting to a MySQL database. It has a simpler syntax than dblink. Could I use that?

推荐答案

在PostgreSQL 8.4和更高版本中，您可以使用外部数据包装器功能来定义连接参数。然后，您只需在dblink命令中引用外部服务器名称即可。请参见文档中的示例。

In PostgreSQL 8.4 and later, you can use the foreign data wrapper functionality to define the connection parameters. Then you'd simply refer to the foreign server name in your dblink commands. See the example in the documentation.

在PostgreSQL 9.1和更高版本中，可以使用外部数据包装器功能来定义外部表，从而透明地访问远程数据库，而无需使用dblink。为此，您需要获取 postgresql_fdw 包装器，该包装器尚未包含在任何生产版本中，但是您可以在互联网上找到实验性代码。实际上，这条路线是将来的选择。

In PostgreSQL 9.1 and later, you can use the foreign data wrapper functionality to define foreign tables and thus access remote databases transparently, without using dblink at all. For that, you'd need to get the postgresql_fdw wrapper, which isn't included in any production release yet, but you can find experimental code in the internet. In practice, this route is more of a future option.

这篇关于在Postgres中使用dblink是否有任何快捷方式？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！