#! /bin/sh #------------------------------------------------------------------------- # # droplang -- # Remove a procedural language from a database # # Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group # Portions Copyright (c) 1994, Regents of the University of California # # $Header: /cvsroot/pgsql/src/bin/scripts/Attic/droplang,v 1.18 2001/09/30 22:17:51 momjian Exp $ # #------------------------------------------------------------------------- CMDNAME=`basename "$0"` PATHNAME=`echo $0 | sed "s,$CMDNAME\$,,"` PSQLOPT= dbname= langname= echo= list= showsql= # Check for echo -n vs echo \c if echo '\c' | grep -s c >/dev/null 2>&1 then ECHO_N="echo -n" ECHO_C="" else ECHO_N="echo" ECHO_C='\c' fi # ---------- # Get options, language name and dbname # ---------- while [ "$#" -gt 0 ] do case "$1" in --help|-\?) usage=t break ;; --list|-l) list=t ;; # options passed on to psql --host|-h) PSQLOPT="$PSQLOPT -h $2" shift;; -h*) PSQLOPT="$PSQLOPT $1" ;; --host=*) PSQLOPT="$PSQLOPT -h "`echo $1 | sed 's/^--host=//'` ;; --port|-p) PSQLOPT="$PSQLOPT -p $2" shift;; -p*) PSQLOPT="$PSQLOPT $1" ;; --port=*) PSQLOPT="$PSQLOPT -p "`echo $1 | sed 's/^--port=//'` ;; --username|-U) PSQLOPT="$PSQLOPT -U $2" shift;; -U*) PSQLOPT="$PSQLOPT $1" ;; --username=*) PSQLOPT="$PSQLOPT -U "`echo $1 | sed 's/^--username=//'` ;; --password|-W) PSQLOPT="$PSQLOPT -W" ;; --dbname|-d) dbname="$2" shift;; -d*) dbname=`echo "$1" | sed 's/^-d//'` ;; --dbname=*) dbname=`echo "$1" | sed 's/^--dbname=//'` ;; --echo|-e) showsql=yes ;; -*) echo "$CMDNAME: invalid option: $1" 1>&2 echo "Try '$CMDNAME --help' for more information." 1>&2 exit 1 ;; *) langname="$1" if [ "$2" ]; then shift dbname="$1" fi ;; esac shift done if [ "$usage" ]; then echo "$CMDNAME removes a procedural language from a database." echo echo "Usage:" echo " $CMDNAME [options] [langname [dbname]]" echo echo "Options:" echo " -h, --host=HOSTNAME Database server host" echo " -p, --port=PORT Database server port" echo " -U, --username=USERNAME Username to connect as" echo " -W, --password Prompt for password" echo " -d, --dbname=DBNAME Database to remove language from" echo " -l, --list Show a list of currently installed languages" echo echo "If 'langname' is not specified, you will be prompted interactively." echo "A database name must be specified." echo echo "Report bugs to ." exit 0 fi if [ "$list" ]; then sqlcmd="SELECT lanname as \"Name\", lanpltrusted as \"Trusted?\" FROM pg_language WHERE lanispl = TRUE" if [ "$showsql" = yes ]; then echo "$sqlcmd" fi ${PATHNAME}psql $PSQLOPT -d "$dbname" -P 'title=Procedural languages' -c "$sqlcmd" exit $? fi # ---------- # Check that we have a database # ---------- if [ -z "$dbname" ]; then echo "$CMDNAME: missing required argument database name" 1>&2 echo "Try '$CMDNAME --help' for help." 1>&2 exit 1 fi # ---------- # If not given on the commandline, ask for the language # ---------- if [ -z "$langname" ]; then $ECHO_N "Language to remove from database $dbname: "$ECHO_C read langname fi PSQL="${PATHNAME}psql -A -t -q $PSQLOPT -d $dbname -c" # ---------- # Make sure the language is installed and find the oid of the handler function # ---------- sqlcmd="SELECT lanplcallfoid FROM pg_language WHERE lanname = '$langname' AND lanispl;" if [ "$showsql" = yes ]; then echo "$sqlcmd" fi lanplcallfoid=`$PSQL "$sqlcmd"` if [ "$?" -ne 0 ]; then echo "$CMDNAME: external error" 1>&2 exit 1 fi if [ -z "$lanplcallfoid" ]; then echo "$CMDNAME: language \"$langname\" is not installed in database $dbname" 1>&2 exit 1 fi # ---------- # Check that there are no functions left defined in that language # ---------- sqlcmd="SELECT COUNT(proname) FROM pg_proc P, pg_language L WHERE P.prolang = L.oid AND L.lanname = '$langname';" if [ "$showsql" = yes ]; then echo "$sqlcmd" fi res=`$PSQL "$sqlcmd"` if [ "$?" -ne 0 ]; then echo "$CMDNAME: external error" 1>&2 exit 1 fi if [ "$res" -ne 0 ]; then echo "$CMDNAME: There are $res functions/trigger procedures declared in language" 1>&2 echo "$langname. Language not removed." 1>&2 exit 1 fi # ---------- # Check that the handler function isn't used by some other language # ---------- sqlcmd="SELECT count(*) FROM pg_language WHERE lanplcallfoid = $lanplcallfoid AND lanname <> '$langname';" if [ "$showsql" = yes ]; then echo "$sqlcmd" fi res=`$PSQL "$sqlcmd"` if [ "$?" -ne 0 ]; then echo "$CMDNAME: external error" 1>&2 exit 1 fi if [ "$res" -eq 0 ]; then keephandler=no else keephandler=yes fi # ---------- # Drop the language # ---------- sqlcmd="DROP LANGUAGE \"$langname\";" if [ "$showsql" = yes ]; then echo "$sqlcmd" fi $PSQL "$sqlcmd" if [ "$?" -ne 0 ]; then echo "$CMDNAME: language removal failed" 1>&2 exit 1 fi # ---------- # Drop the call handler # ---------- if [ "$keephandler" = yes ]; then exit 0 fi sqlcmd="SELECT proname FROM pg_proc WHERE oid = $lanplcallfoid;" if [ "$showsql" = yes ]; then echo "$sqlcmd" fi handler=`$PSQL "$sqlcmd"` if [ "$?" -ne 0 ]; then echo "$CMDNAME: external error" 1>&2 exit 1 fi sqlcmd="DROP FUNCTION \"$handler\" ();" if [ "$showsql" = yes ]; then echo "$sqlcmd" fi $PSQL "$sqlcmd" if [ "$?" -ne 0 ]; then echo "$CMDNAME: language removal failed" 1>&2 exit 1 fi exit 0