曾经只会增删改查和内置函数的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
精彩链接
发表评论