greenplum function 函数 代码获取

写了一个获取 gp 数据库 函数体的脚本，有兴趣的可以拿去看看。

#!/bin/bash

source /usr/local/greenplum-db/greenplum_path.sh

usage(){

  echo ""

  echo "Usage: gpfunction function_name [database_name] "

  echo ""

  exit 1;

}

if [ $# -lt 3 ]

then

if [ x"$1" == x"--help" ]

then

usage

fi

if [ x"$1" == x"--?" ]

then

usage

fi

if [ x"$1" != x"" ]

then

procname="$1"

if [ x"$2" != x"" ]

then

dbname="$2"

else

dbname=$PGDATABASE

fi

else

usage

fi

else

usage

fi

#获取函数名以及相关的参数

sqlstr="select nspname||'.'||proname procname,usename,lanname,prorettype::regtype,proargtypes,proallargtypes,proargmodes,proargnames,proisstrict,proretset,provolatile 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 nspname||'.'||proname=lower('$procname');"

#取出返回值

psql -X -t -c "$sqlstr" -A -F "#@#-#%#!" -d "$dbname"  > /tmp/funarg.txt

n=`cat /tmp/funarg.txt | wc -l`

if [ $n -eq 0 ]

then

echo ""

echo "No such Function $procname Found , Check Your Input !"

echo ""

exit

else

num=1

while [ $num -le $n ]

do

line=`head -n "$num" /tmp/funarg.txt | tail -n 1`

#分解返回值

rusername=`echo $line|awk -F "#@#-#%#!" '{print $2}'`

rlanname=`echo $line|awk -F "#@#-#%#!" '{print $3}'`

retvalue=`echo $line|awk -F "#@#-#%#!" '{print $4}'`

rargtypes=`echo $line|awk -F "#@#-#%#!" '{print $5}'|sed 's/[{}]//g'`

rallargtypes=`echo $line|awk -F "#@#-#%#!" '{print $6}'|sed 's/[{}]//g'`

rargmodes=`echo $line|awk -F "#@#-#%#!" '{print $7}'|sed 's/[{}]//g'`

rargnames=`echo $line|awk -F "#@#-#%#!" '{print $8}'|sed 's/[{}]//g'`

argnum=`echo $rargtypes|awk -F , '{print NF}'`

rstrict=`echo $line|awk -F "#@#-#%#!" '{print $9}'`

rset=`echo $line|awk -F "#@#-#%#!" '{print $10}'`

rvolatile=`echo $line|awk -F "#@#-#%#!" '{print $11}'`

#重组函数的参数串

otype=""

out_type=""

strarg=""

for((i=1;i<=$argnum;i++))

do

typeid=`echo $rargtypes|cut -d ' ' -f $i`

atypeid=`echo $rallargtypes|cut -d , -f $i`

amodes=`echo $rargmodes|cut -d , -f $i`

argnames=`echo $rargnames|cut -d , -f $i`

atypes=`echo $line|awk -F "#@#-#%#!" '{print $5}'`

allatypes=`echo $line|awk -F "#@#-#%#!" '{print $6}'`

if [ x"$atypeid" == x"" ]

then

if [ x"$typeid" == x"" ]

then

atype=""

sqlstr1="select prosrc from pg_catalog.pg_proc a left join pg_catalog.pg_namespace b on a.pronamespace=b.oid where nspname||'.'||proname=lower('$procname') and proargtypes='' and proallargtypes is null"

else

atype=`psql -X -t -c "select $typeid::regtype" -d $dbname `

sqlstr1="select prosrc from pg_catalog.pg_proc a left join pg_catalog.pg_namespace b on a.pronamespace=b.oid where nspname||'.'||proname=lower('$procname') and proargtypes='$atypes' and proallargtypes is null"

fi

otype="${out_type},$atype"

strarg="${strarg},$argnames $atype"

else

if [ x"$typeid" == x"" ]

then

atype2=""

sqlstr1="select prosrc from pg_catalog.pg_proc a left join pg_catalog.pg_namespace b on a.pronamespace=b.oid where nspname||'.'||proname=lower('$procname') and proargtypes='' and proallargtypes='$allatypes'"

else

atype2=`psql -X -t -c "select $typeid::regtype" -d $dbname `

sqlstr1="select prosrc from pg_catalog.pg_proc a left join pg_catalog.pg_namespace b on a.pronamespace=b.oid where nspname||'.'||proname=lower('$procname') and proargtypes='$atypes' and proallargtypes='$allatypes'"

fi

atype1=`psql -X -t -c "select $atypeid::regtype" -d $dbname `

otype="${out_type},$atype2"

if [ "$amodes" == "i" ]

then

strarg="${strarg},IN $argnames $atype1"

elif [ "$amodes" == "o" ]

then

strarg="${strarg},OUT $argnames $atype1"

elif [ "$amodes" == "b" ]

then

strarg="${strarg},INOUT $argnames $atype1"

fi

fi

done

vstrarg=`echo $strarg|cut -c 2-`

out_type=`echo $otype|cut -c 3-`

#输出结果

if [ "$rstrict" == "t" ]

then

str="STRICT"

else

str=""

fi

if [ "$rset" == "t" ]

then

ret="SETOF"

else

ret=""

fi

if [ "$rvolatile" == "i" ]

then

vol="IMMUTABLE"

elif [ "$rvolatile" == "s" ]

then

vol="STABLE"

elif [ "$rvolatile" == "v" ]

then

vol="VOLATILE"

fi

echo ""

echo "                Function \"$procname($out_type)\" "

echo "--------------------------------------------------------------------"

echo "CREATE OR REPLACE FUNCTION ${procname}($vstrarg)"

echo "RETURNS $ret $retvalue AS"

echo "\$BODY\$"

#取出函数体

psql -X -t -c "$sqlstr1" -d "$dbname"|grep -v "^$"|sed '$d'

echo "\$BODY\$"

echo "LANGUAGE $rlanname $vol $str;"

echo "ALTER FUNCTION ${procname}($out_type) OWNER TO $rusername;"

echo ""

num=`expr "$num" + 1`

done

fi