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

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

Обзор

PL/Tcl — загружаемый процедурный язык для ADPG/PostgreSQL, который позволяет использовать язык Tcl для написания функций и процедур.

PL/Tcl предлагает большинство возможностей языка C с небольшими ограничениями, а также позволяет использовать обширные библиотеки обработки строк, доступные для Tcl.

Одним из полезных ограничений является то, что код выполняется в контексте безопасности интерпретатора Tcl. В дополнение к ограниченному набору безопасных команд Tcl располагает лишь несколькими командами для доступа к базе данных с использованием SPI и вызовами elog для выдачи сообщений. PL/Tcl не предоставляет возможности взаимодействия с внутренним механизмом сервера базы данных или доступа к ОС с правами серверного процесса PostgreSQL, что возможно в функциях C. Таким образом, непривилегированным пользователям базы данных можно доверять использование этого языка — он не дает им неограниченных полномочий.

Еще одним серьезным ограничением является то, что функции Tcl нельзя использовать для создания функций ввода/вывода для новых типов данных.

Иногда необходимо написать функцию Tcl, выходящую за рамки безопасного Tcl. Например, может понадобиться функция отправки электронных писем. Для обработки этих случаев существует вариант PL/Tcl, называемый PL/TclU (untrusted Tcl, недоверенный Tcl). Это точно такой же язык, за исключением того, что используется полноценный интерпретатор Tcl. PL/TclU должен быть установлен как недоверенный процедурный язык, чтобы только суперпользователи базы данных могли создавать на нем функции. Автору функции PL/TclU необходимо гарантировать, что функция не может быть использована для каких-либо неожиданных действий, поскольку она имеет возможность выполнять любые операции, доступные пользователю с правами администратора базы данных.

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

CREATE EXTENSION pltcl;

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

Аргументы и функции PL/Tcl

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

CREATE FUNCTION <funcname> (<argument-types>) RETURNS <return-type> AS $$
# PL/Tcl function body
$$ LANGUAGE pltcl [STRICT];

Тело функции является частью скрипта Tcl. При вызове функции значения аргументов передаются в скрипт Tcl как переменные с именами 1 …​ n. Результат возвращается из кода Tcl с помощью оператора return.

Функция PL/TclU имеет такой же синтаксис, за исключением того, что язык должен быть указан как pltclu.

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

CREATE FUNCTION calculate_max(integer, integer) RETURNS integer AS $$
    if {$1 > $2} {return $1}
    return $2
$$ LANGUAGE pltcl STRICT;

Если указано STRICT и передается значение null, функция не вызывается, но возвращает результат null.

Если STRICT не указано и значение аргумента равно null, соответствующей переменной $n будет присвоена пустая строка.

Используйте функцию argisnull, чтобы определить, имеет ли определенный аргумент значение null. Чтобы вернуть значение null из функции PL/Tcl, выполните return_null. Это можно сделать независимо от того, указано ли в функции STRICT или нет.

Функцию calculate_max можно переписать, используя argisnull и return_null, чтобы она возвращала аргумент, не содержащий null, если другой аргумент равен null:

CREATE FUNCTION calculate_max(integer, integer) RETURNS integer AS $$
    if {[argisnull 1]} {
        if {[argisnull 2]} { return_null }
        return $2
    }
    if {[argisnull 2]} { return $1 }
    if {$1 > $2} {return $1}
    return $2
$$ LANGUAGE pltcl;

Аргументы составных типов передаются функциям как Tcl arrays. Имена элементов массива являются именами атрибутов составного типа. Если атрибут в строке имеет значение null, он не отображается в массиве. В следующем примере используется массив:

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 FUNCTION is_in_stock(book) RETURNS boolean AS $$
    if {$1(in_stock)> 0} {
        return "t"
    }
    return "f"
$$ LANGUAGE pltcl;

SELECT book_id, title, is_in_stock(b.*) FROM book b;

Результат:

 book_id |         title         | is_in_stock
---------+-----------------------+-------------
       1 | Mrs. Dalloway         | t
       2 | To the Lighthouse     | t
       3 | To Kill a Mockingbird | f
       4 | The Great Gatsby      | t
       5 | The Lord of the Rings | t

Функции PL/Tcl также могут возвращать результаты составного типа. Для этого код Tcl должен вернуть список пар имя/значение столбца, соответствующих ожидаемому типу результата. Любые имена столбцов, опущенные в списке, возвращают значение null, а при наличии неожидаемых имен столбцов возникает ошибка. Пример:

CREATE FUNCTION sum_prod(x int, y int, out sum int, out prod int) AS $$
    return [list sum [expr {$1 + $2}] prod [expr {$1 * $2}]]
$$ LANGUAGE pltcl;

SELECT sum_prod(2,6);

Результат:

 sum_prod
----------
 (8,12)

Таким же образом можно вернуть выходные параметры процедур, например:

CREATE PROCEDURE calculate_cube(INOUT x integer, INOUT y integer) AS $$
     return [list x [expr {$1 * $1 * $1}] y [expr {$2 * $2 * $2} ]]
$$ LANGUAGE pltcl;

CALL calculate_cube(2, 15);

Результат:

 x |  y
---+------
 8 | 3375

Вы можете использовать команду Tcl array get для создания списка результатов из массива:

CREATE FUNCTION arrival(book, amount int) RETURNS book AS $$
    set 1(in_stock) [expr {$1(in_stock) + $2}]
    return [array get 1]
$$ LANGUAGE pltcl;

SELECT arrival(book::book, 6) FROM book;

Результат:

             arrival
----------------------------------
 (1,"Mrs. Dalloway",1,31)
 (2,"To the Lighthouse",1,19)
 (3,"To Kill a Mockingbird",2,6)
 (4,"The Great Gatsby",3,9)
 (5,"The Lord of the Rings",4,51)

Функции PL/Tcl могут возвращать наборы результатов. Для этого нужно вызывать return_next один раз для каждой строки, чтобы вернуть подходящее значение для скалярного типа или список пар имя/значение столбца для составного типа. Следующая функция возвращает набор скалярного типа:

CREATE FUNCTION incr_sequence(start_seq int, end_seq int) RETURNS SETOF int AS $$
    for {set i $1} {$i <= $2} {incr i} {
    return_next $i
}
$$ LANGUAGE pltcl;

SELECT incr_sequence(8, 14);

Результат:

 incr_sequence
---------------
             8
             9
            10
            11
            12
            13
            14

Пример ниже возвращает набор составного типа:

CREATE FUNCTION cube_sequence(start_seq int, end_seq int) RETURNS TABLE ( x int, x_cube int) AS $$
for {set i $1} {$i <= $2} {incr i} {
return_next [list x $i x_cube [expr {$i * $i * $i}]]
}
$$ LANGUAGE pltcl;

SELECT cube_sequence(8, 14);

Результат:

 cube_sequence
---------------
 (8,512)
 (9,729)
 (10,1000)
 (11,1331)
 (12,1728)
 (13,2197)
 (14,2744)
ПРИМЕЧАНИЕ
Значения аргументов, передаваемые функции PL/Tcl, представляют собой входные аргументы, преобразованные в текст. Команды return и return_next принимают любую строку, которая соответствует формату типа результата объявленной функции или указанного столбца в результате составного типа.

Глобальные данные

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

По соображениям безопасности PL/Tcl выполняет функции, вызываемые определенной ролью SQL, в отдельном интерпретаторе Tcl для этой роли. Это предотвращает случайное или злонамеренное вмешательство одного пользователя в поведение функций PL/Tcl другого пользователя. Каждый такой интерпретатор будет иметь свои значения для любых глобальных переменных Tcl. Таким образом, две функции PL/Tcl будут использовать одни и те же глобальные переменные, только если они выполняются одной и той же ролью SQL. В приложении, в котором в одной сессии выполняется код под несколькими ролями SQL (с помощью SECURITY DEFINER или SET ROLE и т.д.), может потребоваться предпринять явные шаги, чтобы гарантировать, что функции PL/Tcl могут совместно использовать данные. Для этого убедитесь, что функции, которые должны взаимодействовать, принадлежат одному и тому же пользователю, и задайте для них параметр SECURITY DEFINER. См. CREATE FUNCTION.

Все функции PL/TclU, используемые в сессии, выполняются в одном и том же интерпретаторе Tcl, который отличается от интерпретаторов, используемых для функций PL/Tcl. Таким образом, глобальные данные автоматически распределяются между функциями PL/TclU. Это не считается угрозой безопасности, поскольку все функции PL/TclU выполняются на одном и том же уровне доверия, на уровне суперпользователя базы данных.

Чтобы защитить функции PL/Tcl от непреднамеренного влияния друг на друга, каждой функции предоставляется глобальная переменная — массив с помощью команды upvar. Глобальное имя этой переменной — внутреннее имя функции, а локальное имя — GD. Рекомендуется использовать GD для постоянных внутренних данных функции. Задействуйте обычные глобальные переменные Tcl только для значений, которые будут использоваться совместно несколькими функциями. Обратите внимание, что массивы GD являются глобальными только внутри конкретного интерпретатора, поэтому они не обходят ограничения безопасности, упомянутые выше.

Пример использования GD можно найти в описании функции spi_execp.

Обращение к базе данных из PL/Tcl

Ниже приведены команды, используемые при обращении к базе данных из тела функции PL/Tcl. В этом разделе, в соответствии с соглашением Tcl, вопросительные знаки (?) заменяют квадратные скобки ([]), используемые обычно для обозначения необязательных элементов в синтаксическом описании.

spi_exec

Выполняет команду SQL, заданную в виде строки. Ошибка в SQL-команде приводит к возникновению ошибки. Возвращаемое значение spi_exec — это количество строк, обработанных (выбранных, вставленных, обновленных или удаленных) командой, или ноль, если команда является служебным оператором. Кроме того, если команда представляет собой оператор SELECT, значения выбранных столбцов помещаются в переменные Tcl, как описано ниже.

spi_exec ?-count <n>? ?-array <имя>? <команда> ?<тело_цикла>?

Где:

  • <команда> — SQL-команда.

  • count <n> — необязательный параметр, который определяет, что выполнение spi_exec должно остановиться сразу после того, как <n> строк были получены, как если бы запрос включал предложение LIMIT. Если <n> равно нулю, запрос выполняется до завершения, так же как и в случае, когда -count не задан.

  • array <имя> — необязательный параметр, используется если SQL-команда представляет собой оператор SELECT. Когда он задан, значения столбцов сохраняются в элементах именованного ассоциативного массива, а имена столбцов используются в качестве индексов массива. Текущий номер строки в результате (считая с нуля) сохраняется в элементе массива с именем tupno, если это имя не используется в качестве имени столбца. Если array не указан, значения столбцов результатов помещаются в переменные Tcl, названные по именам столбцов.

  • <тело_цикла> — необязательный параметр, определяет фрагмент скрипта Tcl, который выполняется один раз для каждой строки результата запроса. Если команда представляет собой оператор SELECT и параметр <тело_цикла> не указан, то в переменных Tcl или элементах массива сохраняется только первая строка результатов, остальные строки, если таковые имеются, игнорируются. Сохранение не происходит, если запрос не возвращает строк. Если SQL-команда не SELECT, <тело_цикла> игнорируется.

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

CREATE OR REPLACE FUNCTION book_count() RETURNS int AS $$

    spi_exec "SELECT count(*) AS count_books FROM book"

return $count_books

$$ LANGUAGE pltcl;

SELECT book_count();

Результат:

 book_count
------------
          5

Функция ниже возвращает строку, в которой перечислены книги определенного автора. Значения столбцов текущей строки сохраняются в массиве res перед каждой итерацией:

CREATE OR REPLACE FUNCTION books_by_author() RETURNS text AS $$
    set book_result " Virginia Woolf: "
    set counter 1
    spi_exec -array res "SELECT * FROM book WHERE author_id=1" {
        if {$counter > 1} { append book_result ", " }
        append book_result $res(title)
        incr counter
        }

    return $book_result
$$ LANGUAGE pltcl;

SELECT books_by_author();

Результат:

                  books_by_author
---------------------------------------------------
  Virginia Woolf: Mrs. Dalloway, To the Lighthouse

spi_prepare

Функция spi_prepare подготавливает и сохраняет план запроса для последующего выполнения. Сохраненный план доступен во время текущей сессии.

spi_prepare <запрос> <список_типов>

Где:

  • <запрос> — SQL-запрос;

  • <список_типов> — список типов параметров.

Строка запроса, обрабатываемого spi_prepare, поддерживает параметры. Вы можете использовать порядковые номера $1 …​ $n для ссылки на параметры в строке запроса. Типы параметров должны быть заданы в виде списка Tcl. Например, list int4 int4. Используйте пустой список в качестве списка типов, если параметры не используются в запросе.

Функция spi_prepare возвращает идентификатор запроса, который будет использоваться в последующих вызовах функции spi_execp, описанной ниже.

spi_execp

Функция spi_execp выполняет запрос, предварительно подготовленный с помощью spi_prepare.

spi_execp ?-count <n>? ?-array <имя>? ?-nulls <строка>? <ID_запроса> ?<лист_значений>? ?<тело_цикла>?

Где:

  • <ID_запроса> — идентификатор, возвращаемый spi_prepare.

  • count <n> — необязательный параметр, который определяет, что выполнение spi_execp должно остановиться сразу после того, как n строк были получены. Если n равно нулю, запрос выполняется до завершения, так же как и в случае, когда -count не задан.

  • array <имя> — необязательный параметр, используемый если SQL-команда представляет собой оператор SELECT. Когда он задан, значения столбцов сохраняются в элементах именованного ассоциативного массива, а имена столбцов используются в качестве индексов массива. Текущий номер строки в результате (считая с нуля) сохраняется в элементе массива с именем tupno, если это имя не используется в качестве имени столбца. Если array не указан, значения столбцов результатов помещаются в переменные Tcl, названные по именам столбцов.

  • nulls <строка> — необязательный параметр, указывающий, какие из параметров имеют нулевые значения. <строка> — это последовательность символов, состоящая из пробелов и букв n для параметров, имеющих нулевые значения. Строка должна иметь ту же длину, что и список значений. Если nulls не указан, считается, что все значения параметров отличны от null.

  • <лист_значений> — Tcl-список (list) фактических значений параметров запроса. <лист_значений> должен иметь ту же длину, что и список типов параметров, ранее переданный spi_prepare. Если запрос не имеет параметров, <лист_значений> указывать не нужно.

  • <тело_цикла> — необязательный параметр, определяющий фрагмент скрипта Tcl, который выполняется один раз для каждой строки результата запроса. Если команда представляет собой оператор SELECT и параметр <тело_цикла> не указан, то в переменных Tcl или элементах массива сохраняется только первая строка результатов, остальные строки, если таковые имеются, игнорируются. Сохранение не происходит, если запрос не возвращает строк. Если SQL-команда не SELECT, <тело_цикла> игнорируется.

За исключением способа указания запроса и его параметров, spi_execp работает так же, как spi_exec.

Следующая функция вычисляет количество строк, в которых значение столбца aid больше или равно первому параметру функции rows_count, но меньше или равно второму параметру. В примере используется таблица pgbench_accounts, созданная с помощью pgbench:

CREATE FUNCTION rows_count(integer, integer) RETURNS integer AS $$
    if {![ info exists GD(plan) ]} {
    # подготовка плана при первом вызове
        set GD(plan) [ spi_prepare "SELECT count(*) AS row_number FROM pgbench_accounts WHERE aid >= \$1 AND aid <= \$2" [ list int4 int4 ] ]
    }
    spi_execp -count 1 $GD(plan) [ list $1 $2 ]
    return $row_number

$$ LANGUAGE pltcl;

SELECT rows_count(28,3678);

Результат:

 rows_count
------------
       3651

Обратите внимание, что обратные слэши внутри строки запроса, заданной для spi_prepare, гарантируют, что маркеры $n будут переданы как есть и не будут заменены подстановкой переменных Tcl.

quote

Функция quote удваивает все вхождения одинарных кавычек и обратных слэшей в заданной строке. Этот функционал можно использовать для защиты строк, которые будут переданы в SQL-команды, обрабатываемые spi_exec или spi_prepare:

quote <строка>

Пример:

"SELECT '[ quote $val ]' AS ret"

В результате, например, SELECT 'It doesn’t true' AS ret будет трансформировано в SELECT 'It doesn''t true' AS ret.

subtransaction

Функция subtransaction выполняет указанную команду в рамках подтранзакции SQL.

subtransaction <команда>

Где <команда> — скрипт Tcl.

Если скрипт Tcl возвращает ошибку, вся подтранзакция откатывается перед возвратом ошибки в окружающий код.

Пример:

CREATE TABLE accounts(
  acc_number integer PRIMARY KEY,
  balance numeric
);

CREATE TABLE transfer_logs(
  log_id SERIAL PRIMARY KEY,
  result text
);

INSERT INTO accounts VALUES
    (1234,1000.00),
    (4321,2000.00);

CREATE FUNCTION account_transfers() RETURNS void AS $$
    if [catch {
        spi_exec "UPDATE accounts SET balance = balance - 1000 WHERE acc_number = '1234'"
        spi_exec "UPDATE accounts SET balance = balance + 1000 WHERE acc_number = '4321'"
    } errormsg] {
        set result [format "The transfer was canceled: %s" $errormsg]
    } else {
        set result "The transfer is successful"
}
    spi_exec "INSERT INTO transfer_logs (result) VALUES ('[quote $result]')"
$$ LANGUAGE pltcl;

SELECT account_transfers();
SELECT * FROM accounts;

Результат:

 acc_number | balance
------------+---------
       1234 |    0.00
       4321 | 3000.00

Для получения дополнительной информации обратитесь к статье Explicit subtransactions in PL/Tcl.

elog

Функция elog выдает log-сообщение или сообщение об ошибке.

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

Где:

  • <уровень> — уровень приоритета сообщения. Возможные значения: DEBUG, LOG, INFO, NOTICE, WARNING, ERROR и FATAL.

  • <сообщение> --текст сообщения.

Пример:

elog DEBUG "The database contains table $table_name"

Уровень ERROR вызывает ошибку. Если ошибка не перехватывается кодом Tcl, она возвращается в вызывающий запрос, что приводит к отмене текущей транзакции или подтранзакции. Это такое же поведение, к которому приводит использование команды Tcl error. FATAL прерывает транзакцию и завершает текущую сессию. Вероятно, нет веской причины использовать этот уровень ошибок в функциях PL/Tcl, но он предусмотрен для полноты соответствия. Остальные уровни приоритета генерируют только сообщения с разными уровнями приоритета.

То, передаются ли сообщения определенного приоритета клиенту, или записываются в лог сервера, или и то, и другое, контролируется параметрами конфигурации log_min_messages и client_min_messages. Эти параметры можно указать на вкладке Primary Configuration сервиса ADPG. Разверните ноду ADPG Configurations, в поле postgresql.conf найдите и исправьте значение параметра log_min_messages. Чтобы установить client_min_messages, добавьте его имя и значение в postgresql.conf (см. Конфигурационные параметры). За дополнительной информацией об обработке ошибок обратитесь к статье Error handling in PL/Tcl.

Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней