#!/bin/sh
# @(#) shows current queries on a PostgreSQL server
# (c) 2008 by Dirk Jagdmann <doj@cubic.org>

# This software is provided 'as-is', without any express or implied
# warranty. In no event will the authors be held liable for any damages
# arising from the use of this software.
#
# Permission is granted to anyone to use this software for any purpose,
# including commercial applications, and to alter it and redistribute it
# freely, subject to the following restrictions:
#
#     1. The origin of this software must not be misrepresented; you
#        must not claim that you wrote the original software. If you use
#        this software in a product, an acknowledgment in the product
#        documentation would be appreciated but is not required.
#
#     2. Altered source versions must be plainly marked as such, and
#        must not be misrepresented as being the original software.
#
#     3. This notice may not be removed or altered from any source
#        distribution.

usage="usage: pgstat [-i] [-d dbname]\n-i show idle connections\n-d show only connections of specified database"

noidle=1

if [ -z "$PGDATABASE" ] ; then
   PGDATABASE=template1
fi

while getopts "d:ih" options; do
  case $options in
    d ) db=$OPTARG;;
    i ) unset noidle;;

    h ) echo -e $usage
         exit 1;;
    \? ) echo -e $usage
         exit 1;;
    * ) echo -e $usage
          exit 1;;
  esac
done

q="select datname as database, usename as user, query_start as start, (now()-query_start) as elapsed, current_query as q from pg_stat_activity where 1=1"

if [ "$noidle" = 1 ]
then q="$q and current_query <> '<IDLE>'"
fi
if [ -z "$db" ]
then q="$q and datname <> '$PGDATABASE'"
else q="$q and datname = '$db'"
fi

psql -d "$PGDATABASE" -c "$q"
exit $?

=head1 NAME

B<pgstat> - show queries executed and active connections on PostgreSQL database server

=head1 SYNOPSIS

B<pgstat> [B<-i>] [B<-d dbname>]

=head1 OPTIONS

=over

=item B<-i>

show idle connections

=item B<-d dbname>

show only connections of specified database

=back

=head1 ENVIRONMENT

B<PGDATABASE>, B<PGHOST>, B<PGPORT>, B<PGUSER> as described in psql(1).

=head1 SEE ALSO

psql(1)

=head1 AUTHOR

Dirk Jagdmann <doj@cubic.org>
L<http://pgfoundry.org/projects/pg-toolbox/>

=head1 LICENSE

zlib/libpng license: L<http://en.wikipedia.org/wiki/Zlib_License>

=cut
