postgresql - postgres subselect 使用字段值作为模式名称

Question

有什么方法可以从模式名称取自主选择中字段之一的值的模式进行子选择？有点像这样:

create schema a;
create table a.foo(id int);
create schema b;
create table b.foo(id int);

create table public.schemalist (schemaname text);
insert into schemalist values ('a'),('b');

select 
schemaname,
(select count(*) from  schemaname.foo)
from public.schemalist;

但以某种方式告诉 Postgres 在第二列的 schemaname 模式中计算 foo 表...？

Answer 1

您正在尝试根据模型中存储的数据创建 SQL 语句。我相信实现这一目标的唯一方法是使用 PL/pgSQL 函数并在内部构建(和执行)动态语句。

不过，我会仔细检查设计。我自己曾经做过几次类似的事情，但在每种情况下最好的解决方案都是摆脱这个功能。

---

编辑 请注意，使用针对每个客户的模式设计并不是一个坏习惯，我本人提倡这种方法。我的观点是——不可能使用目录详细信息来指定在单个语句中查询哪些表。 SQL 是声明式的，即 DBMS 将执行您在查询中明确提到的操作。

解决问题的几种方法:

1. 作为服务注册商，为您创建专用架构，并创建和维护一系列 View ，这些 View 将跨越所有客户的架构表。比如，如果您有兴趣查看订单，请继续:

   CREATE VIEW registrar.all_orders AS
   SELECT * FROM cust1.orders
   UNION ALL
   SELECT * FROM cust2.orders
   UNION ALL ...
   

   为每个感兴趣的表创建并维护一个专用 View 。我所说的“维护”是指将新客户引入系统的过程还应更新所有注册商 View 。

2. 在注册服务商的架构中创建一个函数以再次动态生成您感兴趣的查询。这可能很棘手，因为您将无法创建这样一个真正通用的函数:

   • 您必须像 RETURNS TABLE 一样定义它并显式定义列，
   • 或者您可以将其定义为 RETURNS SETOF record，但在这种情况下您将被迫提供调用函数时的列规范。

这两者都为您提供了引用数据库中单个对象(使用已知标识符)并通过此抽象获取相关值列表的方法。

如果您的客户端(模式)数量稳定，我会选择 #1，如果您经常添加和/或删除客户端，我会选择 #2。