Thread

1. psql in a bash function

   Ron Johnson <ron.l.johnson@cox.net> — 2018-03-12T18:14:26Z

   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.
   

2. Re: psql in a bash function

   Melvin Davidson <melvin6925@gmail.com> — 2018-03-12T18:21:58Z

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