Thread: psql in a bash function

psql in a bash function

From

Ron Johnson

Date:

13 March 2018, 00:14:26

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=-cP2="select oid, datname from pg_database;"psql: warning: extra command-line argument "datname" ignoredpsql: warning: extra command-line argument "from" ignoredpsql: warning: extra command-line argument "pg_database;"" ignoredpsql: FATAL: database "oid," does not exist

Thanks

--
Angular momentum makes the world go 'round.

Re: psql in a bash function

From

Melvin Davidson

Date:

13 March 2018, 00:21:58

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=-cP2="select oid, datname from pg_database;"psql: warning: extra command-line argument "datname" ignoredpsql: warning: extra command-line argument "from" ignoredpsql: warning: extra command-line argument "pg_database;"" ignoredpsql: 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!

Re: psql in a bash function

From

Eric Raskin

Date:

13 March 2018, 00:24:01

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.johnson@cox.net>

Date: 3/12/18 2:15 PM (GMT-05:00)

To: pgsql-general <pgsql-general@postgresql.org>

Subject: psql in a bash function

--
Angular momentum makes the world go 'round.

Re: psql in a bash function

From

Ron Johnson

Date:

13 March 2018, 00:32:24

I knew it was something simple... Thanks

On 03/12/2018 01:24 PM, Eric Raskin wrote:

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.johnson@cox.net>
Date: 3/12/18 2:15 PM (GMT-05:00)
To: pgsql-general <pgsql-general@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=-cP2="select oid, datname from pg_database;"psql: warning: extra command-line argument "datname" ignoredpsql: warning: extra command-line argument "from" ignoredpsql: warning: extra command-line argument "pg_database;"" ignoredpsql: FATAL: database "oid," does not exist

Thanks

--
Angular momentum makes the world go 'round.

--
Angular momentum makes the world go 'round.