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.
#!/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;
#!/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.