Postgresql 如何一次清掉所有的view, trigger及function

Postgresql有個麻煩的地方，若view, trigger或function綁著table，就無法在table上作新增欄位的動作，更麻煩的是view綁view時，上一層的view要更新就一定要先drop 下一層的view，不像Oracle這麼方便，因此若是上新版本的Database Schema時，需將view,trigger, function清掉，因此寫了一個shell script來作這個動作

filename: dropDBV.sh

usage ()

{

echo "

$0 version 0.9

put the description here

SYNOPSIS

$0 [ --help ] [ -h PGHOST ] [ -p PGPORT ] [ -U PGUSER ] [-d PGDATABASE]

DESCRITPTION

This script use to clean dbaegis History data

for example:

$0 -d cobra

$0 -h ora2 -p 5432 -U postgres -d cobra

[OPTIONS]

-h PGHOST DATABASE server host name or IP address

-p PGPORT DATABASE server port

-U PGUSER DATABASE USER

-d TARGET_DB DATABASE NAME

-v VERBOSE Purge Data use Verbose Mode

"

exit 0

}

#drop view

sqlDropView="

SELECT 'drop view if exists '||pc.relname||' cascade;'

FROM pg_class pc,

pg_namespace pn

WHERE pc.relkind = 'v'

AND pn.nspname = 'public'

AND pn.oid = pc.relnamespace

order by relname

"

#Trigger

sqlDropTrg="

SELECT 'drop trigger if exists '||tgname||' on '||tbl.relname||' cascade;'

from pg_trigger trg, pg_class tbl, pg_proc src, pg_language l,pg_namespace pn

where trg.tgrelid = tbl.oid

AND l.oid = src.prolang

AND pn.nspname = 'public'

AND pn.oid = tbl.relnamespace

AND trg.tgfoid = src.oid

order by tgname

"

#Drop Function

sqlDropFun="

SELECT 'drop function if exists '||

p.proname||'('||

coalesce(case when pt01.typname::varchar is not null then ''||pt01.typname::varchar end,'')||

coalesce(case when pt02.typname::varchar is not null then ','||pt02.typname::varchar end,'')||

coalesce(case when pt03.typname::varchar is not null then ','||pt03.typname::varchar end,'')||

coalesce(case when pt04.typname::varchar is not null then ','||pt04.typname::varchar end,'')||

coalesce(case when pt05.typname::varchar is not null then ','||pt05.typname::varchar end,'')||

coalesce(case when pt06.typname::varchar is not null then ','||pt06.typname::varchar end,'')||

coalesce(case when pt07.typname::varchar is not null then ','||pt07.typname::varchar end,'')||

coalesce(case when pt08.typname::varchar is not null then ','||pt08.typname::varchar end,'')||

coalesce(case when pt09.typname::varchar is not null then ','||pt09.typname::varchar end,'')||

coalesce(case when pt0a.typname::varchar is not null then ','||pt0a.typname::varchar end,'')||

') cascade;' as args

FROM pg_catalog.PG_PROC p

LEFT JOIN pg_type pt01 on pt01.oid = p.proargtypes[0]

LEFT JOIN pg_type pt02 on pt02.oid = p.proargtypes[1]

LEFT JOIN pg_type pt03 on pt03.oid = p.proargtypes[2]

LEFT JOIN pg_type pt04 on pt04.oid = p.proargtypes[3]

LEFT JOIN pg_type pt05 on pt05.oid = p.proargtypes[4]

LEFT JOIN pg_type pt06 on pt06.oid = p.proargtypes[5]

LEFT JOIN pg_type pt07 on pt07.oid = p.proargtypes[6]

LEFT JOIN pg_type pt08 on pt08.oid = p.proargtypes[7]

LEFT JOIN pg_type pt09 on pt09.oid = p.proargtypes[8]

LEFT JOIN pg_type pt0a on pt0a.oid = p.proargtypes[9]

LEFT JOIN PG_LANGUAGE l ON l.OID = p.prolang ,

pg_namespace pn

WHERE NOT p.proisagg

AND pg_catalog.pg_function_is_visible(p.OID)

AND pn.nspname = 'public'

AND pn.oid = p.pronamespace

-- and p.proname = 'dbaegis_string_apart_id'

AND l.lanname = 'plpgsql'

order by p.proname

"

run_f ()

{

cmdDropView=`psql -t -q ${cmd} -c "${sqlDropView}"`

echo "${cmdDropView}"

psql ${cmd} -c "${cmdDropView}"

cmdDropFun=`psql -t -q ${cmd} -c "${sqlDropFun}"`

psql ${cmd} -c "${cmdDropFun}"

cmdDropTrg=`psql -t -q ${cmd} -c "${sqlDropTrg}"`

psql ${cmd} -c "${cmdDropTrg}"

}

# Start the Program

PGCLIENTENCODING="UTF8"

export PGCLIENTENCODING

while [ ! "$*" = "" ]

do

case $1 in

--help) usage|more;exit 0;;

-h)shift ;PGHOST=$1;;

-p)shift ;PGPORT=$1;;

-U)shift ;PGUSER=$1;;

-v)VERBOSE=Y;;

-d)shift ;PGDATABASE=$1;;

esac

shift

done

if [ x"${PGHOST}" != "x" ] && [ x"${PGPORT}" != "x" ] && [ x"${PGUSER}" != "x" ] && [ x"${PGDATABASE}" != "x" ];

then

cmd=" -h ${PGHOST} -U ${PGUSER} -p ${PGPORT} "

run_f

else

usage

exit 1

fi