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

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

=> \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

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