Использование PL/Perl

Евгения Кузина

Обзор

PL/Perl — это загружаемый процедурный язык, который позволяет писать хранимые процедуры и функции на языке программирования Perl в ADPG/PostgreSQL. Основным преимуществом PL/Perl является то, что он позволяет применять множество функций и операторов для работы со строками, имеющихся в Perl. Разобрать сложные строки на языке Perl может быть значительно проще, чем использовать строковые функции и управляющие структуры в PL/pgSQL.

Обычно PL/Perl устанавливается как доверенный язык программирования с названием plperl. В этом языке некоторые операции Perl отключены для обеспечения безопасности. Запрещены все операции взаимодействия с окружением, в том числе операции с файлами, функции Perl require и use, используемые для внешних модулей. Следовательно, функции PL/Perl не могут взаимодействовать с внутренними механизмами сервера базы данных или получать доступ к операционной системе с правами серверного процесса. В результате любому пользователю базы данных, не имеющему привилегий, может быть разрешено использовать этот язык.

ВНИМАНИЕ
Доверенный PL/Perl использует модуль Perl Opcode для обеспечения безопасности. Однако в документации Perl указано, что этот модуль неэффективно работает для доверенного PL/Perl. Если у вас строгие требования к безопасности, рассмотрите возможность выполнения команды REVOKE: REVOKE USAGE ON LANGUAGE plperl FROM PUBLIC. Она отзывает права использовать plperl у PUBLIC. Ключевое слово PUBLIC применяет команду к неявно определенной группе всех ролей.

Чтобы установить PL/Perl в базу данных в качестве доверенного языка, используйте команду CREATE EXTENSION следующим образом:

CREATE EXTENSION plperl;

Если есть необходимость написать на Perl код, функциональность которого не будет ограничиваться, можно установить Perl как недоверенный язык PL/PerlU. В этом случае будут доступны все возможности языка Perl. Чтобы выбрать недоверенный вариант PL/Perl, укажите имя plperlu, создавая расширение:

CREATE EXTENSION plperlu;

При использовании PL/PerlU необходимо убедиться, что с помощью создаваемых функций нельзя будет сделать что-то незапланированное, поскольку эти функции могут выполнять любые операции, которые доступны пользователю с правами администратора базы данных. Обратите внимание, что ADPG/PostgreSQL позволяет только суперпользователям базы данных создавать функции на недоверенных языках.

Функции PL/Perl выполняются в отдельном интерпретаторе Perl для каждой роли SQL. Все функции PL/PerlU, выполняемые в текущей сессии, выполняются в одном интерпретаторе Perl. Это позволяет функциям PL/PerlU обмениваться данными между собой, но взаимосвязь между функциями PL/Perl и PL/PerlU невозможна.

ПРИМЕЧАНИЕ
Если расширение plperl или plperlu создано в базе данных template1, используемой в качестве темплейта по умолчанию, во всех вновь создаваемых базах данных будет установлено это расширение.

ADPG/PostgreSQL поддерживает конфигурационные параметры, которые влияют на PL/Perl. Они перечислены в таблице ниже. Эти параметры можно задать в поле postgresql.conf на вкладке Primary configuration сервиса ADPG. См. Конфигурационные параметры.

Конфигурационные параметры, влияющие на PL/Perl
Название Тип Описание

plperl.on_init

String

Позволяет указать код Perl, который будет выполнен при первой инициализации интерпретатора Perl, до того, как он будет специализирован для использования plperl или plperlu. Функции SPI недоступны при выполнении этого кода. Если выполнение кода завершается ошибкой, инициализация интерпретатора прерывается и ошибка возвращается в вызывающий запрос. В результате текущая транзакция или подтранзакция будет прервана. Для параметра plperl.on_init код ограничен одной строкой. Более длинный код можно поместить в модуль и загрузить строкой, присвоив значение параметру, например: 'require "plperlinit.pl"', 'use lib "/my/app"; use MyApp::PgInit;'.

Этот параметр можно задать только в поле postgresql.conf

plperl.on_plperl_init

plperl.on_plperlu_init

String

Параметры задают код Perl, который будет выполнен, когда интерпретатор Perl получил специализацию plperl или plperlu в соответствии с названием параметра. Это происходит, когда функция PL/Perl или PL/PerlU впервые выполняется в сессии, или когда необходимо создать дополнительный интерпретатор для другого языка, или функция PL/Perl вызывается новой ролью SQL. Код запускается после инициализации, осуществленной с помощью plperl.on_init. Функции SPI недоступны при выполнении этого кода. Если код завершается ошибкой, инициализация прекращается и происходит прерывание текущей транзакции или подтранзакции. Любые действия, уже выполненные в Perl, отменены не будут. Однако этот интерпретатор не будет больше использоваться. Если язык задействуется снова, будет предпринята попытка инициализации нового интерпретатора Perl.

Только суперпользователи могут изменять параметры plperl.on_plperlu_init и plperl.on_plperl_init. Хотя эти настройки можно обновить в рамках сессии, такие изменения не повлияют на интерпретаторы Perl, которые уже использовались для выполнения функций

plperl.use_strict

Bool

Если установлено true, последующие функции PL/Perl будут компилироваться с включенной опцией STRICT. Этот параметр не влияет на функции, уже скомпилированные в текущей сессии

За дополнительной информацией обратитесь к статье PL/Perl Under the Hood. Configuration.

Функции, процедуры и аргументы PL/Perl

Чтобы создать функцию на языке PL/Perl, используйте стандартную команду CREATE FUNCTION:

CREATE FUNCTION funcname (argument-types)
RETURNS return-type
-- атрибуты функции
AS $$
# тело функции PL/Perl
$$ LANGUAGE plperl;

Тело функции представляет собой обычный код Perl. Код PL/Perl оборачивает код функции в подпрограмму Perl. Функция PL/Perl вызывается в скалярном контексте, поэтому она не может вернуть список. Чтобы вернуть нескалярные значения (массивы, записи и наборы данных), используйте ссылки, как описано ниже.

В процедурах PL/Perl любое возвращаемое значение из кода Perl игнорируется.

PL/Perl также поддерживает анонимные блоки кода, вызываемые с помощью оператора DO:

DO $$
# код PL/Perl
$$ LANGUAGE plperl;

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

Не рекомендуется использовать вложенные именованные подпрограммы в Perl, особенно если они обращаются к лексическим переменным в окружающей области. Поскольку функция PL/Perl заключена в подпрограмму, любая именованная подпрограмма, помещенная внутрь нее, будет вложенной. В общем случае гораздо безопаснее создавать анонимные процедуры и вызывать их по ссылке на код. См. описание значений Variable "%s" will not stay shared и Variable "%s" is not available в статье perldiag.

Синтаксис команды CREATE FUNCTION требует, чтобы тело функции было строковой константой. Обычно используется строковая константа в долларовых кавычках. Если вы хотите использовать escape-синтаксис для строки E'', необходимо удваивать одинарные кавычки (') и обратные слэши (\) в теле функции.

Аргументы и результаты обрабатываются так же, как и в любой другой подпрограмме Perl: аргументы передаются в @_, а результирующим значением будет указанное в return или полученное в последнем выражении, вычисленном в функции.

Например, функция, возвращающая большее из двух целочисленных значений, может быть определена как:

CREATE FUNCTION return_max (integer, integer) RETURNS integer AS $$
    if ($_[0] > $_[1]) { return $_[0]; }
    return $_[1];
$$ LANGUAGE plperl;

Аргументы будут преобразованы из кодировки базы данных в UTF-8 для использования в PL/Perl, а затем конвертированы из UTF-8 обратно в кодировку базы данных при возврате значений.

Если в функцию передается значение SQL NULL, значение аргумента обрабатывается как undefined в Perl. Функция, приведенная выше, будет обрабатывать входные значения NULL как нули, что может привести к непредсказуемым результатам. Чтобы избежать этой ситуации, добавьте STRICT в определение функции. В этом случае, если будет передано значение NULL, вызова функции не произойдет и автоматически вернется результат NULL. В качестве альтернативы можно проверить наличие входных аргументов NULL в теле функции. Например, можно обновить функцию return_max, чтобы она возвращала ненулевой аргумент, если один из аргументов равен NULL:

CREATE FUNCTION return_max (integer, integer) RETURNS integer AS $$
    my ($a, $b) = @_;
    if (not defined $a) {
        return undef if not defined $b;
        return $b;
    }
    return $a if not defined $b;
    return $a if $a > $b;
    return $a;
$$ LANGUAGE plperl;

Как показано в примере, чтобы вернуть значение SQL NULL из функции PL/Perl, передайте значение undef. Это можно сделать независимо от того, имеет ли функция указание STRICT.

Любой аргумент функции, который не является ссылкой, является строкой. Эта строка находится в стандартном внешнем текстовом представлении PostgreSQL для соответствующего типа данных. В случае обычных числовых или текстовых типов Perl обрабатывает их так, как ожидается, и нет необходимости выполнять дополнительные операции. Однако при работе с более сложными типами необходимо преобразовать аргумент в форму, подходящую для Perl. Например, чтобы преобразовать bytea в двоичное значение, используйте функцию decode_bytea.

Значения, передаваемые обратно в PostgreSQL, должны быть во внешнем текстовом формате. Например, чтобы подготовить двоичные данные для возврата в виде значения bytea, используйте функцию encode_bytea. См. Вспомогательные функции.

Преобразование логических типов заслуживает отдельного внимания.

Преобразование значений типа Boolean

По умолчанию логические значения передаются в Perl как текст, то есть как t или f. Вот тут-то и возникает проблема, поскольку Perl не обрабатывает f как false. Чтобы исправить ситуацию, используйте "трансформацию" (transform), предоставляемую расширением bool_plperl (bool_plperlu для PL/PerlU). См. CREATE TRANSFORM.

Сначала необходимо установить расширение bool_plperl или bool_plperlu:

CREATE EXTENSION bool_plperl;
-- или
CREATE EXTENSION bool_plperlu;

Затем используйте атрибут TRANSFORM в функции PL/Perl, которая принимает или возвращает bool, например:

CREATE FUNCTION perl_or(bool, bool) RETURNS bool
TRANSFORM FOR TYPE bool
AS $$
  my ($a, $b) = @_;
  return $a || $b;
$$ LANGUAGE plperl;

При применении этого преобразования Perl получает аргументы bool как 1 или пустое значение, которые отображаются в Perl как true или false. Если функция возвращает результат типа bool, она вернет true или false в зависимости от того, оценивает ли Perl возвращаемое значение как true. Аналогичные преобразования также выполняются для аргументов и результатов SPI-запросов, выполняемых внутри функции.

Массивы

Perl может возвращать массивы PostgreSQL как ссылки на массивы Perl. Например:

CREATE OR REPLACE function return_array()
RETURNS text[][] AS $$
    return [['id " 1','text 1'],['id 2','text 2']];
$$ LANGUAGE plperl;

SELECT return_array();

Результат:

              return_array
------------------------------------------
 {{"id \" 1","text 1"},{"id 2","text 2"}}

Perl передает массив в PostgreSQL как объект, сопоставленный с PostgreSQL::InServer::ARRAY. Этот объект можно рассматривать в качестве ссылки на массив или строки для обратной совместимости с кодом Perl, написанным для версий PostgreSQL ниже 9.1. Результат будет отличаться. В функциях ниже shift возвращает первый элемент массива аргументов функции.

Функция со ссылкой на массив:

CREATE OR REPLACE FUNCTION concat_elem_reference(text[]) RETURNS TEXT AS $$
    my $arg = shift;
    my $result = "";
    return undef if (!defined $arg);

    for (@$arg) {
        $result .= $_;
    }

    return $result;
$$ LANGUAGE plperl;

SELECT concat_elem_reference(ARRAY['PL','/','Perl']);

Результат:

 concat_elem_reference
-----------------------
 PL/Perl

Функция, использующая строку:

CREATE OR REPLACE FUNCTION concat_elem_string(text[]) RETURNS TEXT AS $$
    my $arg = shift;
    my $result = "";
    return undef if (!defined $arg);

    $result .= $arg;

    return $result;
  $$ LANGUAGE plperl;

SELECT concat_elem_string(ARRAY['PL','/','Perl']);

Результат:

 concat_elem_string
--------------------
 {PL,/,Perl}

Многомерные массивы представлены в виде ссылок на массивы ссылок меньшей размерности — это общепринятый способ в Perl.

Составные типы

Аргументы составного типа передаются в функцию как ссылки на хеши. Ключами хеша являются имена атрибутов составного типа.

Следующие таблицы понадобятся для демонстрационных целей:

CREATE TABLE book (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR(50) NOT NULL,
    author_id INT NOT NULL,
    in_stock INTEGER DEFAULT 0);

INSERT INTO book(title, author_id, in_stock) VALUES
    ('Mrs. Dalloway',1,25),
    ('To the Lighthouse',1,13),
    ('To Kill a Mockingbird',2,0),
    ('The Great Gatsby',3,3),
    ('The Lord of the Rings',4,45);

CREATE TABLE author (
  id SERIAL PRIMARY KEY,
  author_name VARCHAR(100) NOT NULL
);

INSERT INTO author(author_name) VALUES
    ('Virginia Woolf'),
    ('Harper Lee'),
    ('F. Scott Fitzgerald'),
    ('J.R.R. Tolkien'),
    ('George Orwell'),
    ('Dan Simmons');

Пример использования аргумента составного типа book:

CREATE FUNCTION arrival(book) RETURNS integer AS $$
    my ($bk) = @_;
    return $bk->{in_stock} + 5;
$$ LANGUAGE plperl;

SELECT title, arrival(book.*) FROM book;

Результат:

         title         | arrival
-----------------------+---------
 Mrs. Dalloway         |      30
 To the Lighthouse     |      18
 To Kill a Mockingbird |       5
 The Great Gatsby      |       8
 The Lord of the Rings |      50

Функция PL/Perl может возвращать результат составного типа, используя тот же подход — возвращать ссылку на хеш, имеющий требуемые атрибуты. Например:

CREATE TYPE test_row AS (book_id integer, title text, author_name text);

CREATE OR REPLACE FUNCTION create_row() RETURNS test_row AS $$
    return {book_id => 1, title => 'Animal Farm', author_name => 'George Orwell'};
$$ LANGUAGE plperl;

SELECT create_row();

Результат:

 book_id |    title    |    author_name
---------+-------------+---------------
       1 | Animal Farm | George Orwell

Столбцы в объявленном типе данных результата, отсутствующие в хеше, будут возвращены как значения NULL.

Аналогично выходные аргументы процедур могут быть возвращены как ссылка на хеш:

CREATE PROCEDURE calculate_cube(INOUT x integer, INOUT y integer) AS $$
    my ($x, $y) = @_;
    return {x => $x * $x * $x, y => $y * $y * $y};
$$ LANGUAGE plperl;

CALL calculate_cube(2, 8);

Результат:

 x |  y
---+-----
 8 | 512

Функции PL/Perl могут возвращать наборы (sets) скалярных или составных типов. Возвращение строк по одной позволяет сократить время подготовки и избежать сохранения всего набора результатов в памяти. Для такого подхода можно использовать return_next, как показано ниже. Обратите внимание, что после последнего return_next следует вызвать return или return undef. return undef предпочтительнее, так как он возвращает одно значение.

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

CREATE OR REPLACE FUNCTION return_int_set(int)
RETURNS SETOF INTEGER AS $$
    foreach (0..$_[0]) {
        return_next($_);
    }
    return undef;
$$ LANGUAGE plperl;

SELECT return_int_set(4);

Результат:

 return_set_int
----------------
              0
              1
              2
              3
              4

Функция, возвращающая набор составных типов:

CREATE OR REPLACE FUNCTION return_composite_set()
RETURNS SETOF test_row AS $$
    return_next({ book_id => 1, title => 'Mrs. Dalloway', author_name => 'Virginia Woolf' });
    return_next({ book_id => 2, title => 'To Kill a Mockingbird', author_name => 'Harper Lee' });
    return_next({ book_id => 3, title => 'The Great Gatsby', author_name => 'F. Scott Fitzgerald' });
    return undef;
$$ LANGUAGE plperl;

SELECT return_composite_set();

Результат:

             return_composite_set
----------------------------------------------
 (1,"Mrs. Dalloway","Virginia Woolf")
 (2,"To Kill a Mockingbird","Harper Lee")
 (3,"The Great Gatsby","F. Scott Fitzgerald")

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

Функция ниже возвращает набор целочисленных значений:

CREATE OR REPLACE FUNCTION return_set_of_int(int)
RETURNS SETOF INTEGER AS $$
    return [0..$_[0]];
$$ LANGUAGE plperl;

SELECT return_set_of_int(3);

Результат:

 return_set_of_int
-------------------
                 0
                 1
                 2
                 3

Функция ниже возвращает набор составных значений типа test_row:

CREATE OR REPLACE FUNCTION return_set_of_composite()
RETURNS SETOF test_row AS $$
    return [
        {book_id => 1, title => 'Mrs. Dalloway', author_name => 'Virginia Woolf'},
        {book_id => 2, title => 'To Kill a Mockingbird', author_name => 'Harper Lee'},
        {book_id => 3, title => 'The Great Gatsby', author_name => 'F. Scott Fitzgerald'}
    ];
$$ LANGUAGE plperl;

SELECT return_set_of_composite();

Результат:

           return_set_of_composite
----------------------------------------------
 (1,"Mrs. Dalloway","Virginia Woolf")
 (2,"To Kill a Mockingbird","Harper Lee")
 (3,"The Great Gatsby","F. Scott Fitzgerald")

Использование STRICT с функциями и процедурами

Если вы хотите использовать указание STRICT, есть несколько вариантов:

  • Добавьте следующий код в начале тела функции:

    use strict;

  • Для временного глобального использования со всеми функциями и процедурами выполните:

    $ SET plperl.use_strict to true

    См. таблицу Конфигурационные параметры, влияющие на PL/Perl. Это повлияет на последующие компиляции функций PL/Perl, но не на функции, уже скомпилированные в текущей сессии.

  • Для постоянного глобального использования установите значение plperl.use_strict в true в поле postgresql.conf, которое находится на вкладке Clusters → кластер ADPG → Services → ADPG → Primary configuration в пользовательском интерфейсе ADCM.

Доступ к базе данных из кода PL/Perl

В этом разделе описываются функции, которые можно использовать для доступа к базе данных из кода PL/Perl.

Получение набора строк

Функция spi_exec_query выполняет команду SQL и возвращает весь набор строк как ссылку на массив ссылок на хеши. Эту команду следует использовать только в том случае, если вы уверены, что набор результатов будет относительно небольшим.

spi_exec_query(query [, <лимит>])

Если указан параметр <лимит> и он больше нуля, spi_exec_query извлекает количество строк не более определенного <лимит>, как если бы запрос включал выражение LIMIT. Без указания <лимит> или если он равен 0, ограничение количества строк опускается.

Следующий код возвращает не более трех строк из таблицы book:

$result = spi_exec_query('SELECT * FROM book', 3);

Если book имеет столбец title, вы можете получить значение из строки с номером i:

$book_title = $result->{rows}[$i]->{title};

Общее количество строк, возвращаемых запросом SELECT, можно получить следующим образом:

$r_number = $rv->{processed}

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

$query = "INSERT INTO book VALUES (6, 'Hyperion', 7, 10)";
$result = spi_exec_query($query);

Получить доступ к статусу команды можно следующим образом:

$st = $result->{status};

Пример целиком выглядит следующим образом:

CREATE OR REPLACE FUNCTION change_letter_case() RETURNS SETOF test_row AS $$
    my $result = spi_exec_query('SELECT book.book_id, book.title, author.author_name FROM book INNER JOIN author ON author.id = book.author_id');
    my $status = $result->{status};
    my $r_number = $result->{processed};
    foreach my $rn (0 .. $r_number - 1) {
        my $row = $result->{rows}[$rn];
            $row->{book_id} += 100 if defined($row->{book_id});
            $row->{title} =~ tr/A-Za-z/a-zA-Z/ if (defined($row->{title}));
        return_next($row);
    }
    return undef;
$$ LANGUAGE plperl;

SELECT * FROM change_letter_case();

Результат:

 book_id |         title         |     author_name
---------+-----------------------+---------------------
     101 | mRS. dALLOWAY         | Virginia Woolf
     102 | tO THE lIGHTHOUSE     | Virginia Woolf
     103 | tO kILL A mOCKINGBIRD | Harper Lee
     104 | tHE gREAT gATSBY      | F. Scott Fitzgerald
     105 | tHE lORD OF THE rINGS | J.R.R. Tolkien

Получение большого набор строк

Используйте функции spi_query и spi_fetchrow вместе для наборов строк, которые могут быть большими, или в случаях, когда вы хотите возвращать строки по мере их извлечения. Обратите внимание, что spi_fetchrow работает только с spi_query.

В приведенном ниже примере осуществляется доступ к файлу с диска в коде, написанном на PL/PerlU. Можно использовать файл /etc/passwd или создать любой другой текстовый файл для использования в этом примере.

Также для этого примера требуется модуль Digest::MD5. Используйте следующую команду для его установки:

  • YUM

  • APT

$ sudo yum install perl-Digest-MD5 -y
$ sudo apt install libdigest-perl-md5-perl -y

В примере генерируется количество строк, указанное входным параметром функции. Тип строк — type1, определенный в том же примере. Поле text создается как дайджест в шестнадцатеричной форме:

CREATE TYPE type1 AS (number INTEGER, text TEXT);

CREATE OR REPLACE FUNCTION lotsa_md5 (INTEGER) RETURNS SETOF type1 AS $$
    use Digest::MD5 qw(md5_hex);
    my $file = '/etc/passwd';
    my $t = localtime;
    elog(NOTICE, "opening file $file at $t" );
    open my $fh, '<', $file # a file access
        or elog(ERROR, "cannot open $file for reading: $!");
    my @words = <$fh>;
    close $fh;
    $t = localtime;
    elog(NOTICE, "closed file $file at $t");
    chomp(@words);
    my $row;
    my $sth = spi_query("SELECT * FROM generate_series(1,$_[0]) AS b(a)");
    while (defined ($row = spi_fetchrow($sth))) {
        return_next({
            number => $row->{a},
            text => md5_hex($words[rand @words])
        });
    }
    return;
$$ LANGUAGE plperlu;

SELECT * FROM lotsa_md5(50);

Результат:

NOTICE:  opening file /etc/passwd at Tue Feb 25 08:42:00 2025
NOTICE:  closed file /etc/passwd at Tue Feb 25 08:42:00 2025
 number |               text
--------+----------------------------------
      1 | d429087cc8a5927d6eac8ec266da4426
      2 | 8bce1016b9caf72f9e981d4c7fb828cd
      3 | 963a084efcda655c5312ff2264f931e7
...

Обычно вызовы spi_fetchrow следует повторять до тех пор, пока не будет возвращено undef, указывающее на то, что больше нет строк для чтения. Курсор, возвращаемый spi_query, автоматически освобождается, когда spi_fetchrow возвращает undef. Если вам не нужно читать все строки, вызовите spi_cursor_close, чтобы освободить курсор.

Использование предварительно подготовленных планов запросов

Функции spi_prepare, spi_query_prepared, spi_exec_prepared и spi_freeplan работают с подготовленными планами запросов.

spi_prepare(<команда>, <типы аргументов>)
spi_query_prepared(<план>, <аргументы>)
spi_exec_prepared(<план> [, <атрибуты>], <аргументы>)
spi_freeplan(<план>)

Функция spi_prepare подготавливает план запроса. Она принимает строку запроса, в которую можно подставить аргументы на пронумерованные места ($1, $2 и т.д.) и список типа string, содержащий типы аргументов:

$plan = spi_prepare('SELECT * FROM book
    WHERE id > $1 AND title = $2','INTEGER', 'TEXT');

Этот план запроса используется вместо строкового запроса в функции spi_exec_prepared. Результат выполнения аналогичен возвращаемому функцией spi_exec_query.

Также вы можете передать план как параметр функции spi_query_prepared, которая возвращает курсор аналогично spi_query. Этот курсор можно передать в spi_fetchrow. Необязательный второй параметр spi_exec_prepared — это ссылка на хеши атрибутов. Единственный поддерживаемый в настоящее время атрибут — limit, который устанавливает максимальное количество строк, возвращаемых запросом.

Преимущество подготовленных планов запросов в том, что можно использовать один план для выполнения нескольких запросов. После того как план станет не нужен, ресурсы, которые он использует, можно освободить с помощью spi_freeplan.

CREATE OR REPLACE FUNCTION init() RETURNS VOID AS $$
$_SHARED{my_plan} = spi_prepare('SELECT * FROM book WHERE in_stock < $1', 'INTEGER');
$$ LANGUAGE plperl;

CREATE OR REPLACE FUNCTION add_books( INTEGER ) RETURNS SETOF book AS $$
return spi_exec_prepared(
$_SHARED{my_plan},
$_[0]
)->{rows};
$$ LANGUAGE plperl;

CREATE OR REPLACE FUNCTION done() RETURNS VOID AS $$
spi_freeplan( $_SHARED{my_plan});
undef $_SHARED{my_plan};
$$ LANGUAGE plperl;

SELECT init();
SELECT add_books(15);
SELECT done();

Результат:

            add_books
---------------------------------
 (2,"To the Lighthouse",1,13)
 (3,"To Kill a Mockingbird",2,0)
 (4,"The Great Gatsby",3,3)

Управление транзакциями

Из кода PL/Perl можно зафиксировать или откатить текущую транзакцию с помощью функций spi_commit и spi_rollback соответственно. Эти функции можно вызывать только в процедуре или анонимном блоке кода (команда DO), вызываемом с верхнего уровня. Обратите внимание, что невозможно запустить команды SQL COMMIT или ROLLBACK через spi_exec_query или аналогичные функции Perl. После завершения транзакции автоматически запускается новая транзакция, поэтому для этой операции нет отдельной функции.

Пример:

CREATE TABLE test (a int);

CREATE PROCEDURE transaction_test1()
LANGUAGE plperl
AS $$
foreach my $i (0..9) {
    spi_exec_query("INSERT INTO test (a) VALUES ($i)");
    if ($i % 2 == 0) {
        spi_commit();
    } else {
        spi_rollback();
    }
}
$$;

CALL transaction_test1();

SELECT a FROM test;

Результат:

 a
---
 0
 2
 4
 6
 8

Для нечетных значений записи отсутствуют, поскольку соответствующие транзакции были отменены.

Вспомогательные функции

Вспомогательные функции, доступные в PL/Perl, перечислены в таблице ниже.

Название Описание

elog(<уровень>, <сообщение>)

Выдает log-сообщение или сообщение об ошибке. Возможные значения: DEBUG, LOG, INFO, NOTICE, WARNING и ERROR. Уровень ERROR вызывает ошибку. Если ошибка не перехватывается кодом Perl, она возвращается в вызывающий запрос, что приводит к отмене текущей транзакции или подтранзакции. Это поведение аналогично использованию команды Perl die. Остальные значения генерируют только сообщения с разными уровнями приоритета. То, передаются ли сообщения определенного приоритета клиенту, или записываются в лог сервера, или и то, и другое, контролируется параметрами конфигурации log_min_messages и client_min_messages. Эти параметры можно указать на вкладке Primary Configuration сервиса ADPG. Разверните ноду ADPG Configurations, в поле postgresql.conf найдите и исправьте значение параметра log_min_messages. Чтобы установить client_min_messages, добавьте его имя и значение в postgresql.conf (см. Конфигурационные параметры).

quote_literal(<строка>)

Оформляет переданную строку для использования в качестве строкового литерала в операторе SQL. Одинарные кавычки (') и обратные слэши (\) удваиваются. Обратите внимание, что quote_literal возвращает undef, если входной параметр — undef. Для случаев когда аргумент может быть undef, функция quote_nullable подойдет больше

quote_nullable(<строка>)

Возвращает переданную строку, заключенную в кавычки, для использования в качестве строкового литерала в операторе SQL. Если аргумент является undef, возвращает NULL без кавычек. Одинарные кавычки (') и обратные слэши (\) удваиваются

quote_ident(<строка>)

Возвращает указанную строку, заключенную в кавычки при необходимости, для использования в качестве идентификатора в строке оператора SQL. Кавычки добавляются, только если это требуется. Например, если строка содержит символы, недопустимые для идентификаторов, или буквы в разных регистрах. Кавычки, имеющиеся в строке, удваиваются

decode_bytea(<строка>)

Возвращает неэкранированные двоичные данные, представленные содержимым указанной строки, которые должны быть закодированы в формате bytea

encode_bytea(<строка>)

Возвращает строку, закодированную в формате bytea

encode_array_literal(<массив>)

encode_array_literal (<массив>, <разделитель>)

Возвращает содержимое указанного массива в виде строки в формате массива (см. Array value input). encode_array_literal возвращает значение аргумента без изменений, если оно не является ссылкой на массив. Если разделитель не указан или равен undef, по умолчанию в качестве разделителя между элементами используется запятая (,)

encode_typed_literal(<значение>, <имя_типа>)

Преобразует переменную Perl в значение типа данных, переданного в качестве второго аргумента, и возвращает строковое представление этого значения. Эта функция обрабатывает правильно вложенные массивы и составные типы

encode_array_constructor(<массив>)

Возвращает содержимое указанного массива в виде строки в формате конструктора массива (см. Array constructors). Отдельные значения заключаются в кавычки с помощью quote_nullable. Функция возвращает значение аргумента, обработанное с помощью quote_nullable, если оно не является ссылкой на массив

looks_like_number(<строка>)

Возвращает значение true, если содержимое указанной строки выглядит как число согласно Perl, в противном случае возвращает false. Возвращает undef, если аргумент — undef. Начальные и конечные пробелы игнорируются. Inf и Infinity считаются числами

is_array_ref(<аргумент>)

Возвращает значение true, если переданный аргумент можно рассматривать как ссылку на массив, то есть это ссылка на ARRAY или PostgreSQL::InServer::ARRAY. В противном случае возвращает false
Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней