imported>Pavel v 18. 7. 2015, 12:18

2015-07-18T12:18:50Z

Nová stránka

<pre>
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
</pre>
Použití:
<pre>
[pavel@dhcppc2 ~]$ ./pgls -a | xargs -n 1 psql -c "select current_database()"
current_database
------------------
template1
(1 row)

current_database
------------------
postgres
(1 row)
</pre>

Pgls - Historie editací

imported>Pavel v 18. 7. 2015, 12:18