PostgreSQL 批量创建序列并使用序列

您所在的位置：网站首页 › pg数据库存储过程写法 › PostgreSQL 批量创建序列并使用序列

PostgreSQL 批量创建序列并使用序列

2024-01-30 13:29| 来源: 网络整理| 查看: 265

迁移生产数据库过程中发现问题，PG多张表公用一个序列，实际上如果表中数据每日变动量较小的话理论上是可行的。

但是如果太大就考虑需要单独为每张表建立一个序列，并使其id自增。

不过确实没有mysql方便可以直接设置“自增主键”，每次创建序列确实麻烦。

于是...

批量创建序列

注意执行用户权限没有权限可能会查不到数据

- -如果需要按表名动态更改序列名称，可以使用动态SQL语句中的变量来构造序列名。以下是一个示例，用于为数据库中的每个表创建名为"table_name_id_seq"的序列，其中"table_name"是表名：

-- 在这个示例中，我们使用了pg_tables元数据表来获取所有test架构下的表名。你可以根据需要修改条件。

-- 执行此代码段将为数据库中的每个表创建一个名为"table_name_id_seq"的序列，其

中"table_name"是表名， table_schema 表示你的模式名。

DO $$DECLARE table_name varchar(255); sql_statement varchar(1000); BEGIN FOR table_name IN SELECT tablename FROM pg_tables WHERE schemaname='test' LOOP sql_statement := 'CREATE SEQUENCE ' || table_name || '_id_seq START 1 INCREMENT 1'; EXECUTE sql_statement; END LOOP; END$$; 当然，这种做法适用于都是新表的情况下，一般我们的表中都会存在数据，所以我们需要保证我们的新序列的起始值是我们表中的最大值，这样才能保证后续的id不重复。

我们使用EXECUTE语句动态构建了查询最大ID或AutoID值的SQL语句，并使用EXECUTE语句执行该SQL语句，将查询结果存储到'max_id'变量中。这样，就可以正确解析'table_record.table_name'关系

DO $$ DECLARE table_record record; max_id bigint; sql_statement varchar(1000); sequence_name varchar(100); BEGIN FOR table_record IN SELECT table_name, column_name FROM information_schema.columns WHERE table_schema='test' AND (column_name = 'autoid' OR column_name = 'id') LOOP sequence_name := table_record.table_name || '_id_seq'; IF NOT EXISTS (SELECT 1 FROM pg_class WHERE relname = sequence_name ) THEN EXECUTE 'SELECT COALESCE(MAX(' || table_record.column_name || '), 0) FROM ' || table_record.table_name INTO max_id; sql_statement := 'CREATE SEQUENCE ' || sequence_name || ' START ' || max_id + 1 || ' INCREMENT 1'; EXECUTE sql_statement; END IF; END LOOP; END$$; 批量使用序列

-- 要为每个表的id或auto id列使用对应的序列，可以使用以下示例代码：

-- 在这个示例中，我们使用了information_schema.columns元数据表来获取所有具有'id'或'auto_id'列的表名和列名。你可以根据需要修改条件。

-- 执行此代码段将为每个具有'id'或'auto_id'列的表创建一个名为"table_name_id_seq"的序列，如果存在对应关系，执行跳过。并将每个表的id或auto id列的默认值设置为该序列的下一个值。

DO $$ DECLARE table_rec RECORD; tablename varchar(255); col_name varchar(255); sql_statement varchar(1000); bool_result BOOLEAN; BEGIN FOR table_rec IN (SELECT table_name, column_name FROM information_schema.columns WHERE table_schema='test' AND (column_name = 'autoid' OR column_name = 'id')) LOOP tablename := table_rec.table_name; col_name := table_rec.column_name; sql_statement := 'SELECT EXISTS(SELECT 1 FROM information_schema.sequences WHERE sequence_name = ''' || tablename || '_id_seq'' and sequence_schema = ''iotms'')'; EXECUTE sql_statement INTO bool_result; IF bool_result THEN sql_statement := 'ALTER TABLE ' || tablename || ' ALTER COLUMN ' || col_name || ' SET DEFAULT nextval(''' || tablename || '_id_seq''::regclass)'; EXECUTE sql_statement; END IF; END LOOP; END$$;

辅助sql，可以用来验证具体某一序列

-- 授权所有序列给指定用户 GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA iotms TO user_iotms -- 查看序列有哪些表使用 SELECT * from information_schema.sequences WHERE sequence_name = 'devicegroup' -- 查询出所有表 SELECT TABLENAME from pg_tables WHERE SCHEMANAME = 'test' -- 查询该模式表下的主键列名称 SELECT table_name, column_name FROM information_schema.columns WHERE table_schema='test' AND (column_name = 'autoid' OR column_name = 'id') -- 查询每张表下的主键最大值 SELECT COALESCE(MAX( autoid), 0) as maxid FROM yzz_iot_device_branch_20230316 -- 查询序列的当前最大值 select LAST_VALUE from dp_dict_id_seq

了解一下这种写法：

这种写法是一种叫做PL/pgSQL的编程语言，它是PostgreSQL内置的过程化语言。而这段代码则是一个匿名代码块，也叫做"匿名存储过程"，它包含了一些SQL语句和PL/pgSQL代码，可以被直接执行。因此，我们可以将其看作是一种存储过程的变体。

在这段代码中，我们使用了DECLARE关键字来声明变量，用于存储查询元数据表后的结果。然后，我们使用FOR循环遍历查询结果，并对每个表的'id'或'auto_id'列执行ALTER TABLE语句，以将其默认值设置为该列对应序列的下一个值。在代码块的末尾，我们使用END关键字来结束代码块。

需要注意的是，由于这段代码是一个匿名代码块，它不会在数据库中创建任何对象。因此，它只适用于一次性的、临时性的任务，而不能像存储过程一样被重复调用。

总之，这段代码使用了PL/pgSQL的语法结构，它类似于存储过程，但并不是一个真正的存储过程。

【本文地址】

PostgreSQL 批量创建序列并使用序列

PostgreSQL 批量创建序列并使用序列

今日新闻

推荐新闻