Re: psql in a bash function

On Mon, Mar 12, 2018 at 2:14 PM, Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)net> wrote:

> Hi,
>
> Because I need to log into many servers, I created functions as keyboard
> shortcuts (not aliases, since I will want to embed these shortcuts in other
> functions).
>
> psqlxyz ()
> {
> echo "P1=$1";
> echo "P2=$2";
> psql -U postgres -h XYZ $@
> }
>
> This is the (simple, test) command that I want to run, which works when
> run explicitly using psql, but not my function. Any ideas why the function
> isn't properly passing the "-c" and '"select ..."' to psql?
>
> $ psql -U postgres -h XYZ -c "select oid, datname from pg_database;"
> oid | datname
> ---------+------------------------
> 1 | template1
> 11563 | template0
> 11564 | postgres
> 16404 | test1
> 3039800 | ABCD
> 319011 | EFGH
> 649861 | IJKL
> (7 rows)
>
> $ psqldba -c *'*"select oid, datname from pg_database;"*'*
> P1=*-c*
> P2=*"select oid, datname from pg_database;"*
> psql: warning: extra command-line argument "datname" ignored
> psql: warning: extra command-line argument "from" ignored
> psql: warning: extra command-line argument "pg_database;"" ignored
> psql: FATAL: database "oid," does not exist
>
> Thanks
>
> --
> Angular momentum makes the world go 'round.
>

Ron,

Here is a model that works in any LINUX environment. You can tweak for your
commands:
Please include your PostgreSQL version and O/S in future submissions to
this list.

#!/bin/bash
# Reports sizes for all or selected database

PORT=""
USER=""
DBNAME="%"
usage() {
echo "Usage: $0 [-d <dbname> -U <user> -p <port>]"
exit 1
}

while getopts "d:p:uU:" OPT;
do case "${OPT}" in
d) DBNAME=$OPTARG
;;
p) PORT="-p $OPTARG"
;;
U) USER="-U $OPTARG"
;;
u) usage
;;
[?]) usage
esac;
done

if [ "$DBNAME" = "" ]
then
usage
exit 1
fi

psql $PORT $USER postgres <<_EOF_
SELECT txid_current() AS txid_current;
SELECT datname,
rolname as owner,
pg_size_pretty(pg_database_size(datname) )as size_pretty,
pg_database_size(datname) as size,
(SELECT pg_size_pretty (SUM( pg_database_size(datname))::bigint)
FROM pg_database) AS total,
(pg_database_size(datname) / (SELECT SUM(
pg_database_size(datname))
FROM pg_database) ) *
100::numeric(6,3) AS pct
FROM pg_database d
JOIN pg_authid a ON a.oid = datdba
WHERE datname LIKE '%$DBNAME%'
ORDER BY datname;
_EOF_

--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!