PostgreSQL跨2个数据库联接 [英] PostgreSQL join across 2 databases

问题描述

我是PostgreSQL的新手.我在PostgreSQL 9.0中有2个数据库，分别是db1和db2，对于db2，我具有只读访问权限.我想创建一个存储函数，否则可以通过JOIN或嵌套查询轻松实现，这是PostgreSQL在数据库之间无法做到的.

I am new to PostgreSQL. I have 2 databases in PostgreSQL 9.0, db1 and db2, and with db2 I have read only access. I want to create a stored function that would be otherwise easily accomplished with a JOIN or a nested query, something PostgreSQL can't do across databases.

在db1中，我具有table1，可以在其中查询一组外键键，这些键可用于在db2的table2中搜索记录，例如:

In db1, I have table1 where I can query for a set of foreign keys keys that I can use to search for records in a table2 in db2, something like:

SELECT * from db2.table2 WHERE db2.table2.primary_key IN (
    SELECT db1.table1.foreign_key FROM db1.table1 WHERE
        db1.table1.primary_key="whatever");

在Postgres中执行此操作的最佳做​​法是什么?我不能在db2中使用临时表，并且在db2中运行的存储函数中将外键作为参数传递似乎不是一个好的解决方案.

What is the best practice for doing this in Postgres? I can't use a temporary tables in db2, and passing in the foreign keys as a parameter in a stored function running in db2 doesn't seem like a good solution.

注意:键都是VARCHAR(11)

Note: the keys are all VARCHAR(11)

推荐答案

您需要查看 db_link 贡献.

此外，如果您熟悉C，还有一个可爱的功能，称为外部数据包装器.它允许使用纯SQL几乎处理任何源. Twitter示例:

As an aside if you're familiar with C, there also is a cute functionality called foreign data wrappers. It allows to manipulate pretty much any source using plain SQL. Example with Twitter:

SELECT from_user, created_at, text FROM twitter WHERE q = '#postgresql';

这篇关于PostgreSQL跨2个数据库联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！