postgresql 在 Postgres 中使用 dblink 有什么捷径吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7828792/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is there any shortcut for using dblink in Postgres?
提问by Joe
In Postgres, you can link to your other databases using dblink, but the syntax is very verbose. For example you can do:
在 Postgres 中，您可以使用 链接到其他数据库dblink，但语法非常冗长。例如你可以这样做：
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?
有没有办法更快地做到这一点？也许预先定义连接？
I noticed that Postgres has a new create foreign tablefunction for connecting to a MySQL database. It has a simpler syntax than dblink. Could I use that?
我注意到 Postgres 有一个create foreign table连接 MySQL 数据库的新功能。它的语法比dblink. 我可以用那个吗？
回答by Peter Eisentraut
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 8.4 及更高版本中，您可以使用外部数据包装器功能来定义连接参数。然后，您只需在 dblink 命令中引用外部服务器名称。请参阅文档中的示例。
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_fdwwrapper, 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.
在 PostgreSQL 9.1 及更高版本中，您可以使用外部数据包装器功能来定义外部表，从而透明地访问远程数据库，而根本不使用 dblink。为此，您需要获取postgresql_fdw包装器，该包装器尚未包含在任何生产版本中，但您可以在互联网上找到实验代码。实际上，这条路线更像是未来的选择。
回答by Erwin Brandstetter
You can wrap connection parametersin a FOREIGN SERVERobjectlike @Peter explains, but you'd still have to spell out the rest.
您可以将连接参数包装在一个FOREIGN SERVER对象中，例如@Peter Explains，但您仍然需要拼出其余部分。
You can encapsulate everything in a view or function, so you type it only once. Example with a function - Run as superuser:
您可以将所有内容封装在视图或函数中，因此只需键入一次。函数示例 - 以超级用户身份运行：
CREATE OR REPLACE FUNCTION f_lnk_tbl()
RETURNS TABLE(tbl_id int, col1 text, log_ts timestamp) AS
$BODY$
SELECT *
FROM dblink(
'SELECT tbl_id, col1, log_ts
FROM tbl
ORDER BY tbl_id'::text) AS b(
tbl_id int
,col1 text
,log_ts timestamp);
$BODY$ LANGUAGE sql STABLE SECURITY DEFINER;
REVOKE ALL ON FUNCTION f_lnk_tbl() FROM public;
CREATE OR REPLACE FUNCTION f_sync()
RETURNS text AS
$BODY$
SELECT dblink_connect('hostaddr=123.123.123.123 port=5432 dbname=mydb
user=postgres password=*secret*');
INSERT INTO my_local_tbl SELECT * FROM f_lnk_tbl();
-- more tables?
SELECT dblink_disconnect();
$BODY$
LANGUAGE sql VOLATILE SECURITY DEFINER;
REVOKE ALL ON FUNCTION blob.f_dbsync() FROM public;
-- GRANT ....;
