PgBash (en)

Translated by Luboš Truhlář

You can download PgBash as a binary file, or translate it from source codes. They are in a form of patch for bash, so you have to have source codes from appropriate version of bash.

#!/usr/local/bin/pgbash
connect to testdb011;
SELECT name FROM names; | less
disconnect all;

Same like we use variables in shell commands, we can use there in SQL commands. PgBash extend SQL command SELECT with possibility of assign a value of column to shell variables.

#!/usr/local/bin/pgbash
connect to testdb011;
SELECT COUNT(name) INTO :lname FROM names 
  WHERE name LIKE '$1%'; > /dev/null
echo "By prefix $1 start $lname names"
disconnect all;

We can test inbuilded variables $SQLCODE, $SQLERRD2 etc. after the command is done. If SQL command finished with mistake, then $SQLCODE contains number of error, otherwise contains a null (we can use $SQL_OK). $SQLERRD2 contains the number of returned rows.

#!/usr/local/bin/pgbash
connect to testdb011;
SELECT c.relname FROM pg_catalog.pg_class c 
  WHERE c.relkind = 'r' AND pg_catalog.pg_table_is_visible(c.oid) 
    AND c.relname = 'mytab'; > /dev/null
# the table is created only if doesn't exists
if [ $SQLERRD2 = 0 ]; then
  CREATE TABLE mytab (
    a integer,
    b integer
  );
fi
disconnect all;

The iteration by rows of returned table is possible by using a cursor. Script, which return first $2 rows from table names, looks following (first parameter of script is prefix of names):

#!/usr/local/bin/pgbash
connect to testdb011;
BEGIN;
DECLARE c CURSOR FOR 
  SELECT name FROM names WHERE name LIKE '$1%';
lines=1
FETCH IN c INTO :name;
while [ $SQLCODE -eq $SQL_OK ]; do
  if [ $lines -gt $2 ] ; then
    break
  fi
  echo $name
  let "lines+=1"
  FETCH IN c INTO :name;
done
END;
disconnect all;

PgBash can be used for creation of automatic instalation scripts including filling of datas - (pgbash supports dynamic switching of connections ), we can read a table in one connection and write readed rows to the table in the second connection.

#!/usr/local/bin/pgbash
connect to testdb1@kix as db1;
connect to testfce as db2;

set connection db2;

BEGIN;
  DECLARE c CURSOR FOR
  SELECT * FROM t1;
  FETCH IN c INTO :c1, :c2;
  while [ $SQLCODE -eq $SQL_OK ]; do
    set connection db1;
    INSERT INTO t1 VALUES($c1,\'$c2\');
    set connection db2;
    FETCH IN c INTO :c1, :c2;
  done
END;
disconnect all;

We can automate administration of databases or system by the PgBash. We can use periodic starting of applications (cron) , access to file-system (export and import), access to system's variables by automatization of administration of databases . If we use a pgbash to administration of system, it will be probably in the cases, when default datas will be saved in some database (list of users, list of computers etc. ). Next example cancel database, whose name of owner conform to condition in LIKE. I show two variations of command. First utilize the fact that we can evaluate SQL command by ``. And the outcome is a string containing multi-rows text. Second variation uses a cursor. Because of we can use the cursor only in transaction, it is not possible to use command DROP DATABASE in open transaction, script has to contain two cycles.

variation 1

#!/usr/local/bin/pgbash
connect to template1;

set option_header=off;
set option_bottom=off;
set option_alignment=off;
set option_separator=;

dblist=`SELECT d.datname FROM 
          pg_catalog.pg_database d LEFT JOIN pg_catalog.pg_user u 
          ON d.datdba = u.usesysid WHERE u.usename LIKE '$1';`

if [ "$dblist" != "" ]; then
  echo "$dblist" | while read db; do
    echo "Removing databaze $db"
    DROP DATABASE \"$db\";
  done
fi

disconnect all;

variation 2

#!/usr/local/bin/pgbash
connect to template1;

BEGIN;
DECLARE c CURSOR FOR 
        SELECT d.datname FROM 
          pg_catalog.pg_database d LEFT JOIN pg_catalog.pg_user u 
          ON d.datdba = u.usesysid WHERE u.usename LIKE '$1';

FETCH IN c INTO :dbname;
while [ $SQLCODE -eq $SQL_OK ]; do
  dblist="$dblist \"$dbname\""
  FETCH IN c INTO :dbname;
done
END;
eval 'for i in '"$dblist"'; do eval "DROP DATABASE
       \"$i\";"; done'
disconnect all;

If we want to cancel all databases of users groupxx on 1st. March, then we add to the table of cron (command cron -e) row (I suppose, that the script is saved in file dropdbs.psh):

      0 0 1 3 * dropdbs.psh group% 

We can use pgbash like a wrapper plpgsql – we can use the script of pgbash as a simply interface conveying handcover of variables to save procedure.

#!/usr/local/bin/pgbash
connect to template1
SELECT drop_students_databases($1) INTO :zd;
if [ $SQLCODE = $SQL_OK ]; then
  echo " $zd databazes was canceled"
fi
disconnect all

We can create simply CGI script too– pgbash contains mode for CGI, when outputs are formatted to HTML table.

#!/usr/local/bin/pgbash
connect to testdb011;
echo "Content-type: text/html"
echo ""
set EXEC_SQL_OPTION CGI;
echo "<HTML>"
echo "<BODY>"
SELECT * FROM names WHERE name LIKE 'S%';
echo "</BODY>"
echo "</HTML>"
disconnect all;

PgBash can be used as a wrapper of SQL command – we can write an interface for anyone SQL command (the example is in the beginnig of the article). Unfortunately pgbash doesn't support setup of formatting of columns and tables (in contrast of SQL*Plus in RDBMS Oracle), and because of that pgbash can't be used for generating of formations (parameterization of command SELECT).

Definitely pgbash doesn't offer editaton comfort like psql. It serves there where the ability of psql are not sufficing so far. It offers parameterization of SQL commands and basic programming construction (if, like, for). Untill this functionality won't be fill in, pgbash has definitely position in the sun. And for everyone, who use PostgreSQL daily, pgbash will be unreplaceable assistant.