PostgreSQL遍历所有的表并为其创建基于某个字段的索引

 完整代码

以下以"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 列的索引。以下是对代码的详细解释：

1. 声明部分 (DECLARE)：

   • table_name TEXT;：声明一个变量 table_name，用于存储表名。

   • index_name TEXT;：声明一个变量 index_name，用于存储索引名。

2. 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;：在索引已经存在时输出日志信息。