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

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

Обзор

Процедурный язык PL/Python позволяет писать функции и процедуры PostgreSQL на языке Python. ADPG поддерживает версию языка Python 3.

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

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

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

Функции PL/Python

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

CREATE FUNCTION function_name (argument-list)
  RETURNS return-type
AS $$
  # тело функции PL/Python
$$ LANGUAGE plpython3u;

Тело функции представляет собой скрипт Python. При вызове функции её аргументы передаются как элементы списка args. Именованные аргументы также передаются в сценарий Python как обычные переменные. Именованные аргументы делают код более читабельным. Результат возвращается из кода Python с помощью return или yield (в случае функции, возвращающей множество). Если вы не укажете возвращаемое значение, Python вернет значение по умолчанию — None. PL/Python преобразует None в значение SQL NULL. В процедуре результат выполнения кода Python должен быть None. Для этого вы можете завершить процедуру без оператора return или использовать return без аргументов. В противном случае произойдет ошибка.

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

CREATE FUNCTION get_max (a integer, b integer)
  RETURNS integer
AS $$
  if a > b:
    return a
  return b
$$ LANGUAGE plpython3u;

SELECT get_max(28,3);

Результат:

 get_max
----------
       28

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

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

CREATE FUNCTION fn_lower (str text)
RETURNS text
AS $$
    str = str.lower() # ошибка
    return str
$$ LANGUAGE plpython3u;

Функция будет создана, но вернет ошибку: ERROR: UnboundLocalError: local variable 'str' referenced before assignment.

Добавьте определение global переменной str, чтобы решить проблему:

CREATE FUNCTION fn_lower (str text)
  RETURNS text
AS $$
  global str
  str = str.lower()
  return str
$$ LANGUAGE plpython3u;

SELECT fn_lower('TEsT STRinG');

Результат:

  fn_lower
-------------
 test string

Однако рекомендуется не полагаться на такие детали реализации PL/Python, а подразумевать, что параметры функции доступны только для чтения.

Типы данных

Когда вызывается функция PL/Python, её аргументы преобразуются из типа данных PostgreSQL в соответствующий тип Python. В таблице ниже приведены сопоставления типов аргументов функции.

Сопоставление типов аргументов
Тип ADPG/PostgreSQL Тип Python

Boolean

Bool

Smallint, int, bigint, oid

Int

Real, double

Float

Numeric

Decimal

Bytea

Bytes

Null

None. См. раздел NULL → None

Array

List. См. раздел Arrays → Lists

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

Аргументы составного типа передаются в функцию как сопоставления Python (mappings). См. раздел Составные типы

Остальные типы данных, включая типы символьных строк PostgreSQL

Str (строка в Unicode, как и все строки Python)

Возвращаемое значение функции PL/Python преобразуется в объявленный тип данных PostgreSQL с использованием правил, описанных в таблице ниже.

Преобразование типа возвращаемого значения
Возвращаемое значение ADPG/PostgreSQL Правила конвертации

Boolean

Возвращаемое значение будет оцениваться в соответствии с правилами Python. Для 0 и пустой строки будет возвращено значение false, но обратите внимание, что для f будет возвращено значение true

Bytea

Возвращаемое значение будет преобразовано в тип Python bytes с использованием встроенных средств Python, а затем преобразовано в тип bytea

Null

None. См. раздел NULL → None

Array

List. См. раздел Arrays → Lists

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

Результат составного типа может быть возвращен в виде последовательности, сопоставления или объекта. Вы также можете использовать функцию с параметрами OUT. См. раздел Составные типы

Другие типы возвращаемых результатов PostgreSQL

Возвращаемое значение преобразуется в строку с помощью встроенной функции Python str, а результат передается во входную функцию типа данных PostgreSQL. Если значение Python имеет тип float, оно преобразуется с использованием repr вместо str, чтобы избежать потери точности. При передаче в PostgreSQL строки преобразуются в кодировку сервера PostgreSQL

Информация о нескалярных типах данных приведена ниже.

Обратите внимание, что логические несоответствия между объявленным типом возвращаемого значения в PostgreSQL и возвращаемым типом данных Python игнорируются — значение все равно будет преобразовано.

NULL → None

Если функции SQL передается значение NULL, значение аргумента в Python будет отображаться как None. Вы можете добавить STRICT в определение функции, чтобы ADPG/PostgreSQL возвращал результат NULL, если значение NULL передается в качестве параметра. В этом случае функция не вызывается. В качестве альтернативы можно проверить наличие значений NULL в теле функции:

CREATE FUNCTION get_max (a integer, b integer)
  RETURNS integer
AS $$
  if (a is None) or (b is None):
    return None
  if a > b:
    return a
  return b
$$ LANGUAGE plpython3u;

Следующий код вернет NULL:

SELECT get_max(NULL, 5);

Arrays → Lists

Значения массива SQL (array) передаются в PL/Python в виде списка Python (list). Чтобы вернуть значение массива SQL из функции PL/Python, верните список Python:

CREATE FUNCTION get_array()
RETURNS char[]
AS $$
return ['a', 'b', 'c', 'd', 'e']
$$ LANGUAGE plpython3u;

SELECT get_array();

Результат:

  get_array
-------------
 {a,b,c,d,e}

Многомерные массивы передаются в PL/Python как вложенные списки Python. Например, двумерный массив представляет собой список списков. При возврате многомерного массива SQL из функции PL/Python все внутренние списки на каждом уровне должны быть одинакового размера.

CREATE FUNCTION get_two_dim_array(x int4[]) RETURNS int4[] AS $$
plpy.info(x, type(x))
return x
$$ LANGUAGE plpython3u;

SELECT * FROM get_two_dim_array(ARRAY[[1,2,3],[11,12,13],[21,22,23]]);

Результат:

INFO:  ([[1, 2, 3], [11, 12, 13], [21, 22, 23]], <class 'list'>)

        get_two_dim_array
---------------------------------
 {{1,2,3},{11,12,13},{21,22,23}}

Другие последовательности Python, например кортежи (tuples), также принимаются для обратной совместимости с PostgreSQL версии 9.6 и ниже. Однако они всегда рассматриваются как одномерные массивы, чтобы не возникало неоднозначности с составными типами. По той же причине, когда составной тип используется в многомерном массиве, он должен быть представлен кортежем, а не списком.

Обратите внимание, что в Python строки представляют собой последовательности, что может иметь нежелательные последствия:

CREATE FUNCTION get_str_arr()
  RETURNS varchar[]
AS $$
return "test"
$$ LANGUAGE plpython3u;

SELECT get_str_arr();

Результат:

 get_str_arr
-------------
 {t,e,s,t}

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

Аргументы составного типа передаются в функцию как сопоставления Python (mappings). Имена атрибутов составного типа являются именами элементов сопоставления. Пример:

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

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

CREATE FUNCTION sufficient_in_stock(b book)
  RETURNS boolean
AS $$
  if b["in_stock"] >= 10 :
    return True
  return False
$$ LANGUAGE plpython3u;

SELECT book_id, title, sufficient_in_stock(b.*) AS sufficient
 FROM book b;

Результат:

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

Если атрибут имеет значение NULL в переданной строке, в сопоставлении он будет иметь значение None.

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

CREATE TYPE named_value AS (
  name   text,
  value  integer
);

Составной результат может быть возвращен одним из следующих вариантов.

Последовательность (кортеж или список)

 

Возвращенный объект последовательности должен содержать то же количество элементов, что и составной тип результата. Элемент с индексом 0 присваивается первому полю составного типа, 1 — второму и так далее. Например:

CREATE FUNCTION create_named_value1 (name text, value integer)
    RETURNS named_value
    AS $$
    return ( name, value )
#   return [ name, value ] -- также можно вернуть список
$$ LANGUAGE plpython3u;

SELECT create_named_value1('name1', 1);

Результат:

 create_named_value1
---------------------
 (name1,1)

Чтобы вернуть значение SQL NULL для любого столбца, вставьте None в соответствующую позицию.

Когда возвращается массив составных типов, его нельзя вернуть в виде списка, поскольку неясно, представляет ли список Python составной тип или другое измерение массива.

Составной тип не может быть множеством Python (set), поскольку он не индексируем.

Сопоставление (словарь)

 

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

CREATE FUNCTION create_named_value2 (name text, value integer)
  RETURNS named_value
AS $$
  return { "name": name, "value": value }
$$ LANGUAGE plpython3u;

SELECT create_named_value2('name2',2);

Результат:

 create_named_value2
---------------------
 (name2,2)

Любые дополнительные пары ключ/значение игнорируются. Отсутствие ключа вызывает ошибку. Чтобы вернуть значение SQL NULL для любого столбца, вставьте None с соответствующим именем столбца в качестве ключа.

Объект (любой объект с методом getattr)

 

Это работает так же, как сопоставление. Пример:

CREATE FUNCTION create_named_value3 (name text, value integer)
  RETURNS named_value
AS $$
  class named_value:
    def __init__ (self, n, v):
      self.name = n
      self.value = v
  return named_value(name, value)
$$ LANGUAGE plpython3u;

Также функцию можно переписать следующим образом:

CREATE FUNCTION create_named_value3 (name text, value integer)
  RETURNS named_value
AS $$
  class nv: pass
  nv.name = name
  nv.value = value
  return nv
$$ LANGUAGE plpython3u;

SELECT create_named_value3('name3',3);

Результат:

 create_named_value3
---------------------
 (name3,3)
Параметры OUT

 

Можно использовать функции с выходными параметрами:

CREATE FUNCTION return_out_params(OUT id integer, OUT name text ) AS $$
    return (1, "text1")
$$ LANGUAGE plpython3u;

SELECT * FROM return_out_params();

Результат:

 id | name
----+-------
  1 | text1

Выходные параметры процедур передаются таким же образом:

CREATE PROCEDURE calculate_cube (INOUT x integer, INOUT y integer) AS $$
    return (pow(x, 3), pow(y, 3))
$$ LANGUAGE plpython3u;

CALL calculate_cube(2, 5);

Результат:

 x |  y
---+-----
 8 | 125

Функции, возвращающие множества

Функция PL/Python также может возвращать наборы скалярных или составных типов. Существует несколько способов осуществить это, поскольку возвращаемый объект внутри превращается в итератор. В примерах ниже используется следующий составной тип:

CREATE TYPE author_book AS (
  author text,
  book text
);

Множество в качестве результата может быть возвращено из типа последовательности, итератора или генератора. Также поддерживаются функции возврата набора с параметрами OUT.

Примеры

Последовательность (кортеж, список, множество)

 

CREATE FUNCTION get_books(author text)
  RETURNS SETOF author_book
AS $$
  # return tuple containing lists as composite types
   return ( [ author, "War and Peace" ], [ author, "Anna Karenina" ], [ author, "The Kreutzer Sonata" ] )
$$ LANGUAGE plpython3u;

SELECT get_books('Tolstoy');

Результат:

            get_books
---------------------------------
 (Tolstoy,"War and Peace")
 (Tolstoy,"Anna Karenina")
 (Tolstoy,"The Kreutzer Sonata")
Итератор

 

Функция должна возвращать объект, имеющий методы __iter__ и __next__.

CREATE FUNCTION get_books1(author text)
  RETURNS SETOF author_book
AS $$
  class producer:
    def __init__ (self, author, book):
      self.author = author
      self.book = book
      self.ndx = -1

    def __iter__ (self):
      return self

    def __next__ (self):
      self.ndx += 1
      if self.ndx == len(self.book):
        raise StopIteration
      return ( self.author, self.book[self.ndx] )

  return producer(author, [ "War and peace", "Anna Karenina", "The Kreutzer sonata" ])
$$ LANGUAGE plpython3u;

SELECT get_books1('Tolstoy');

Результат:

           get_books1
---------------------------------
 (Tolstoy,"War and peace")
 (Tolstoy,"Anna Karenina")
 (Tolstoy,"The Kreutzer sonata")
Генератор (yield)

 

CREATE FUNCTION get_books2 (author text)
  RETURNS SETOF author_book
AS $$
  for book in ["War and peace", "Anna Karenina", "The Kreutzer sonata"]:
    yield (author, book)
$$ LANGUAGE plpython3u;

SELECT get_books2('Tolstoy');

Результат:

           get_books2
---------------------------------
 (Tolstoy,"War and peace")
 (Tolstoy,"Anna Karenina")
 (Tolstoy,"The Kreutzer sonata")
Функция, возвращающая множества, с параметрами OUT

 

CREATE FUNCTION multiout_simple_setof(n integer, OUT integer, OUT integer) RETURNS SETOF record AS $$
    return [(1, 2)] * n
$$ LANGUAGE plpython3u;

SELECT * FROM multiout_simple_setof(3);

Результат:

 column1 | column2
---------+---------
       1 |       2
       1 |       2
       1 |       2

Анонимные блоки кода

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

DO $$
# PL/Python code
$$ LANGUAGE plpython3u;

Пример:

DO $$
plpy.notice('This code is executed inside the Do block')
$$ LANGUAGE plpython3u;

Результат:

NOTICE:  This code is executed inside the Do block
DO

Совместное использование данных

PL/Python предоставляет глобальный словарь SD для хранения данных между повторными вызовами одной и той же функции. У него также есть еще один глобальный словарь — GD для общедоступных данных, который доступен всем функциям Python в сессии.

Каждая функция получает свою собственную среду выполнения в интерпретаторе Python, поэтому глобальные данные и аргументы одной функции недоступны для другой. Исключением являются данные словаря GD, о котором говорилось выше.

Доступ к базе данных

Модуль языка PL/Python автоматически импортирует модуль Python под названием plpy. Функции и константы в этом модуле доступны в коде Python как plpy.<имя_функции>.

Модуль plpy предоставляет несколько функций для выполнения запросов к базе данных.

execute

Команда plpy.execute выполняет заданный запрос с необязательным параметром лимит и возвращает объект результата.

plpy.execute(<запрос> [, <лимит>])

Если указан лимит и он больше 0, plpy.execute возвращает количество строк, не превышающих лимит, как если бы запрос включал выражение LIMIT. Если вы опустите лимит или установите для него значение 0, лимит строк не будет установлен.

Например, следующий код возвращает до 3 строк из таблицы book.

CREATE FUNCTION access_db() RETURNS SETOF book AS $$
    rows=plpy.execute("SELECT * FROM book", 3)
    return rows
$$ LANGUAGE plpython3u;

SELECT * FROM access_db();

Pезультат:

 book_id |         title         | author_id | in_stock
---------+-----------------------+-----------+----------
       1 | Mrs. Dalloway         |         1 |       25
       2 | To the Lighthouse     |         1 |       13
       3 | To Kill a Mockingbird |         2 |        2

Объект результата эмулирует объект списка (list) или словаря (dictionary). Доступ к значениям можно получить по номеру строки и имени столбца. Например, вы можете получить доступ к значениям столбца title следующим образом:

col = rows[i]["title"]

Вы можете использовать встроенную функцию len для получения количества возвращаемых строк.

Объект результата также имеет дополнительные методы, перечисленные в таблице ниже.

Метод Описание

nrows()

Возвращает количество строк, обработанных командой. Обратите внимание, что это не обязательно то же самое, что количество возвращаемых строк. Например, команда UPDATE устанавливает это значение, но не возвращает никаких строк (если не используется RETURNING)

status()

Возвращаемое значение SPI_execute()

colnames()

Возвращает список имен столбцов

coltypes()

Возвращает список OID типов столбцов

coltypmods()

Возвращает список модификаторов типа столбцов

__str__()

Стандартный метод Python __str__(), который возвращает строковое представление объекта. Таким образом, можно, например, отслеживать результаты выполнения запроса с помощью plpy.debug(row)

Методы colnames(), coltypes() и coltypmods() вызывают исключение при вызове у объекта результата выполнения команды, которая не создала набор результатов, например UPDATE без RETURNING или DROP TABLE. Однако вы можете использовать эти методы для набора результатов, содержащего ноль строк.

РЕКОМЕНДАЦИЯ
Вызов plpy.execute приводит к чтению всего набора результатов в память. Используйте эту функцию только в том случае, если уверены, что набор результатов будет относительно небольшим. Если вы не хотите рисковать чрезмерным использованием памяти при получении больших результатов, используйте plpy.cursor вместо plpy.execute.

prepare and execute

Функция plpy.prepare подготавливает план выполнения запроса. Она вызывается со строкой запроса и списком типов параметров, если в запросе есть ссылки на параметры. Например:

plan = plpy.prepare("SELECT in_stock FROM book WHERE title = $1", ["text"])

text — это тип переменной, которая будет передана как $1.

После prepare используйте функцию plpy.execute для выполнения плана запроса. Передайте план в качестве первого аргумента (вместо строки запроса), а список значений параметров в запросе — в качестве второго аргумента. Его можно опустить, если запрос не предполагает никаких параметров.

row = plpy.execute(plan, ["To the Lighthouse"])

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

В качестве альтернативы вы можете вызвать метод execute объекта plan:

row = plan.execute(["To the Lighthouse"])

Когда план выполнения создается с помощью модуля PL/Python, он автоматически сохраняется. Для получения дополнительной информации обратитесь к статье Server Programming Interface. Чтобы эффективно использовать это при вызове нескольких функций, вам может потребоваться задействовать один из словарей постоянного хранилища SD или GD. Например:

CREATE FUNCTION use_plan(min_value int) RETURNS SETOF book AS $$
    if "plan" in SD:
        plan = SD["plan"]
    else:
        plan = plpy.prepare("SELECT * FROM book WHERE in_stock >=$1", ["int"])
        SD["plan"] = plan
    rows = plpy.execute(plan, [min_value])
    plpy.notice(rows.colnames()) # вывод названий столбцов
    total_books=0
    for i, elem in enumerate(rows):
        plpy.notice(rows[i]["title"]) # вывод названий книг
        total_books = total_books + rows[i]["in_stock"]
    plpy.notice("total books: " + str(total_books)) # вывод общего количества книг
    return rows
$$ LANGUAGE plpython3u;

SELECT * from use_plan(10);

Результат:

NOTICE:  ['book_id', 'title', 'author_id', 'in_stock']
NOTICE:  Mrs. Dalloway
NOTICE:  To the Lighthouse
NOTICE:  The Lord of the Rings
NOTICE:  total books: 83
 book_id |         title         | author_id | in_stock
---------+-----------------------+-----------+----------
       1 | Mrs. Dalloway         |         1 |       25
       2 | To the Lighthouse     |         1 |       13
       5 | The Lord of the Rings |         4 |       45

В приведенном выше примере метод plpy.notice используется для отображения информации внутри и за пределами тела цикла в демонстрационных целях. Обычно этот метод вызывают для создания log-сообщений PostgreSQL. См. Utility functions.

cursor

Функция plpy.cursor принимает те же аргументы, что и plpy.execute (за исключением ограничения на количество строк), и возвращает объект курсора, что позволяет обрабатывать большие наборы результатов меньшими порциями. Как и в случае с plpy.execute, можно использовать строку запроса или объект плана со списком аргументов.

plpy.cursor(query)

plpy.cursor(plan [, arguments])

Функцию cursor можно также вызвать как метод объекта плана. Ниже приведен пример, в котором, как и в других примерах в этом разделе, используется таблица pgbench_accounts, созданная с помощью утилиты pgbench.

CREATE FUNCTION count_odd_prepared() RETURNS integer AS $$
    odd = 0
    plan = plpy.prepare("SELECT aid FROM pgbench_accounts WHERE aid % $1 <> 0", ["integer"])
    rows = list(plpy.cursor(plan, [2]))
#   rows = list(plan.cursor([2])) -- вызов cursor как метода объекта плана запроса
    return len(rows)
$$ LANGUAGE plpython3u;

SELECT count_odd_prepared();

Результат:

 count_odd_prepared
--------------------
            2500000

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

CREATE FUNCTION count_odd_fetch(batch_size integer) RETURNS integer AS $$
odd = 0
cursor = plpy.cursor("SELECT aid FROM pgbench_accounts")
while True:
    rows = cursor.fetch(batch_size)
    if not rows:
        break
    for row in rows:
        if row['aid'] % 2:
            odd += 1
return odd
$$ LANGUAGE plpython3u;

SELECT count_odd_fetch(10000);

Результат:

 count_odd_fetch
-----------------
         2500000

Объект курсора также предоставляет интерфейс iterator, возвращающий по одной строке, пока все строки не будут выданы. Данные, полученные таким образом, не являются объектами результатов, а представляют собой словари (dictionary). Каждый словарь соответствует одной строке результата.

CREATE FUNCTION count_odd_iterator() RETURNS integer AS $$
odd = 0
for row in plpy.cursor("SELECT aid FROM pgbench_accounts"):
    if row['aid'] % 2:
         odd += 1
return odd
$$ LANGUAGE plpython3u;

SELECT count_odd_iterator();

Результат:

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