批量修改存储过程

需求:有50个高斯存储过程需要做变更,如果单个放到data studio去执行太麻烦,通过脚本来做会更简便

测试:
创建2个存储过程

CREATE OR REPLACE FUNCTION test.test01()
 RETURNS void
 LANGUAGE plpgsql
 NOT FENCED NOT SHIPPABLE
AS $$
DECLARE
BEGIN
 insert into test."ypg_0407" values ('1','one');
END$$
/

CREATE OR REPLACE FUNCTION test.test02()
 RETURNS void
 LANGUAGE plpgsql
 NOT FENCED NOT SHIPPABLE
AS $$
DECLARE
BEGIN
 insert into test."ypg_0407" values ('2','two');
END$$
/

变更这2个存储过程如下:

vi proc1.sql
CREATE OR REPLACE FUNCTION test.test01()
 RETURNS void
 LANGUAGE plpgsql
 NOT FENCED NOT SHIPPABLE
AS $$
DECLARE
BEGIN
 insert into test."ypg_0407" values ('3','three');
END$$

vi proc2.sql
CREATE OR REPLACE FUNCTION test.test02()
 RETURNS void
 LANGUAGE plpgsql
 NOT FENCED NOT SHIPPABLE
AS $$
DECLARE
BEGIN
 insert into test."ypg_0407" values ('4','four');
END$$

vi proc.txt
proc1.sql
proc2.sql

cat batch_modify_proc.sh
#!/bin/bash

********************************************

file_name： batch_modify_proc.sh

Func：批量修改存储过程

Author: ypg

create_date: 2023-09-14

modify_info:

version : V1.0

execution：sh batch_modify_proc.sh proc.txt

********************************************

#加载环境
source ~/.bash_profile
gs_source

cat proc.txt |while read line
do
gsql -d test -p 25308 -U test -W test@123 -f “$line” >> proc.log
done