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;

• automatizace administrace 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. Funkčně je ekvivalentní s příkladem z dřívějšího článku PL/pgSQL (pro rušení databází byla použita uložená procedura). 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%

• Parametrizace volání uložených 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

• Tvorba jednoduchých CGI skriptů – 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;

• Parametrizace 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.

Pro úplnost dodávám, že psql ve verzi 7.3 umožňuje alespoň minimální parametrizaci SQL příkazů, např.:

testdb011=# \set deset 10
testdb011=# INSERT INTO mojetab(i) VALUES(:deset);

Výzva uživatelů databáze PostgreSQL: Používáte pgbash, PL/pgSQL nebo PL/sh profesionálně? Dejte o sobě vědět. Zajímalo by mne, jaká část uživatelů PostgreSQL tyto nástroje používá a s jakými zkušenostmi.