Процедурные языки

Список установленных процедурных языков:

=> \dL
                      List of languages
  Name   |  Owner   | Trusted |         Description          
---------+----------+---------+------------------------------
 plpgsql | postgres | t       | PL/pgSQL procedural language
(1 row)

По умолчанию доступен только PL/pgSQL.


После появления в PostgreSQL механизма расширений, новые языки принято устанавливать командой CREATE EXTENSION. Проверим, какие еще процедурные языки доступны для установки.

=> SELECT name, installed_version, comment
     FROM pg_available_extensions WHERE name LIKE 'pl%';
  name   | installed_version |                comment                 
---------+-------------------+----------------------------------------
 plperlu |                   | PL/PerlU untrusted procedural language
 plpgsql | 1.0               | PL/pgSQL procedural language
 plperl  |                   | PL/Perl procedural language
(3 rows)


Установим plperl и plperlu, доверенный и недоверенный соответственно:

=> CREATE EXTENSION plperl;
CREATE EXTENSION
=> CREATE EXTENSION plperlu;
CREATE EXTENSION
=> \dL
                           List of languages
  Name   |  Owner   | Trusted |              Description               
---------+----------+---------+----------------------------------------
 plperl  | student  | t       | PL/Perl procedural language
 plperlu | student  | f       | PL/PerlU untrusted procedural language
 plpgsql | postgres | t       | PL/pgSQL procedural language
(3 rows)

Примечание. Чтобы языки автоматически появлялись во всех новых базах данных, расширения нужно предварительно установить в БД template1.


Пример использования доверенного языка plperl:

=> CREATE FUNCTION num_matches(str text, pattern text) RETURNS integer AS $$
my ($str,$pattern) = @_;
my @matches = $str =~ /$pattern/g;
return scalar @matches;
$$ LANGUAGE plperl;
CREATE FUNCTION
=> SELECT num_matches('Hello, world!', 'l');
 num_matches 
-------------
           3
(1 row)


Недоверенные языки могут использовать только суперпользователи.

Пример использования недоверенного языка plperl:

=> CREATE FUNCTION read_file(fname text) RETURNS SETOF text AS $$
my ($fname) = @_;
open FILE, $fname or die "Cannot open file";
chomp(my @f = <FILE>);
close FILE;
return \@f;
$$ LANGUAGE plperlu;
CREATE FUNCTION
=> SELECT * FROM read_file('/etc/passwd') LIMIT 3;
                    read_file                    
-------------------------------------------------
 root:x:0:0:root:/root:/bin/bash
 daemon:x:1:1:daemon:/usr/sbin:/usr/sbin/nologin
 bin:x:2:2:bin:/bin:/usr/sbin/nologin
(3 rows)


Анонимные блоки PL/pgSQL

Минимальный блок PL/pgSQL-кода:

=> DO 'BEGIN END;';
DO

В этом примере:


Вариант программы Hello, World!

=> DO $$
DECLARE
 /* После каждого объявления ставится знак ';'
    Этот же знак ставится после каждого оператора.
 */
    var1 text;
    var2 text := 'World';
BEGIN -- здесь нельзя ставить ;
    var1 := 'Hello';
    RAISE NOTICE '%, %!', var1, var2;
END;
$$;
NOTICE:  Hello, World!
DO

В этом примере:


Вложенные блоки:

=> DO $$
<<outer_block>>
DECLARE
    var text := 'Hello';
BEGIN
    <<inner_block>>
    DECLARE
        var text := 'World';
    BEGIN
        RAISE NOTICE '%, %!', outer_block.var, inner_block.var;
        RAISE NOTICE 'Без метки используется внутренняя переменная: %', var;
    END inner_block;
END outer_block;
$$;
NOTICE:  Hello, World!
NOTICE:  Без метки используется внутренняя переменная: World
DO

Функции PL/pgSQL

Пример функции форматирования номера телефона с входным параметром и условным оператором IF. Для возврата значения используется оператор RETURN.

=> CREATE FUNCTION fmt_in (IN phone text) RETURNS text
AS $$
BEGIN
    IF phone ~ '^[0-9]*$' AND length(phone) = 10 THEN
        RETURN '+7(' || substr(phone,1,3) || ')' 
                     || substr(phone,4,3) || '-'
                     || substr(phone,7,2) || '-'
                     || substr(phone,9);
    ELSE
        RETURN NULL;
    END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;
CREATE FUNCTION

Эта же функция с параметром OUT:

=> CREATE FUNCTION fmt_out (IN phone text, OUT retval text)
AS $$
BEGIN
    retval := fmt_in(phone);
END;
$$ LANGUAGE plpgsql IMMUTABLE;
CREATE FUNCTION

Функции fmt_in и fmt_out по сути одинаковы. Отличия:


Эта же функция с параметром INOUT:

=> CREATE FUNCTION fmt_inout (INOUT phone text)
AS $$
BEGIN
    phone := fmt_out(phone); -- вызов функции с параметром OUT
END;
$$ LANGUAGE plpgsql IMMUTABLE;
CREATE FUNCTION

Параметр INOUT с одной стороны заставляет функцию принимать значение, с другой стороны определяет возвращаемое значение функции.


Все три варианта функции вызываются одинаково:

=> SELECT fmt_in('8122128506'), 
          fmt_out('8122128506'), 
          fmt_inout('8122128506');
      fmt_in      |     fmt_out      |    fmt_inout     
------------------+------------------+------------------
 +7(812)212-85-06 | +7(812)212-85-06 | +7(812)212-85-06
(1 row)


Чтобы вернуть несколько значений, используем несколько OUT параметров.

=> CREATE FUNCTION fmt_out_2 (IN phone text, OUT code text, OUT num text)
-- RETURNS можно не писать, предполагается RETURNS record
AS $$
BEGIN
    IF phone ~ '^[0-9]*$' AND length(phone) = 10 THEN
        code := substr(phone,1,3);
        num  := substr(phone,4);
    ELSE
        code := NULL;
        num  := NULL;
    END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;
CREATE FUNCTION

Условный оператор CASE.

=> DO $$
DECLARE
    code text := (fmt_out_2('8122128506')).code;
BEGIN
    CASE code
    WHEN '495', '499' THEN
        RAISE NOTICE '% - Москва', code;
    WHEN '812' THEN
        RAISE NOTICE '% - Санкт-Петербург', code;
    WHEN '384' THEN
        RAISE NOTICE '% - Кемеровская область', code;
    ELSE
        RAISE NOTICE '% - Прочие', code;
    END CASE;
END;
$$;
NOTICE:  812 - Санкт-Петербург
DO

Циклы

В PL/pgSQL можно использовать циклы:


Целочисленный вариант цикла FOR.

Цикл повторяется, пока счетчик цикла (i), инициализированный в значение нижней границы, не превысит верхнюю границу цикла. С каждой итерацией счетчик увеличивается на 1 (можно изменить в BY).

Для этого варианта FOR не требуется явное объявление счетчика цикла.

=> CREATE FUNCTION reverse_for (line text) RETURNS text
AS $$
DECLARE
    line_length CONSTANT int := length(line);
    retval text := '';
BEGIN
    FOR i IN 1 .. line_length LOOP
        retval := substr(line, i,1) || retval;
    END LOOP;
    RETURN retval;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;
CREATE FUNCTION

Примечание. Указание STRICT говорит, что если не заданы входные параметры, то нужно сразу вернуть NULL без выполнения функции.


При указании REVERSE значение счетчика на каждой итерации уменьшается, а нижнюю и верхнюю границы цикла нужно поменять местами.

=> CREATE FUNCTION reverse_rof (line text) RETURNS text
AS $$
DECLARE
    line_length CONSTANT int := length(line);
    retval text := '';
BEGIN
    FOR i IN REVERSE line_length .. 1 LOOP
        retval := retval || substr(line, i,1);
    END LOOP;
    RETURN retval;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;
CREATE FUNCTION

Цикл WHILE.

Выполняется до тех пор, пока истинно условие на входе в цикл.

=> CREATE FUNCTION reverse_while (line text) RETURNS text
AS $$
DECLARE
    line_length CONSTANT int := length(line);
    i int := 1;
    retval text := '';
BEGIN
    WHILE i <= line_length LOOP
        retval := substr(line, i,1) || retval;
        i := i + 1;
    END LOOP;
    RETURN retval;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;
CREATE FUNCTION

Цикл LOOP.

Для выхода из цикла используется оператор EXIT:

=> CREATE FUNCTION reverse_loop (line text) RETURNS text
AS $$
DECLARE
    line_length CONSTANT int := length(reverse_loop.line);
    i int := 1;
    retval text := '';
BEGIN
    <<main_loop>>
    LOOP
        EXIT main_loop WHEN i > line_length;
        retval := substr(reverse_loop.line, i,1) || retval;
        i := i + 1;
    END LOOP;
    RETURN retval;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;
CREATE FUNCTION

Обратите внимание. Тело функции помещается в неявный блок, метка которого совпадает с именем функции. Поэтому к параметрам можно обращаться как имя_функции.параметр.


Убедимся что функции работают правильно:

=> SELECT reverse_for('абырвалГ') as "for",
          reverse_rof('Главрыба') as "reverse for",
          reverse_while('абырвалГ') as "while",
          reverse_loop('Главрыба') as "loop";
   for    | reverse for |  while   |   loop   
----------+-------------+----------+----------
 Главрыба | абырвалГ    | Главрыба | абырвалГ
(1 row)


Замечание. В PostgreSQL есть встроенная функция reverse.


Выражения

Любые выражения в PL/pgSQL выполняются с помощью запросов к базе данных. Самый простой способ убедиться в этом - совершить ошибку и посмотреть на сообщение:

=> DO $$
BEGIN
    RAISE NOTICE '%', 2 + 'a';
END;
$$;
ERROR:  invalid input syntax for integer: "a"
LINE 1: SELECT 2 + 'a'
                   ^
QUERY:  SELECT 2 + 'a'
CONTEXT:  PL/pgSQL function inline_code_block line 3 at RAISE

Таким образом, в PL/pgSQL доступно ровно то, что доступно и в SQL. Например, если в SQL можно использовать конструкцию CASE, то точно такая же конструкция будет работать и в коде на PL/pgSQL (в качестве выражения; не путайте с оператором CASE ... END CASE, который есть только PL/pgSQL):

=> DO $$
BEGIN
    RAISE NOTICE '%', CASE 2+2 WHEN 4 THEN 'Все в порядке' END;
END;
$$;
NOTICE:  Все в порядке
DO

В выражениях можно использовать и подзапросы, но только скалярные:

=> CREATE TABLE t(id integer, code text);
CREATE TABLE
=> INSERT INTO t VALUES (1, 'Раз'), (2, 'Два');
INSERT 0 2
=> DO $$
BEGIN
    RAISE NOTICE '%', (SELECT code FROM t WHERE id=1);
END;
$$;
NOTICE:  Раз
DO

Конец демонстрации.