SQLCLI_CNF=

pgsql_atexit() {
  [ -z "$SQLCLI_CNF" ] || rm -f "$SQLCLI_CNF" >/dev/null
}

postgres_client() {
   su -l -c "psql -q -t -v 'ON_ERROR_STOP=1' -P 'format=unaligned' -P 'footer=off' -P 'title' ${psql_extra_opt:-} $*" postgres
}

non_postgres_client() {
   env PGPASSFILE="$SQLCLI_CNF" psql -q -t -v 'ON_ERROR_STOP=1' -P 'format=unaligned' -P 'footer=off' -P 'title' ${psql_extra_opt:-} $*
}

pgsql_safe_db_name() {
   echo "\"$1\""
}

pgsql_prepare_client() {
   local _host _port _admin _admpass remoteserver
   _host="$1"
   _port="$2"
   _admin="$3"
   _admpass="$4"

   which psql >/dev/null || return 1

   if [ -z "$_host" ]; then
      remoteserver='no'
   elif [ "$_host" = 'localhost' -o "$_host" = '127.0.0.1' -o "$_host" = '::1' ]; then
      remoteserver='no'
   else
      remoteserver='yes'
   fi

   if [ "$remoteserver" = 'yes' -o -n "$_admin" ]; then
      SQLCLI_CNF=`mktemp --tmpdir 'XXXXXXXXXX.pgpass'` || exit 1
      cat >>"$SQLCLI_CNF" <<EOF
*:*:*:$_admin:$_admpass
EOF
      psql_extra_opt="-w -U $_admin"
      if [ -n "$_host" ]; then
         psql_extra_opt="$psql_extra_opt -h $_host"
      fi
      if [ -n "$_port" ]; then
         psql_extra_opt="$psql_extra_opt -p $_port"
      fi
      SQLCLI='non_postgres_client'
   else
      SQLCLI='postgres_client'
   fi
   echo 'select version();' | $SQLCLI template1 >/dev/null
}

pgsql_check_db_exist() {
   if test -n "$(echo "select d.datname as name from pg_catalog.pg_database d where d.datname = '$1'" | $SQLCLI template1)"; then
      echo 'yes'
   else
      echo 'no'
   fi
}

# usage: 
#  1) pgsql_exec_sql < file or echo "sqlcmd" | pgsql_exec_sql
#  2) pgsql_exec_sql dbname < sqlfile
#  3) echo "sqlcmd" | pgsql_exec_sql dbname
#  4) pgsql_exec_sql dbname sqlfile
pgsql_exec_sql() {
   case $# in
      0)
         cat | $SQLCLI template1
         ;;
      1)
         if [ -f "$1" ]; then
            $SQLCLI template1 < "$1"
         else
            cat | $SQLCLI --dbname "$1"
         fi
         ;;
      2)
         $SQLCLI --dbname "$1" < "$2"
         ;;
      *) return 1
         ;;
   esac
}

pgsql_list_database_like() {
   echo "select d.datname as name from pg_catalog.pg_database d where d.datname like '$1'" | $SQLCLI
}

# usage: pgsql_create_user $user $password
pgsql_create_user() {
   r=$(echo "select rolname as name from pg_catalog.pg_authid d where rolname = '$1'" | $SQLCLI)
   if [ -z "$r" ]; then
      echo "create role \"$1\" with login password '$2'" | $SQLCLI > /dev/null
   else
      echo "alter role \"$1\" with login password '$2'" | $SQLCLI > /dev/null
   fi
}

pgsql_drop_user() {
   echo "drop role \"$1\"" | $SQLCLI > /dev/null
}
