Расширение PostgreSQL
PostgreSQL не ограничивает пользователя встроенными функциями и операторами, позволяя ему создавать собственные расширения. Если вам приходится часто выполнять некоторую стандартную последовательность команд SQL или программных операций, пользовательские функции помогут решить эту задачу более надежно и эффективно. Также в PostgreSQL предусмотрена возможность определения операторов для вызова пользовательских (или встроенных) функций, что делает команды SQL понятнее и эффективнее.
Функции и операторы тоже существуют как объекты базы данных и поэтому связываются с конкретной базой. Например, функция, созданная при подключении к базе данных booktown, доступна только для пользователей, также подключившихся к этой базе.
Если некоторые общие функции или операторы должны использоваться в разных базах данных, создайте их в базе данных template 1. В этом случае объекты функций и операторов будут автоматически копироваться из шаблона template 1 при создании новой базы данных.
В следующих подразделах рассматриваются операции создания, использования и удаления нестандартных функций и операторов.
Создание новых функций
Разновидность команды SQL99 CREATE FUNCTION, поддерживаемая в PostgreSQL, не обладает прямой совместимостью со стандартом, но зато обеспечивает широкие возможности для расширения PostgreSQL за счет создания пользовательских функций (за информацией о встроенных операторах и функциях обращайтесь к главе 5).
Синтаксис команды CREATE FUNCTION:
CREATE FUNCTION имя ( [ тип_аргумента [. ...] ] )
RETURNS тип_возвращаемого_значения
AS 'определение'
LANGUAGE 'язык'
[ WITH ( атрибут [. ...] ) ]
- CREATE FUNCTION имя ( [ тпип_аргумента [, ...] ] ). После ключевых слов CREATE FUNCTION указывается имя создаваемой функции, после чего в круглых скобках перечисляются типы аргументов, разделенные запятыми. Если список в круглых скобках пуст, функция вызывается без аргументов (хотя сами круглые скобки обязательно должны присутствовать как в определении функции, так и при ее использовании).
- RETURNS тип_возвращаемого^значения. Тип данных, возвращаемый функцией.
- AS ' определение'. Программное определение функции. В процедурных языках (таких, как PL/pgSQL) оно состоит из кода функции. Для откомпилированных функций С указывается абсолютный системный путь к файлу, содержащему объектный код.
- LANGUAGE 'язык'. Название языка, на котором написана функция. В аргументе может передаваться имя любого процедурного языка (такого, как plpgsql или plperl, если соответствующая поддержка была установлена при компиляции), С или SQL.
- [ WITH ( атрибут [. ...] ) ]. В PostgreSQL 7.1.x аргумент атрибут
может принимать два значения: iscachablen isstrict.
- i scachabl e. Оптимизатор может использовать предыдущие вызовы функций для ускоренной обработки будущих вызовов с тем же набором аргументов. Кэширование обычно применяется при работе с функциями, сопряженными с большими затратами ресурсов, но возвращающими один и тот же результат при одинаковых значениях аргументов.
- i sstri ct. Функция всегда возвращает NULL в случае, если хотя бы один из ее аргументов равен NULL. При передаче атрибута isstrict результат возвращается сразу, без фактического выполнения функции.
ПРИМЕЧАНИЕ
PostgreSQL позволяет перегружать функции, то есть присваивать одно имя нескольким функциям, отличающимся по типу аргументов. Перегрузка позволяет связать с одним именем функции несколько выполняемых операций в зависимости от количества и типа аргументов.
Создание функций SQL
Из всех разновидностей функций в PostgreSQL проще всего создаются «чистые» функции SQL, поскольку их создание не требует ни знания других языков, ни серьезного опыта программирования. Функция SQL определяется как обычная команда с позиционными параметрами.
Позиционный параметр представляет собой ссылку на один из аргументов, переданных при вызове функции SQL. Он называется позиционным, поскольку в ссылке указывается его позиция в списке переданных аргументов. Позиционный параметр состоит из знака $, за которым следует номер (нумерация начинается с 1). Например, $1 означает первый аргумент в переданном списке.
В листинге 7.46 создается функция i sbn_to_ti tl e, которая возвращает название книги по заданному коду ISBN. Функция получает один аргумент типа text и возвращает результат того же типа.
Листинг 7.46. Создание функции SQL
booktown=# CREATE FUNCTION isbn_to_title(text) RETURNS text
booktown-l AS 'SELECT title FROM books
booktown'f JOIN editions AS e (isbn. id)
booktown'# USING (id)
booktown'# WHERE isbn = $1'
booktown-# LANGUAGE 'SQL';
CREATE
Позиционный параметр $1 при выборке заменяется значением первого аргумента в списке, переданном при вызове функции 1sbn_to_title. Позиционный параметр не заключается в отдельные апострофы, поскольку апострофы являются частью переданного аргумента. Остальные составляющие определения функции являются либо идентификаторами, либо стандартными ключевыми словами SQL
Сообщение CREATE означает, что создание функции прошло успешно. В листинге 7.47 функция i sbn_to_ti tl e вызывается с одним текстовым аргументом 0929605942. Функция возвращает название книги, связанное с этим кодом ISBN. При этом используется код SQL, содержащийся в листинге 7.46.
Листинг 7.47. Использование функции SQL
booktown=# SELECT isbn_to_title('0929605942');
isbn to title
The Tell-Tale Heart
(1 row)
Созданная функция доступна для всех пользователей, обладающих соответствующими правами. Например, для выполнения функции 1sbn_to_title необходим доступ для чтения к таблицам editions и books (права пользователей описаны в главе 10).
Создание функций на языке С
СУБД PostgreSQL, написанная на языке С, может динамически подгружать откомпилированный код С без перекомпиляции пакета. Использование команды
CREATE FUNCTION для компоновки с функциями С разрешено только суперпользователям, поскольку эти функции могут содержать системные вызовы, представляющие потенциальную угрозу для безопасности системы.
Документирование всего интерфейса API системы PostgreSQL выходит за рамки книги. Впрочем, опытный программист сможет очень легко написать, откомпилировать и скомпоновать простейшие функции С с использованием загружаемых общих модулей.
У компилятора gcc (GNU С Compiler) имеется ключ -shared, предназначенный для создания динамически загружаемых модулей. В простейшем случае загружаемый модуль создается командой следующего вида:
$ gcc -shared input.с -о output.so
Здесь input.с — имя файла, содержащего компилируемый код С, a output.so — файл общего загружаемого модуля.
В листинге 7.48 приведена пара очень простых функций, написанных на языке С. Первая функция, is_zero(int), возвращает true (1), если при вызове ей был передан аргумент 0; в противном случае возвращается false (0). Вторая функция, is_zero_two(int. int), возвращает true, если хотя бы один из переданных аргументов равен нулю.
Листинг 7.48. Функции на языке С
/* 1s_zero.c
* Простейшие проверочные функции */
int is_zero(int);
int is_zero_two(int. int):
int is_zero(int incoming) {
/* Вернуть true, если аргумент равен 0. */
if (incoming == 0) return 1;
else return 0: }
int is_zero_two(int left, int right) {
/* Вернуть true, если хотя бы один из аргументов равен 0. */
if (left —0 || right == 0) return 1:
else return 0; }
ВНИМАНИЕ
В этот простейший пример не были включены заголовочные файлы PostgreSQL. В данном случае они не нужны из-за очевидного соответствия между типами данных С и SQL. Более реальные примеры с использованием внутреннего интерфейса API PostgreSQL и структур данных находятся во вложенном каталоге contrib исходного каталога PostgreSQL.
В листинге 7.49 файл is_zero.c компилируется с ключом -shared, а полученный общий модуль создается в файле is_zero.so. Путь к файлу передается в определении функции в команде CREATE FUNCTION; атрибут LANGUAGE сообщает, что функция написана на языке С.
Листинг 7.49. Создание функции на языке С
[jworsley@cmd ~]$ gcc -shared is_zero.c -о is_zero.so
[jworsley@cmd -]$ psql -U manager booktown
Welcome to psql. the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
booktown-* CREATE FUNCTION is_zero(int4) RETURNS Boolean
booktown-l AS '/home/jworsley/is_zero.so' LANGUAGE 'C';
CREATE
Команда CREATE FUNCTION в листинге 7.49 создает функцию с именем is_zero(), которая получает один аргумент типа int4 и возвращает значение типа boolean. В объявление включена ссылка на функцию С с именем i s_zero( i nt), реализованную в объектном модуле /home/jworsley/is_zero.so (поскольку в языке С нет типа boo! ean, PostgreSQL приходится преобразовывать целочисленное значение, возвращаемое функцией, к логическому типу). При этом число 0 преобразуется в fal se, a 1 — в true.
По умолчанию PostgreSQL ищет в общем модуле функцию с тем же именем, с которым она создается в PostgreSQL. Такой способ подходит для функции i s_zero(i nteger), имя которой соответствует откомпилированному символическому имени функции is_zero(int) в файле is_zero.so. Для предотвращения конфликтов имен вторая функция в общем объектном модуле определяется с сигнатурой is_zero_two(int.int). Чтобы ассоциировать ее с перегруженной функцией PostgreSQL, получающей два аргумента вместо одного, имя функции С в виде строковой константы передастся после пути к файлу общего модуля.
Имя указывается без круглых скобок и без перечисления аргументов, а от пути к файлу оно отделяется запятой:
CREATE FUNCTION имя ( [ тип_аргумента [. ...] ] )
RETURNS тип_возвращаемого_значения
AS 'определение'. 'имя_в_объектном_файле'
LANGUAGE 'С' [ WITH ( атрибут [. ...] ) ]
В листинге 7.50 подгружается тот же общий модуль, но на этот раз перегруженная функция PostgreSQL ассоциируется с функций С is_zero_two.
Листинг 7.50. Перегрузка функции С
booktown=# CREATE FUNCTION is_zero(int4. int4) RETURNS Boolean
booktown-# AS '/home/jworsley/is_zero.so'. 'is_zero_two'
booktown-* LANGUAGE 'C':
CREATE
Функции С, как и функции SQL, могут вызываться любым пользователем. Функции С могут вносить прямые изменения в файловую систему (если позволяют права) и производить операции системного уровня, поэтому к их проектированию следует подойти особенно тщательно, чтобы избежать потенциальных злоупотреблений. В листинге 7.51 приведены примеры нескольких вызовов функции i s_zero, определенной в листинге 7.49, и ее перегруженной версии из листинга 7.51.
Листинг 7.51. Использование функций С
booktown=# SELECT is_zero(0) AS zero. is_zero(l) AS one,
booktown-# 1s_zero(6. 0) AS one_zero, is_zero(11.12) AS neither:
zero | one | one_zero | neither
t [ f | t | f
(1 row)
Уничтожение функций
Функции уничтожаются владельцем или суперпользователем при помощи команды SQL DROP FUNCTION. Синтаксис команды DROP FUNCTION:
DELETE FUNCTION имя ( [ тип_аргуменга [. ...] ] ):
В листинге 7.52 приведен пример удаления функции 1sbn_to_t1tle(text). Обратите внимание: типы аргументов должны указываться обязательно, даже если функция и не перегружалась.
Листинг 7.52. Удаление функции
booktown=# DROP FUNCTION isbn_to_title(text);
DROP
Сообщение сервера DROP означает, что функция была успешно удалена. Команда DROP FUNCTION, как и большинство команд DROP, необратима, поэтому перед ее выполнением убедитесь в том, что функцию действительно требуется удалить.
Создание новых операторов
Кроме пользовательских функций PoslgreSQL позволяет создавать пользовательские операторы. С технической точки зрения операторы всего лишь обеспечивают альтернативный синтаксис для вызова функций. Например, оператор сложения (+) в действительности вызывает одну из встроенных функций (numeri c_add() и т. д.). Пример:
booktown=# SELECT I + 2 AS by_operator. numeric_add(l,2) AS by_function;
by_operator [ by_function
3 | 3
(1 row)
Определение оператора сообщает, к какому типу данных относятся левый и правый операнды. Кроме того, в определении указывается функция, которой при вызове в качестве аргументов передаются операнды.
Создание оператора
Новые операторы создаются командой SQL CREATE OPERATOR. Синтаксис команды
CREATE OPERATOR:
CREATE OPERATOR оператор ( PROCEDURE = функция
[. LEFTARG = тип! ]
[. RIGHTARG = тип2 ]
[. COMMUTATOR = коммутатор ]
[. NEGATOR = инвертор ]
[. RESTRICT = функция ограничения ]
[. JOIN = функция_обьединения ]
[. HASHES ]
[. SORT1 = левдя_сортировкд ]
[. SORT2 = правая_сортировка ] )
В этом определении оператор — символ нового оператора, а функция — имя функции, вызываемой этим оператором. Остальные секции не обязательны, хотя в определении должна присутствовать хотя бы одна из секций LEFTARG или RIGHTARG. Оператор может состоять из следующих символов:
*-*/<>=~!@#*Л&|-?$
ПРИМЕЧАНИЕ
За дополнительной информацией об остальных секциях команды CREATE OPERATOR и ограничениях на символы операторов обращайтесь к документации.
Если в команде CREATE OPERATOR указан только тип данных LEFTARG, оператор работает только с левым операндом (константой или идентификатором). И наоборот, если указан только тип RIGHTARG, оператор работает только с правым операндом. При указании обоих типов данных, LEFTARG и RIGHTARG, оператор работает с обоими операндами, левым и правым.
Хорошим примером встроенного оператора, использующего только левый операнд, является оператор факториала (!), а оператор сложения работает с обоими операндами. Количество аргументов функции, указанной в команде CREATE OPERATOR, должно соответствовать использованию ключевых слов LEFTARG и RIGHTARG (один или два аргумента). Более того, типы аргументов функции должны соответствовать типам, указанным в команде CREATE OPERATOR.
В листинге 7.53 создается оператор !#, левый операнд которого передается функции is_zero() (см. листинг 7.49). Следовательно, обозначение х !# эквивалентно вызову функции is_zero(x).
Листинг 7.53. Создание пользовательского оператора
booktown=# CREATE OPERATOR !# (PROCEDURE = is_zero,
booktown(# LEFTARG = integer);
CREATE
Сообщение CREATE означает, что создание оператора прошло успешно. Новый оператор становится доступным для любого пользователя, подключенного к базе данных (по аналогии с функциями). Оператор принадлежит пользователю, создавшему его, и поэтому не может быть удален другим пользователем, не обладающим правами суперпользователя. В листинге 7.54 новый оператор !# возвращает из таблицы stock список книг, отсутствующих на складе.
Листинг 7.54. Применение пользовательского оператора
booktown=# SELECT * FROM stock WHERE stock !#;
isbn | cost | retail | stock
0394900014 | 23.00 | 23.95 | 0
0451198492 | 36.00 | 46.95 | 0
0451457994 | 17.00 j 22.95 | 0
(3 rows)
Перегрузка операторов
Операторы, как и функции, поддерживают возможность перегрузки. Иначе говоря, в программе можно создать оператор с таким же именем, как у существующего
оператора, но работающего с другими типами операндов. При этом в программе должна существовать функция, количество и тины аргументов которой соответствуют количеству и типам операндов.
Пример перегрузки оператора !# приведен в листинге 7.55. Первая команда CREATE OPERATOR создает оператор, аналогичный оператору из листинга 7.53. Тем не менее в новой версии оператора вместо LEFTARG указано ключевое слово RIGHTARG, поэтому новый оператор работает с операндом типа integer, находящимся не слева, а справа. Вторая команда создает третью версию оператора !#, работающую с обоими операндами.
Листинг 7.55. Перегрузка пользовательского оператора
booktown=# CREATE OPERATOR !# (PROCEDURE = is_zero.
booktown(# RIGHTARG = integer);
CREATE
booktown=# CREATE OPERATOR !# (PROCEDURE = is_zero,
booktown(# LEFTARG = integer,
booktown(# RIGHTARG = integer);
CREATE
В листинге 7.55 оператор !# перегружается с тем же именем функции is_zero(), поскольку сама функция была перегружена в листинге 7.50 (см. пункт «Создание функций на языке С» в подразделе «Создание новых функций» данного раздела). Наличие двух версий функции is_zero() (с одним и с двумя аргументами) позволяет перегрузить оператор !# в двух вариантах, с одним операндом (левым или правым) и с двумя операндами.
Лексический анализатор PostgreSQL правильно интерпретирует все перегруженные операторы в командах SQL. В листинге 7.56 продемонстрированы три варианта использования оператора !# с разными операндами. Все три варианта допустимы, так как оператор был перегружен в листинге 7.55.
Листинг 7.56. Использование перегруженного оператора
booktownHf SELECT isbn, stock FROM stock booktown-# WHERE stock !#
booktown-# AND !# stock
booktown-# AND stock !# stock;
isbn | stock
0394900014 0
0451198492 | 0
0451457994 | 0
(3 rows)
Удаление оператора
Операторы удаляются командой DROP OPERATOR. Выполнение этой команды разрешено либо пользователю, создавшему оператор, либо суперпользователю PostgreSQL.
ВНИМАНИЕ
Команда DROP OPERATOR применима не только к пользовательским, но и к встроенным операторам, поэтому при выполнении этой команды с правами суперпользователя необходимо действовать очень осторожно.
Так как операторы определяются не только именем, но и типом операндов, в команде DROP OPERATOR необходимо задать типы левого и правого операндов. Если какой-либо из операндов не используется, вместо типа указывается ключевое слово NONE.
Синтаксис команды DROP OPERATOR:
DROP OPERATOR оператор ( { левый_тип \ NONE } .
{ правый_тип \ NONE } )
В листинге 7.57 удаляется версия оператора ! #, работающая с двумя операндами.
Листинг 7.57. Удаление оператора
booktown=# DROP OPERATOR !# (integer, integer);
DROP
Команда в листинге 7.58 выглядит почти так же, но она удаляет перегруженную версию с правым операндом.
Листинг 7.58. Удаление перегруженного оператора
booktown=# DROP OPERATOR !# (NONE, integer);
DROP
