您现在的位置是：首页 >技术杂谈 >利用PostgreSQL的存储过程为多个库表增加固定字段网站首页技术杂谈

曾经只会增删改查和内置函数的SQL使用，第一次发现原来自定义函数/存储过程还是非常可以提高效率的。

背景需求：
需要为某个schema下的表追加字段。

原始手段：为每个表生成插入字段的SQL，执行，要粘贴好多遍表名 /  手工一个一个表加，很累！！！

存储过程改进（真的救了命了）：

1 定义存储过程

-- 用于声明函数名及需要的参数。需要注意参数格式：(变量名1 变量类型 , 变量名2 变量类型,…)
create or replace function "public"."table_append_fields"("schema_name" varchar,"field_name" varchar,"field_type" varchar)

    -- 声明返回值的类型。注意这里是returns, 两个美元符中间可以填入符合命名规则的任意字符，如$body$ (注意上下保持一致性)
	returns "pg_catalog"."void" as $BODY$
        -- declare表示声明变量，可以声明多个变量，此处声明了字符d
		declare d varchar;

        --  声明了一个数组类型的变量并赋了值，值取自SQL查询不包含某字段的表名信息（避免重复插入表字段）。
		declare pg_tables varchar[] := array(
			select tablename
			from pg_tables
			where schemaname = schema_name
				and tablename not in (select distinct table_name from information_schema.columns where column_name = field_name)
		);

	-- begin表示语句开始
	begin
        -- for循环数组（表名），执行插入表字段SQL
	    foreach d in array pg_tables loop 
	    execute format ( 'ALTER TABLE %I.%I ADD COLUMN %I %I ', schema_name, d, field_name, field_type);

        -- 结束循环
		end loop;

    -- end表示语句结束
	end;

-- 同上方的 $BODY$ 命名一致
$BODY$

  --  声明该存储过程使用的语言。注意不是'sql'
  language plpgsql volatile
  cost 100

2.执行生成存储过程

将上述SQL执行一下，就可以看到定义好的函数啦

3.使用存储过程

执行下方SQL测试

SELECT public.table_append_fields('public','age','int2');

SELECT public.table_append_fields('public','name','varchar');

执行前，表中字段

 执行后，表中字段

举一反三，以后就会定义自定义函数啦！！！

详细的参数类型可以查看教程
PostgreSQL 数据类型 | 菜鸟教程 (runoob.com)https://www.runoob.com/postgresql/postgresql-data-type.html还有些详细的使用方法下面的博客也有说明

PostgreSQL的存储过程及基本使用_postgresql存储过程调用_NMAZMMF的博客-CSDN博客https://blog.csdn.net/mr_door/article/details/102527225