Re: psql in a bash function

[Melvin Davidson <melvin6925@xxxxxxxxx>]

On Mon, Mar 12, 2018 at 2:14 PM, Ron Johnson <ron.l.johnson@xxxxxxx> 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!