sql - 如何从手动分区表中动态选择
问题描述
假设我有这样的租户表;
CREATE TABLE tenants (
name varchar(50)
)
对于每个租户,我都有一个名为 的对应表{tenants.name}_entities
,例如,tenant_a
我将有下表。
CREATE TABLE tenant_a_entities {
id uuid,
last_updated timestamp
}
有没有办法可以创建具有以下结构的查询?(使用创建表语法来显示我在寻找什么)
CREATE TABLE all_tenant_entities {
tenant_name varchar(50),
id uuid,
last_updated timestamp
}
--
我确实知道这是一个奇怪的数据库布局,我正在使用Postgres 中的外国数据来联合外国数据库。
解决方案
您是否考虑过为您的关系设计使用声明式分区?为您的案例列出分区,使用PARTITION BY LIST
...
要回答手头的问题:
您根本不需要tenants
查询表,只需要详细表。你最终会以一种或另一种方式将UNION ALL
它们缝合在一起。
SELECT 'a' AS tenant_name, id, last_updated FROM tenant_a_entities
UNION ALL SELECT 'b', id, last_updated FROM tenant_b_entities
...
您可以动态添加名称,例如:
SELECT tableoid::regclass::text, id, last_updated FROM tenant_a_entities
UNION ALL SELECT tableoid::regclass::text, id, last_updated FROM tenant_a_entities
...
看:
但是在您的情况下(第一个代码示例)动态构建查询时添加常量名称会更便宜- 例如:
SELECT string_agg(format('SELECT %L AS tenant_name, id, last_updated FROM %I'
, split_part(tablename, '_', 2)
, tablename)
, E'\nUNION ALL '
ORDER BY tablename) -- optional order
FROM pg_catalog.pg_tables
WHERE schemaname = 'public' -- actual schema name
AND tablename LIKE 'tenant\_%\_entities';
租户名称不能包含_
,否则您必须执行更多操作。
有关的:
您可以将其包装在自定义函数中以使其完全动态:
CREATE OR REPLACE FUNCTION public.f_all_tenant_entities()
RETURNS TABLE(tenant_name text, id uuid, last_updated timestamp)
LANGUAGE plpgsql AS
$func$
BEGIN
RETURN QUERY EXECUTE
(
SELECT string_agg(format('SELECT %L AS tn, id, last_updated FROM %I'
, split_part(tablename, '_', 2)
, tablename)
, E'\nUNION ALL '
ORDER BY tablename) -- optional order
FROM pg_tables
WHERE schemaname = 'public' -- your schema name here
AND tablename LIKE 'tenant\_%\_entities'
);
END
$func$;
称呼:
SELECT * FROM public.f_all_tenant_entities();
您可以像在 SQL 中的大多数上下文中使用表一样使用此集合返回函数(又名“表函数”)。
有关的:
请注意,RETIRN QUERY
在 Postgres 14 之前不允许并行查询。发行说明:
允许 plpgsql 的 RETURN QUERY 使用并行性执行其查询 (Tom Lane)
推荐阅读
- flutter - 如何从flutter中的构造函数调用有状态类中的方法
- reactjs - 如何在 React 中生成动态 DOM 元素
- laravel - 使用 Laravel 8(和 Jetstream)将自定义数据传递到 login.blade.php
- python - 如何减少 Python 项目的 Pyinstaller 构建时间?
- python - 在 Python 函数中使用变量作为参数
- css-counter - 嵌套在 div 中的标题的 CSS 计数器
- apache-spark-sql - 面向行还是面向列?:Databricks 数据库表
- typescript - 如何使用提供的参数推断函数的返回类型?
- ruby - Ruby:使用多个 return..if 语句有什么后果?
- vb.net - 从datagridview打印它给了我错误信息