公司有个需求要导出所有业务数据库中的函数,由于函数个数太多写了一个导出脚本,需要自行修改函数的 namespace,有需要的可以 试试看, 有问题欢迎 提出来一起谈论。
#!/bin/bash
source /usr/local/greenplum-db/greenplum_path.sh
mkdir -p /data2/copy/output
dbname="dp_bidb"
funcon=`psql -X -t -d $dbname -c "select count(*) from pg_proc where pronamespace in (2200,17173,17174,17175,17176,17177,17178,17179)"`
echo funnum:$funcon
outpath="/data2/copy/output"
strdb=""
if [ "$dbname" != "" ]
then
strdb=" -d $dbname "
fi
for ((n=0;n<$funcon;n++))
do
echo $n > /data2/tname_$n
#获取函数名以及相关的参数
sqlstr="select nspname||'.'|| proname procname,usename,lanname,prorettype::regtype,proargtypes,proallargtypes,proargmodes,proargnames \
from pg_catalog.pg_proc a \
left join pg_catalog.pg_namespace b on a.pronamespace=b.oid \
left join pg_catalog.pg_user c on a.proowner=c.usesysid \
left join pg_catalog.pg_language d on a.prolang=d.oid \
where a.pronamespace in (2200,17173,17174,17175,17176,17177,17178,17179) \
order by procname,proargtypes offset $n limit 1"
#取出返回值
line=`psql -X -t -c "$sqlstr" -A -F#@# $strdb`
echo "$line" >> /data2/tname_$n
#分解返回值
procname=`echo $line|awk -F#@# '{print $1}'`
echo $procname
vusername=`echo $line|awk -F#@# '{print $2}'`
vlanname=`echo $line|awk -F#@# '{print $3}'`
vretvalue=`echo $line|awk -F#@# '{print $4}'`
vargtypes=`echo $line|awk -F#@# '{print $5}'|sed 's/[{}]//g'`
vallargtypes=`echo $line|awk -F#@# '{print $6}'|sed 's/[{}]//g'`
vargmodes=`echo $line|awk -F#@# '{print $7}'|sed 's/[{}]//g'`
vargnames=`echo $line|awk -F#@# '{print $8}'|sed 's/[{}]//g'`
#判断参数个数
fnum=`echo $vargnames|awk -F, '{print NF}'`
#echo $strarg
#重组函数的参数串
for ((i=1;i<=$fnum;i++))
do
typeid=`echo $vargtypes|cut -d' ' -f$i`
atypeid=`echo $vallargtypes|cut -d, -f$i`
amodes=`echo $vargmodes|cut -d, -f$i`
argnames=`echo $vargnames|cut -d, -f$i`
if [ "$atypeid" = "" ]
then
atypeid=$typeid
fi
atype=`psql -X -t -c "select $atypeid::regtype" $strdb `
otype="${otype},$atype"
if [ "$amodes" == "i" ]
then
strarg="${strarg},IN $argnames $atype"
elif [ "$amodes" == "o" ]
then
strarg="${strarg},OUT $argnames $atype"
else
strarg="${strarg},$argnames $atype"
fi
done
vstrarg=`echo $strarg|cut -c 2-`
out_type=`echo $otype|cut -c 3-`
typeset -u var
var="$procname"
outfile=/data2/copy/output/$var\($out_type\).sql
if [ "$fnum" -eq 0 ]
then
vstrarg=""
out_type=""
fi
#生成文件
sqlstr="select t.src from ( \
select nspname||'.'|| proname procname,prosrc src \
from pg_catalog.pg_proc a \
left join pg_catalog.pg_namespace b on a.pronamespace=b.oid \
where a.pronamespace in (2200,17173,17174,17175,17176,17177,17178,17179) \
order by procname,proargtypes offset $n limit 1) t"
echo "CREATE OR REPLACE FUNCTION ${procname}($vstrarg)" > $outfile
echo "RETURNS $vretvalue AS " >> $outfile
echo "\$BODY\$" >> $outfile
psql -X -t -c "$sqlstr" $strdb >> $outfile
echo "\$BODY\$" >> $outfile
echo "LANGUAGE $vlanname VOLATILE;" >> $outfile
echo "ALTER FUNCTION ${procname}($out_type) OWNER TO $vusername;" >> $outfile
strarg=""
out_type=""
otype=""
vstrarg=""
#typeid=""
#atypeid=""
#amodes=""
#argnames=""
done