Krátce o PL/Perl

Neznám žádnou jinou SQL databázi, které by měla bohatší nabídku programovacích jazyků než PostgreSQL. Pravdou je, že tuto vlastnost jsem nikdy nevyužil – oblíbil jsem si jednoduchý a relativně přehledný zápis SQL příkazů v PL/pgSQL a neměl jsem potřebu zkoušet nic dalšího, ačkoliv jsem sem tam v konferenci narazil na příklady použití pltcl a plperl. Na libůstky typy plR se stále dívám jako na čistě akademickou záležitost – patrně asi nikdy nebudu spouštět z uložené procedury komplikované statistické výpočty a R použiji nejspíš jen na klientské straně. Následující příklady ukazují možnosti, které plpgsql nemá (spoušť v pltcl konvertující všechny pole na malá písmena, R – agregační funkce pro určení mediánu).

CREATE OR REPLACE FUNCTION lowercase() RETURNS TRIGGER AS '
  set NEW($1) [string tolower $NEW($1)]
  return [array get NEW]' LANGUAGE pltcl;

CREATE OR REPLACE FUNCTION r_median(_float8) RETURNS float as '
  median(arg1)
' language 'plr';

CREATE AGGREGATE median (
  sfunc = plr_array_accum,
  basetype = float8,
  stype = _float8,
  finalfunc = r_median
); 

Když jsem poprvé viděl plperl, nechápal jsem, k čemu vůbec může být. V předchozích verzích měl relativně velká omezení, víceméně se z něj nedaly spouštět dotazy, nedal se použít pro tvorbu spouští. Snad jen masochista by si místo plpgsql vybral plperl. Nedávno jsem svůj názor musel přehodnotit. Procedury v plperlu se mi náramně hodily.

SELECT substring('žlutý kůň 23' FROM '^[[:alpha:]]+'); --> žlutý
SELECT substring('žlutý kůň 23' FROM '[[:digit:]]+');  --> 23
SELECT * FROM lidi WHERE prijmeni ~* 'a$'; 

PostgreSQL sice obsahuje podporu regulárních výrazů (funkce substring a operátory ~ a ~*), pro složitější úlohy ale vestavěná podpora nestačí. Pokud chceme s regulárními výrazy pracovat více – použít náhrady, výběr řetězců – pak vlastně nemáme žádnou jinou možnost než použít plperl. Ovšem co jiného použít na regulární výrazy než Perl, že? plpgsql regulární výrazy nepodporuje, alespoň já o žádné podpoře nevím. (Kromě plperlu samozřejmě můžeme použít plphp, případně pltcl nebo plpython.) Otázka zní, proč pracovat s regulárními výrazy. Potřeboval jsem čistit databázi. V MySQL, MSSQL bych si musel napsat externí aplikaci, kterou data proženu. V MSSQL si mohu pomoci DTS (data transformation services), kde mám regulární výrazy přístupné ve VB scriptu. V Oracle je package owa_pattern. Teprve nyní mi přijde cena za možnost použití více jazyků přijatelná – komplikovaný zápis, resp. kód, se předává jako string – následně vznikají problémy s nečitelností v plpgsql (přehršel apostrofů).

CREATE OR REPLACE FUNCTION get_numbers(VARCHAR) RETURNS INTEGER[] AS '
  my($source) = @_; $retval = "";
  while ( $source =~ m/([[:digit:]]+)/g ) {
    $retval = $retval . ", " if ($retval ne "");
    $retval = $retval . $1;
  }
  return "{$retval}"; ' LANGUAGE plperl;

work=#SELECT get_numbers('dasf adfsadf 10 dasf dasf 20 adsf 111');
get_numbers
--
{10,20,111}
{1 řádka} 

Bohužel plperl má pravděpodobně nejkomplikovanější instalaci z jazyků podporovaných PostgreSQL. Vyžaduje knihovnu libperl, která obyčejně není na počítačích nainstalována (perl je slinkován staticky). Až budete překládat perl, nezapomeňte, že napřed musíte mít přeloženou a zaregistrovanou knihovnu libperl, pak teprve můžete znova provést configure PostgreSQL se svými parametry a navíc s –with-perl. Není nutné překládat kompletní PostgreSQL, stačí jen adresář /src/pl/plperl.

plperl můžeme použít bez problémů (běží v safe módu). Bohužel se opět jednou projeví naše česká specifika. Aby se operace s řetězci chovaly správně vůči diakritice, je třeba použít locale. Jenomže to už je pro perl nebezpečná operace, a musíme tedy použít untrusted perl. Což může vadit v systémech, kde se tvůrci databází netěší až tak velké důvěře: webhostingy, školy atd. Untrusted plperl nejenže nemá žádná omezení v přístupu k lokálním zdrojům, ale běží pod uživatelem postgres. Není problém si například smazat databázi.

-- Pokud se jedna o Prahu, Brno, Liberec, Ostravu,
-- Plzen nebo Karvinou, tj. jednoslovne nazvy, da
-- se predpokladat, ze dalsim slovem bude identifikace
-- casti, napr. Praha Bohnice -> Praha - Bohnice

CREATE OR REPLACE FUNCTION checkname(varchar) RETURNS varchar AS '
  use locale;
  my ($source) = @_;
  if (! defined $source) {return undef;}

  if ($source =~ m#[a-zA-Z]+[[:space:]]+[a-zA-Z]+#i) {
    if (index($source, "-") == -1 && index($source, ",") && index($source, "+")) {
      $source =~ s/^[[:space:]]*(Praha|Brno|Liberec|Ostrava|Plzeň|Karviná) {1}[[:space:]]/$1 - /i;
    }
  }
  return uc($source); ' LANGUAGE plperlu; 

(Pozn. ed.: mezera za Karvinou být nemá, ústupek sazbě –Johanka)

       

Nejtvrdším omezením plperl je nemožnost přímo volat funkce PostgreSQL. Jakékoliv hodnoty se při převodu z Perlu do PostgreSQL konvertují do řetězců: čísla, texty, ale i pole. plperl podporuje typ record, hodnoty jsou opět textové a uložené v hash tabulce. Pro přístup k funkcím PostgreSQL musíme použít rozhraní DBD::PgSPI. Bohužel toto rozhraní již tři roky nebylo aktualizováno a jeho dokumentace také není nejlepší.

CREATE OR REPLACE FUNCTION foospi() RETURNS VARCHAR AS '
  use DBD::PgSPI;
  my @ar=$pg_dbh->selectrow_array("SELECT CURRENT_USER");
  return $ar[0]; ' LANGUAGE plperlu; 

Když už nám nezbude nic jiného než použít untrusted plperl, nemusíme se bránit používání existující další (nejen db relevantní knihovny) Perlu. S trochou nadsázky by se dalo říci, že o něco podobného se snaží v tuto chvíli Microsoft se svou integrací .NET frameworku do MsSQL.

CREATE OR REPLACE FUNCTION foo() RETURNS text AS '
  use Mail::Sendmail;
  %mail = ( To   =>  q(you@yourname.com),
            From =>  q(me@here.com),
            Message => "This is very short message"
          );
  sendmail(%mail) or die $Mail::Sendmail::error;
  return "Ok. Log says:\n", $Mail::Sendmail::log;
' LANGUAGE plperlu;