PgBash

PgBash si můžete stáhnout jako binární soubor, nebo přeložit ze zdrojových kódů. Ty jsou ve formě patche pro bash, tudíž musíte mít k dispozici zdrojové kódy od odpovídající verze bashe.

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

Stejně jako používáme proměnné v příkazech shellu, můžeme proměnné použít v SQL příkazech. Pgbash rozšiřuje SQL příkaz SELECT o možnost přiřadit hodnotu sloupce proměnné shellu.

#!/usr/local/bin/pgbash
connect to testdb011;
SELECT COUNT(jmeno) INTO :pjmen FROM jmena 
  WHERE jmeno LIKE '$1%'; > /dev/null
echo "Prefixem $1 začíná $pjmen jmen"
disconnect all;

Po provedení dotazu můžeme testovat "vestavěné" proměnné $SQLCODE, $SQLERRD2 a další. Pokud SQL příkaz skončil s chybou, pak $SQLCODE obsahuje číslo chyby, jinak obsahuje nulu (můžeme použít $SQL_OK). $SQLERRD2 obsahuje počet vrácených řádků.

#!/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 = 'mojetab'; > /dev/null
# tabulka se vytvori pouze v pripade, ze dosud neexistuje
if [ $SQLERRD2 = 0 ]; then
  CREATE TABLE mojetab (
    a integer,
    b integer
  );
fi
disconnect all;

Iterace po řádcích vrácené tabulky je možná pomocí kurzorů. Skript, který vypíše prvních $2 řádků z tabulky jména, vypadá následovně (prvním parametrem skriptu je prefix jmen):

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

Pgbash můžeme použít pro tvorbu automatizovaných instalačních skriptů včetně plnění údaji - (pgbash podporuje dynamické přepínání připojení), v jednom připojení můžeme číst tabulku, ve druhém zapisovat přečtené řádky do tabulky.

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

Prostřednictvím pgbashe můžeme automatizovat administraci databází nebo systému. Při automatizaci administrace databáze můžeme využít periodické spouštění aplikací (cron), přístup k souborovému systému (exporty a importy), přístup k systémovým proměnným. Pokud použijeme pgbash pro administraci systému, tak pravděpodobně v těch případech, kdy výchozí data budou uložena v některé databázi (seznam uživatelů, seznam počítačů atd).

Následující příklad ruší ty databáze, jejichž jméno vlastníka vyhovuje podmínce LIKE. Uvádím dvě varianty příkazu. První využívá faktu, že SQL příkaz lze vyhodnotit pomocí ``. Výsledkem je pak řetězec obsahující víceřádkový text. Druhá varianta používá kurzor. Jelikož kurzor můžeme používat pouze v transakci a při otevřené transakci nelze použít příkaz DROP DATABASE, musí skript obsahovat dva cykly.

varianta 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 "Odstranuji databazi $db"
    DROP DATABASE \"$db\";
  done
fi

disconnect all;

varianta 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;

Jestliže budeme chtít 1. března zrušit všechny databáze uživatelů groupxx, pak přidáme do tabulky cronu (příkazem cron -e) řádek (předpokládám, že je skript uložen v souboru dropdbs.psh):

      0 0 1 3 * dropdbs.psh group% 

Pgbash může použít jako wrapper plpgsql procedur - skript pgbashe můžeme použít jako jednoduchý interface zprostředkovávající předání parametrů uložené proceduře.

#!/usr/local/bin/pgbash
connect to template1
SELECT drop_students_databases($1) INTO :zd;
if [ $SQLCODE = $SQL_OK ]; then
  echo "Celkem bylo zruseno $zd databází"
fi
disconnect all

Také lze v pgbashi vytvářetjednoduché CGI skripty - pgbash obsahuje mod pro CGI, kdy jsou výstupy zformátovány do HTML tabulky.

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

Pgbash lze použít jako wraper SQL příkazů - můžeme napsat rozhraní pro libovolný SQL příkaz (viz. příklad na začátku článku). Bohužel pgbash nepodporuje nastavení formátování sloupců a tabulek (tak, jak jej podporuje např. SQL*Plus v RDBMS Oracle), a tak se zatím pgbash pro generování sestav (parametrizace příkazu SELECT) nedá použít.

Pgbash rozhodně nenabízí takový editační komfort jako psql. Poslouží však tam, kde jsou schopnosti psql zatím nedostačující. Nabízí parametrizaci SQL příkazů a základní programové konstrukce (if, while, for). Dokud tato funkčnost nebude do psql doplněna, má pgbash rozhodně své místo na slunci a pro všechny, kteří denodenně používají PostgreSQL, se stane nenahraditelným pomocníkem.