时间戳

首页 > 解决方案 > 跨不同模式的 Postgres SQL 查询

sql - 跨不同模式的 Postgres SQL 查询

问题描述

我们有多个模式，我想跨模式运行一个简单的计数查询，例如：

SELECT COUNT(col_x) FROM schema1.table WHENRE col_x IS NOT NULL

我看到我能够通过以下方式获得所有模式：

SELECT schema_name FROM information_schema.schemata

所以通过使用：

set search_path to schema1; 
SELECT COUNT(col_x)
FROM table
WHERE col_x is not NULL;

我能够运行 schema1 的查询

问题是 - 是否可以循环运行并将模式名称用作 search_path 的参数并跨所有模式运行查询？或任何其他有效的方法？

标签： sqlpostgresql

解决方案

为此，您将需要一些plpgsql动态 SQL。这是一个用于说明的匿名块：

do language plpgsql
$$
declare
 v_schema_name text;
 table_row_count bigint;
 sysSchema text[] := array['pg_toast','pg_temp_1','pg_toast_temp_1','pg_catalog','public','information_schema'];
 -- other declarations here
begin
 for v_schema_name in SELECT schema_name FROM information_schema.schemata WHERE (schema_name != ALL(sysSchema)) loop
   begin
     execute format('select count(col_x) from %I.t_table', v_schema_name)
     into table_row_count;
     raise notice 'Schema % count %', v_schema_name, table_row_count;
   exception when others then null;  -- t_table may not exists in some schemata
   end
 -- other statements here
 end loop;
end;
$$;

顺便说一句WHERE col_x is not NULL是多余的。

推荐阅读