Pgls

Z PostgreSQL
Přejít na: navigace, hledání
unset SHOW_ALL
unset ONLY_OWNED
unset LIMIT
unset SORT_SIZE_ASC
unset SORT_SIZE_DESC

while getopts "au:U:h:n:sS" opt; do
  case $opt in
    a)
      SHOW_ALL='YES'
      ;;
    u)
      OWNED_BY=$OPTARG
      ;;
    n)
      LIMIT=$OPTARG
      ;;
    s)
      SORT_SIZE_ASC=YES
      ;;
    S)
      SORT_SIZE_DESC=YES
      ;;
    U)
      export PGUSER=$OPTARG
      ;;
    h)
      export PGHOST=$OPTARG
      ;;
    p)
      export PGPORT=$OPTARG
      ;;
    \?)
      echo "pgls list of PostgreSQL databases.

Usage:
  pgls [OPTIONS]

Filtering options:
  -a                       show all connectable databases
  -n ROWS                  show only n rows
  -s                       sort by size 
  -S                       sort by size descent
  -u USERNAME              show databases owned by user

Connection options:
  -h HOSTNAME              database server host
  -p PORT                  database server port
  -U USERNAME              connect as user
"
      exit 1
      ;;
  esac
done

SQLQUERY="SELECT datname FROM pg_database WHERE datallowconn"

if [ -z "$SHOW_ALL" ]; then
  SQLQUERY="$SQLQUERY AND NOT datistemplate"
fi

if [ -n "$OWNED_BY" ]; then
  SQLQUERY="$SQLQUERY AND datdba = (SELECT oid FROM pg_roles WHERE rolname='$OWNED_BY')"
fi

if [ -n "$SORT_SIZE_ASC" ]; then
  SQLQUERY="$SQLQUERY ORDER BY pg_database_size(oid) ASC"
elif [ -n "$SORT_SIZE_DESC" ]; then
  SQLQUERY="$SQLQUERY ORDER BY pg_database_size(oid) DESC"
fi

if [ -n "$LIMIT" ]; then
  SQLQUERY="$SQLQUERY LIMIT $LIMIT"
fi

psql -At -c "$SQLQUERY" postgres

Použití:

[pavel@dhcppc2 ~]$ ./pgls -a | xargs -n 1 psql -c "select current_database()"
 current_database 
------------------
 template1
(1 row)

 current_database 
------------------
 postgres
(1 row)