postgresql ：未实现跨数据库引用：
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/51784903/
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
postgresql : cross-database references are not implemented:
提问by SpringUser
I am trying to convert SQL inner join query into PostgreSQL inner join query. In this inner join query which tables are using that all tables are not present in one database. we separated tables into two databases i.e. application db and security db
我正在尝试将 SQL 内连接查询转换为 PostgreSQL 内连接查询。在这个内部连接查询中，哪些表正在使用，但所有表都不存在于一个数据库中。我们将表分成两个数据库，即应用程序数据库和安全数据库
- users and permission table are present in security db
- userrolemapping and department are present in application db
- 用户和权限表存在于安全数据库中
- 用户角色映射和部门存在于应用程序数据库中
I tried like below but I am getting following error
我尝试如下，但出现以下错误
Error
错误
ERROR: cross-database references are not implemented: "Rockefeller_ApplicationDb.public.userrolemapping"
LINE 4: INNER JOIN "Rockefeller_ApplicationDb".public.userro..
SQL Stored Function
SQL 存储函数
SELECT Department.nDeptID
FROM Users INNER JOIN Permission
ON Users.nUserID = Permission.nUserID INNER JOIN UserRoleMapping
ON Users.nUserID = UserRoleMapping.nUserID INNER JOIN Department
ON Permission.nDeptInst = Department.nInstID
AND Department.nInstID = 60
WHERE
Users.nUserID = 3;
PostgreSQL Stored Function
PostgreSQL 存储函数
SELECT dep.ndept_id
FROM "Rockefeller_SecurityDb".public.users as u
INNER JOIN "Rockefeller_SecurityDb".public.permissions p ON u.nuser_id = p.nuser_id
INNER JOIN "Rockefeller_ApplicationDb".public.userrolemapping as urm ON u.nuser_id = urm.nuser_id
INNER JOIN "Rockefeller_ApplicationDb".public.department dep ON p.ndept_inst = dep.ninst_id
AND dep.ninst_id = 60
WHERE
u.nuser_id = 3;
回答by Laurenz Albe
You cannot join tables from different databases.
您不能连接来自不同数据库的表。
Databases are logically separated in PostgreSQL by design.
数据库在 PostgreSQL 中按设计在逻辑上是分开的。
If you want to join the tables, you should put them into different schemas in one database rather than into different databases.
如果要连接表，则应将它们放入一个数据库中的不同模式中，而不是放入不同的数据库中。
Note that what is called “database” in MySQL is called a “schema” in standard SQL.
请注意，在 MySQL 中称为“数据库”的内容在标准 SQL 中称为“模式”。
If you really need to join tables from different databases, you need to use a foreign data wrapper.
如果确实需要连接来自不同数据库的表，则需要使用外部数据包装器。
