Re: psql in a bash function

[Eric Raskin]

Yes, you need double quotes around $@, as in "$@".

https://www.gnu.org/software/bash/manual/html_node/Special-Parameters.html



Sent from my Verizon, Samsung Galaxy smartphone


 Original message 
From: Ron Johnson <ron.l.john...@cox.net> 
Date: 3/12/18 2:15 PM (GMT-05:00) 
To: pgsql-general <pgsql-gene...@postgresql.org> 
Subject: psql in a bash function 



 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.

Re: psql in a bash function

[Melvin Davidson]

On Mon, Mar 12, 2018 at 2:14 PM, Ron Johnson  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  -U  -p ]"
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!

psql in a bash function

[Ron Johnson]

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.