Introduction to PL/pgSQL

Usage of PL/pgSQL for administration of database

Suitable designed saved procedures make administration of database measurably easier. I use following function for clearing RDBMS from databases that students create during a term. If the function is started without parameters or if the amount of arguments does not agree as well, it shows help.

CREATE OR REPLACE FUNCTION drop_students_databases() RETURNS INTEGER AS $$
DECLARE
  helpstr VARCHAR(300);
BEGIN
  helpstr  := E'Funkce pro zrušení všech databází uživatelů jejichž\n' ||
    E'jméno (jméno uživatele) vyhovuje masce příkazu LIKE.\n\n' ||
    E'např. SELECT drop_students_databases(' || quote_literal('group%') || E')\n\n' ||
    E'pozn. musíte být přihlášen jako uživatel postgres.\n' ||
    E'autor: Pavel Stěhule, stehule@kix.fsv.cvut.cz 19.1.2002\n';

  RAISE NOTICE '%', helpstr;
  RETURN 0;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION drop_students_databases(_mask varchar(100)) RETURNS INTEGER AS $$
DECLARE 
  db RECORD;
  deleted INTEGER := 0;
  helpstr VARCHAR(300);
BEGIN 
  IF length(_mask) = 0 OR _mask ISNULL OR current_user() <> 'postgres' THEN
    helpstr  := E'Funkce pro zrušení všech databází uživatelů jejichž\n' ||
      E'jméno (jméno uživatele) vyhovuje masce příkazu LIKE.\n\n' ||
      E'např. SELECT drop_students_databases(' || quote_literal('group%') || ')\n\n' ||
      E'pozn. musíte být přihlášen jako uživatel postgres.\n' ||
      E'autor: Pavel Stěhule, stehule@kix.fsv.cvut.cz 19.1.2002\n';
    IF current_user <> 'postgres' THEN
      RAISE EXCEPTION E'Funkci může volat pouze uživatel postgres\n\n%', helpstr;
    ELSE
      RAISE EXCEPTION E'Argument funkce musí být neprázdný řetězec\n\n%', helpstr;    
    END IF;
  END IF;
  
  FOR db IN 
    select datname, usename from pg_database, pg_user 
      where datdba=usesysid and usename like _mask
  LOOP
    deleted := deleted + 1;
    RAISE NOTICE 'Drop database %,%', db.datname, db.usename;
    EXECUTE 'drop database "' || db.datname || '"';
  END LOOP;
  RETURN deleted;
END;
$$ LANGUAGE plpgsql;

Notice the construction of FOR IN LOOP END LOOP which iterates through the result of SQL order (in this case list of databases whose owners suit the mask LIKE). Since tables may contain gaps, it is needed to type the name table in quotation marks. PL/pgSQL has problems when exerting order DROP DATABASE if the name of database contains a gap. We can go round this problem by saving the whole SQL order in chain and we perform the enquiry EXECUTE. We can use EXECUTE in all cases when PL/pgSQL refuses the enquiry.

Usage of PL/pgSQL in CHECK conditions

We can use functions in PL/pgSQL as a control in CHECK conditions. These functions have to have only one argument of corresponding type and if the value of the argument is NULL, then it must return NULL. This behaviour may be treated programmatic or we may use attribute isstrict which ensure that whenever at least one of the arguments is NULL, then we use the value NULL like the result of function without executing the function. The following example tests the validity of ISBN code (although supplement isbn_issn exists, it will not be installed in most cases). Notice the usage of parameter weight of field type.

CREATE OR REPLACE FUNCTION check_ISBN(ISBN CHAR(12)) RETURNS boolean AS $$
  DECLARE 
    pos INTEGER; asc INTEGER; suma INTEGER DEFAULT 0;
    weight INTEGER[] DEFAULT '{10,9,8,7,6,5,4,3,2,1}';  -- pro ISSN {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 IN (88, 120) THEN -- ISDN muze obsahovat kontrolni cislo X
        suma := suma + 10;
        digits := digits + 1;
      ELSIF asc >= 48 AND asc <= 57 THEN
        suma := suma + (asc - 48)*weight[digits];
        digits := digits + 1;
      END IF;
    END LOOP;
    IF digits <> 11 THEN -- pro ISSN <> 9
      RETURN 'f';
    ELSE
      RETURN (suma % 11) = 0;
    END IF;
  END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;

We can use the defined function for CHECK condition, e.g. (column 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)://.+')
);

Usage of PL/pgSQL functions for designing own operators

PostgreSQL does not support operation of dividing interspace by interspace. However, it is not problem to overcome this defect and define own operator / for this combination of operand.

omega=# select '1hour'::interval / '10min'::interval;
ERROR:  operator does not exist: interval / interval

CREATE OR REPLACE FUNCTION div_op(a interval, b interval) 
RETURNS double precision AS $$
BEGIN
  RETURN EXTRACT(EPOCH FROM a) / EXTRACT(EPOCH FROM b);
END;
$$ LANGUAGE plpgsql IMMUTABLE RETURNS NULL ON NULL INPUT;

CREATE OPERATOR / (procedure = div_op, leftarg = interval, rightarg = interval);

omega=# select '1hour'::interval / '10min'::interval;
 ?column?
----------
        6

In CHECK expressions we can use binary operators OR and AND. In standard distribution the operators XOR are missing. Nevertheless it is not a problem this missing operators fill in the system.

CREATE OR REPLACE FUNCTION op_xor (a boolean, b boolean) RETURNS boolean AS $$
  BEGIN
    RETURN ((NOT a) AND b) OR (a AND (NOT b));
  END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION op_imp (a boolean, b boolean) RETURNS boolean AS $$
  BEGIN
    RETURN (a OR NOT b);
  END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;

Both functions have to be registered and we have to push ashore them some symbol. It performs the order CREATE OPERATOR. We create the name of the operator by combining following characters +-*/<>=~!@#%^&|’?$.

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 = >>>
);

We can try both operators on the table:

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;

Using of this two operators make the CHECK conditions significantly easier. In the table Hlaseni I require entering user’s id or user’s name. Further I require filling in column popis chyby if the type of return is error.

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 

Absence of operators XOR can be solved easily (without necessity of writing PL/pgSQL function).

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