Jemný úvod do PL/pgSQL (2)

30. 8. 2002

Doba čtení: 6 minut

Líbí se vám článek?
Podpořte redakci

Dnes se podíváme na použití PL/pgSQL funkcí v CHECK podmínkách, použití PL/pgSQL funkcí při návrhu vlastních operátorů a použití PL/pgSQL funkcí při návrhu vlastních agregačních funkcí.

Funkce v PL/pgSQL můžeme použít jako kontrolní v CHECK podmínkách. Tyto funkce musí mít pouze jeden argument odpovídajícího typu, a pokud je hodnota argumentu NULL, musí vrátit NULL. Toto chování lze ošetřit programově, nebo lze použít atribut isstrict, který zajistí, že kdykoliv je alespoň jeden z argumentů funkce NULL, pak jako výsledek funkce se použije hodnota NULL, aniž by se vykonala funkce. Následující příklad testuje validitu ISBN kódu (sice existuje doplněk isbn_issn, ten však ve většině případů nebude nainstalován). Všimněte si použití proměnné weight typu pole.

CREATE OR REPLACE FUNCTION check_ISBN(CHAR(12)) RETURNS boolean AS '
  DECLARE
ISBN ALIAS FOR $1;
pos INTEGER; asc INTEGER; suma INTEGER DEFAULT 0;
weight INTEGER[] DEFAULT ''{10,9,8,7,6,5,4,3,2,1}'';
digits INTEGER DEFAULT 1;
  BEGIN
FOR pos IN 1..length(ISBN) LOOP
  asc := ascii(substr(ISBN,pos,1));
  IF asc >= 48 AND asc <= 57 THEN
suma := suma + (asc - 48)*weight[digits];
digits := digits + 1;
  END IF;
END LOOP;
IF digits <> 11 THEN
  RETURN ''f'';
ELSE
  RETURN (suma % 11) = 0;
END IF;
  END;
' LANGUAGE 'plpgsql' WITH (isstrict);

Definovanou funkci můžeme použít v CHECK podmínce, např (sloupec isbn):

CREATE TABLE ZCHU_RezervacniKnihy (
  id INTEGER PRIMARY KEY DEFAULT nextval('doc_seq'),
  idoo INTEGER NOT NULL,  -- id objektu ochrany
  taboo CHAR(1) NOT NULL CHECK(taboo IN ('m','v','s')),
  isign varchar(50) NOT NULL UNIQUE CHECK (isign <> ''),
  typ CHAR(2) NOT NULL REFERENCES cv_TypDocRK(kod),
  autor_prijmeni VARCHAR(30) NOT NULL CHECK (autor_prijmeni <> ''),
  autor_jmeno VARCHAR(30) NULL CHECK (autor_jmeno <> ''),
  nazev VARCHAR(100) NOT NULL CHECK (nazev <> ''),
  poznamka VARCHAR(300) NULL,
  rok_vydani INTEGER NULL CHECK (rok_vydani > 1918 AND rok_vydani <
   EXTRACT(year FROM current_date)),
  zalozeno DATE DEFAULT current_date NOT NULL,
  vyrazeno DATE NULL CHECK (vyrazeno > zalozeno),
  isbn CHAR(12) NULL CHECK (check_ISBN(isbn)),
  url VARCHAR(100) NULL CHECK (url ~* '^(file|http|ftp)://.+')
);

Použití PL/pgSQL funkcí při návrhu vlastních operátorů

V CHECK výrazech můžeme používat binární operátory OR a AND. V standardní distribuci chybí operátory XOR a implikace. Nicméně není žádným problémem tyto chybějící operátory do systému doplnit.

CREATE OR REPLACE FUNCTION op_xor (boolean, boolean) RETURNS boolean AS '
  BEGIN
RETURN ((NOT $1) AND $2) OR ($1 AND (NOT $2));
  END;
' LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION op_imp (boolean, boolean) RETURNS boolean AS '
  BEGIN
RETURN ($2 OR NOT $1);
  END;
' LANGUAGE 'plpgsql';

Obě funkce musíme zaregistrovat a přiřadit jim nějaký symbol. To provede příkaz CREATE OPERATOR. Jméno pro operátor vytvoříme kombinací následujících znaků ±*/<>=~!@#%^&|'?$.

DROP OPERATOR # (boolean, boolean);

CREATE OPERATOR # (
  procedure = op_xor,
  leftarg = boolean,
  rightarg = boolean,
  commutator = #
);

DROP OPERATOR >>> (boolean, boolean);

CREATE OPERATOR >>> (
  procedure = op_imp,
  leftarg = boolean,
  rightarg = boolean,
  commutator = >>>
);

Oba operátory si můžeme vyzkoušet na tabulce:

DROP TABLE logtab;

CREATE TABLE logtab (l boolean, p boolean);

INSERT INTO logtab VALUES(FALSE,FALSE);
INSERT INTO logtab VALUES(TRUE,FALSE);
INSERT INTO logtab VALUES(FALSE,TRUE);
INSERT INTO logtab VALUES(TRUE,TRUE);

SELECT l, p, l # p AS XOR FROM logtab;
SELECT l, p, l >>> p AS IMPL FROM logtab;

Použití obou těchto operátorů významně zjednoduší CHECK podmínky. V tabulce Hlaseni požaduji, aby bylo zadáno buď id uživatele, nebo jméno uživatele. Dále je požadováno, aby byl vyplněn sloupec popis chyby, pokud je typ hlášení chyba.

DROP TABLE Hlaseni;
DROP SEQUENCE Hlaseni_id_seq;

CREATE TABLE Hlaseni (
  id SERIAL PRIMARY KEY,
  zalozeno DATE DEFAULT current_date NOT NULL,
  zalozil_neprihlasen VARCHAR(60) CHECK (zalozil_neprihlasen <> ''),
  zalozil INTEGER REFERENCES Uzivatele(id) CHECK (
zalozil_neprihlasen IS NOT NULL # zalozil IS NOT NULL),
  trida_chyby CHAR(2) NOT NULL REFERENCES TridaChHlaseni(kod),
  chybove_hlaseni TEXT CHECK (
(trida_chyby IN ('ch','zc') >>> chybove_hlaseni IS NOT NULL) AND
(chybove_hlaseni <> '')),
  podrobny_popis TEXT NULL CHECK (podrobny_popis <> '')
);

INSERT INTO Hlaseni (zalozil, trida_chyby, podrobny_popis)
  VALUES (1,'po', 'Nainstaloval jsem novou verzi.'); -- ok

INSERT INTO Hlaseni (zalozil, zalozil_neprihlasen, trida_chyby,
  podrobny_popis) VALUES (1, 'Pavel Stěhule','po', 'Nainstaloval
  jsem novou verzi.'); -- selže

INSERT INTO Hlaseni (zalozil_neprihlasen, trida_chyby,
podrobny_popis) VALUES ('Pavel Stěhule','po', 'Nainstaloval
  jsem novou verzi.'); -- ok

INSERT INTO Hlaseni (zalozil_neprihlasen, trida_chyby,
podrobny_popis) VALUES ('Pavel Stěhule','ch', 'Nainstaloval
  jsem novou verzi.'); -- selže

Neexistenci operátoru XOR lze obejít poměrně jednoduše (bez nutnosti psaní PL/pgSQL funkce).

 zalozil INTEGER REFERENCES Uzivatele(id) CHECK (
(zalozil_neprihlasen IS NOT NULL) <> (zalozil IS NOT NULL))

Použití PL/pgSQL funkcí při návrhu vlastních agregačních funkcí

Porovnáme-li počet vestavěných agregačních funkcí v PostgreSQL s jinými srovnatelnými RDBMS, zjistíme, že v PostgreSQL mnohé agregační funkce chybí. Naštěstí si v PostgreSQL můžeme agregační funkci navrhnout sami.

V PostgreSQL agregační funkci vytvoříme pomocí dvou funkcí. První, která se spouští pro každou hodnotu (prvním parametrem je mezivýsledek, druhým pak samotná hodnota), a finální, jejíž parametrem je stávající mezivýsledek. První příklad tuto finální funkci nepotřebuje – vytvoří seznam položek oddělených čárkou. U agregačních funkcí je třeba (aby se chovaly tak, jak se očekává), aby ignorovaly hodnotu NULL.

CREATE OR REPLACE FUNCTION comma_aggreg (text, text) RETURNS text AS '
  BEGIN
IF $2 IS NULL THEN
  RETURN $1;
END IF
IF length($1) > 0 THEN
  RETURN $1 || ', ' || $2;
ELSE
  RETURN $2;
END IF;
  END;
' LANGUAGE 'plpgsql';

DROP AGGREGATE comma(text);

CREATE AGGREGATE comma (
  basetype = text,
  sfunc = comma_aggreg,
  stype = text,
  initcond = ''
);

SELECT comma(jmeno) FROM jmena;

Následující, mírně komplikovanější, agregační funkce vrací seznam položek v HTML formátu.

CREATE OR REPLACE FUNCTION html_li_aggr (text, text) RETURNS text AS '
  BEGIN
IF $2 IS NULL THEN
  RETURN $1;
ELSE
  RETURN $1 || ''<li>'' || $2 || ''</li>\n'';
END IF;
  END;
' LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION html_ul_final (text) RETURNS text AS '
  BEGIN
IF $1 <> '''' THEN
  RETURN ''<ul>\n'' || $1 || ''</ul>'';
ELSE
  RETURN '''';
END IF;
  END;
' LANGUAGE 'plpgsql';

DROP AGGREGATE html_ul (text);

CREATE AGGREGATE html_ul (
  basetype = text,
  sfunc = html_li_aggr,
  stype = text,
  initcond = '',
  finalfunc = html_ul_final
);

Výsledkem dotazu SELECT html_ul(jmeno) FROM jmena je jedna hodnota (i když více řádků textu), kterou můžeme přímo vložit do HTML stránky.

<ul>
<li>Stěhule</li>
<li>Rusňák</li>

<li>Chromečka</li>
<li>Bilíček</li>
<li>Bilak</li>
<li>Gottwald</li>
</ul>

Dvě agregační funkce, které v PostgreSQL nenajdete a např. v MS Access nebo MSQL ano, jsou First a Last. First vrací první hodnotu z množiny, Last poslední.

CREATE OR REPLACE FUNCTION First_aggr (text, text) RETURNS text AS '
  BEGIN
IF $1 <> '''' THEN
  RETURN $1;
ELSE
  RETURN $2;
END IF;
  END;
' LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION Last_aggr (text, text) RETURNS text AS '
  BEGIN
RETURN $2;
  END;
' LANGUAGE 'plpgsql';

DROP AGGREGATE First (text);

CREATE AGGREGATE First (
  basetype = text,
  sfunc = First_aggr,
  stype = text,
  initcond = ''
);

DROP AGGREGATE Last (text);

CREATE AGGREGATE Last (
  basetype = text,
  sfunc = Last_aggr,
  stype = text
);

SELECT First(jmeno) FROM jmena; -- -> Stěhule
SELECT Last(jmeno) FROM jmena;  -- -> Gottwald