完整代码
以下以"collect_time"字段为例,其他字段请自行全局替换
DO $$
DECLARE
table_name TEXT;
index_name TEXT;
BEGIN
-- 遍历 public 模式下的所有表
FOR table_name IN (
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY table_name
)
LOOP
-- 检查表是否包含 collect_time 列
IF EXISTS (
SELECT 1
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = table_name AND column_name = 'collect_time'
) THEN
-- 构建索引名称
index_name := 'idx_' || table_name || '_collect_time';
-- 检查索引是否存在
IF NOT EXISTS (
SELECT 1
FROM pg_indexes
WHERE schemaname = 'public' AND tablename = table_name AND indexname = index_name
) THEN
-- 打印日志
RAISE NOTICE 'Creating index % on table %', index_name, table_name;
-- 创建基于 collect_time 的索引
EXECUTE format('CREATE INDEX %I ON %I (collect_time)', index_name, table_name);
ELSE
-- 打印日志
RAISE NOTICE 'Index % already exists on table %', index_name, table_name;
END IF;
END IF;
END LOOP;
END;
$$ LANGUAGE plpgsql;
如何使用
数据库命令行模式直接执行
代码剖析
遍历 public
模式下的所有表,并检查每个表是否包含 collect_time
列。如果表包含该列,则创建基于 collect_time
列的索引。以下是对代码的详细解释:
-
声明部分 (
DECLARE
):-
table_name TEXT;
:声明一个变量table_name
,用于存储表名。 -
index_name TEXT;
:声明一个变量index_name
,用于存储索引名。
-
-
BEGIN 和 END 之间的主体部分:
-
遍历表 (
FOR
循环):-
FOR table_name IN (...)
:使用FOR
循环遍历public
模式下的所有表。 -
SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' ORDER BY table_name
:从information_schema.tables
视图中选择public
模式下的所有表名,并按表名排序。
-
-
检查表是否包含
collect_time
列:-
IF EXISTS (...)
:检查当前表是否包含collect_time
列。 -
SELECT 1 FROM information_schema.columns WHERE table_schema = 'public' AND table_name = table_name AND column_name = 'collect_time'
:从information_schema.columns
视图中查询当前表是否包含collect_time
列。
-
-
构建索引名称:
-
index_name := 'idx_' || table_name || '_collect_time';
:构建索引名称,格式为idx_<table_name>_collect_time
。
-
-
检查索引是否存在:
-
IF NOT EXISTS (...)
:检查索引是否已经存在。 -
SELECT 1 FROM pg_indexes WHERE schemaname = 'public' AND tablename = table_name AND indexname = index_name
:从pg_indexes
视图中查询索引是否存在。
-
-
打印日志和创建索引:
-
RAISE NOTICE 'Creating index % on table %', index_name, table_name;
:在创建索引之前输出日志信息。 -
EXECUTE format('CREATE INDEX %I ON %I (collect_time)', index_name, table_name);
:使用EXECUTE
语句动态执行创建索引的 SQL 命令。 -
RAISE NOTICE 'Index % already exists on table %', index_name, table_name;
:在索引已经存在时输出日志信息。
-
-