Перегрузка функции С

Листинг 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)

Удаление функции

Листинг 7.52. Удаление функции

booktown=# DROP FUNCTION isbn_to_title(text);

DROP

Сообщение сервера DROP означает, что функция была успешно удалена. Команда DROP FUNCTION, как и большинство команд DROP, необратима, поэтому перед ее выполнением убедитесь в том, что функцию действительно требуется удалить.

Создание пользовательского оператора

Листинг 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. Перегрузка пользовательского оператора

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)

Удаление оператора

Листинг 7.57. Удаление оператора

booktown=# DROP OPERATOR !# (integer, integer);

DROP

Команда в листинге 7.58 выглядит почти так же, но она удаляет перегруженную версию с правым операндом.

Удаление перегруженного оператора

Листинг 7.58. Удаление перегруженного оператора

booktown=# DROP OPERATOR !# (NONE, integer);

DROP

Массивы

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

Модификация производных таблиц

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

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

С модификацией записей в базовых таблицах дело обстоит сложнее. Команды UPDATE и DELETE по умолчанию работают не только с записями базовой таблицы, но и с записями всех производных таблиц, подходящих по заданному критерию. В листинге 7.14 выполняется команда UPDATE для таблицы authors. Как видно из листинга, команда также изменяет записи таблицы di stinguished_authors.

Наследование

В PostgreSQL поддерживается механизм создания объектно-реляционных связей, называемый наследованием. Таблица может наследовать некоторые атрибуты своих полей от одной или нескольких других таблиц, что приводит к созданию отношений типа «предок—потомок». В результате производные таблицы («потомки») обладают теми же полями и ограничениями, что и их базовые таблицы («предки»), а также дополняются собственными полями.

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

Нетривиальное использование таблиц

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

Кроме того, в PostgreSQL поддерживается механизм наследования, характерный для объектно-реляционных СУБД. Наследование позволяет установить между таблицами связи типа «предок—потомок» и создать иерархию полей.

В этом разделе будут рассмотрены обе темы. Кроме того, речь пойдет о создании и практическом применении производных таблиц.

Нетривиальные возможности

В данной главе рассматриваются нетривиальные возможности PostgreSQL, в том числе оптимизация доступа к таблицам с использованием индексов, наследование и установка ограничений для таблиц, практическое применение массивов в значениях полей, работа с транзакциями и курсорами. Все перечисленные возможности выгодно отличают PostgreSQL от других реляционных СУБД.

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

Объявление курсора

Команда SQL DECLARE создает курсор и выполняет его. Этот процесс также называется открытием курсора. Курсор может быть объявлен только в существующем транзакционном блоке, поэтому перед объявлением курсора должна быть выполнена команда BEGIN. Синтаксис команды DECLARE:

DECLARE курсор [ BINARY ] [ INSENSITIVE ] [ SCROLL ]

CURSOR FOR запрос

[ FOR { READ ONLY | UPDATE [ OF none [. ...]]}]

DECLARE курсор. Имя создаваемого курсора. [ BINARY ]. Ключевое слово BINARY означает, что выходные данные должны возвращаться в двоичном формате вместо стандартного ASCII-кода. Иногда переключение на двоичный формат повышает эффективность курсора, но это относится лишь к пользовательским приложениям, поскольку стандартные клиенты (такие, как psql) работают только с текстовым выводом. [ INSENSITIVE ] [ SCROLL ]. Ключевые слова INSENSITIVE и SCROLL существуют для совместимости со стандартом SQL, но они описывают поведение PostgreSQL по умолчанию, поэтому их присутствие не обязательно. Ключевое слово SQL INSENSITIVE обеспечивает независимость данных, возвращенных курсором, от других курсоров или подключении. Поскольку PostgreSQL требует, чтобы курсоры определялись в транзакционных блоках, это требование заведомо выполняется. Ключевое слово SQL SCROLL указывает, что курсор поддерживает одновременную выборку нескольких записей. Этот режим поддерживается в PostgreSQL по умолчанию, даже если ключевое слово SCROLL не указано. CURSOR FOR запрос. Запрос, после выполнения которого итоговый набор становится доступным через курсор. FOR { READ ONLY | UPDATE [ OF поле [. ...] ] }. В PostgreSQL 7.1.x поддерживаются курсоры, доступные только для чтения (READ ONLY), поэтому секция FOR оказывается лишней.

В листинге 7.42 мы создаем транзакцию командой BEGIN и открываем курсор с именем all_books, ассоциированный с командой SELECT * FROM books.

Ограничения полей

При выполнении команды \h CREATE TABLE клиент psql выводит несколько подробных синтаксических диаграмм для ограничений, которые могут устанавливаться для таблиц. Синтаксис ограничения поля выглядит так:

[ CONSTRAINT ограничение ]

{ NOT NULL UNIQUE | PRIMARY KEY | DEFAULT значение | CHECK ( условие ) | REFERENCES таблица [ ( поле ) ]

[ MATCH FULL | MATCH PARTIAL ]

[ ON DELETE операция ]

[ ON UPDATE операция ]

[ DEFERRABLE | NOT DEFERRABLE ]

[ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] }

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

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

NOT NULL. Поле не может содержать псевдозначение NULL. Ограничение NOT NULL эквивалентно ограничению CHECK (поле NOT NULL). UNIQUE. Поле не может содержать повторяющиеся значения. Следует учитывать, что ограничение UNIQUE допускает многократное вхождение псевдозначений NULL, поскольку формально NULL не совпадает ни с каким другим значением. PRIMARY KEY. Автоматически устанавливает ограничения UNIQUE и NOT NULL, а для заданного поля создается индекс. В таблице может устанавливаться только одно ограничение первичного ключа. DEFAULT значение. Пропущенные значения поля заменяются заданной величиной. Значение по умолчанию должно относиться к типу данных, соответствующему типу поля. В PostgreSQL 7.1.x значение по умолчанию не может задаваться при помощи подзапроса. CHECK условие. Команда INSERT или UPDATE для записи завершается успешно лишь при выполнении заданного условия (выражения, возвращающего логический результат). При установке ограничения поля в секции CHECK может использоваться только поле, для которого устанавливается ограничение. REFERENCES. Это ограничение состоит из нескольких секций, которые перечислены ниже. REFERENCES таблица [ ( поле ) ]. Входные значения ограничиваемого поля сравниваются со значениями другого поля в заданной таблице. Если совпадения отсутствуют, команда INSERT или UPDATE завершается неудачей. Если параметр поле не указан, проверка выполняется по первичному ключу. Ограничение REFERENCES похоже на ограничение таблицы FOREIGN KEY, описанное в следующем пункте этого подраздела. Действительно, между этими ограничениями есть много общего. Пример таблицы, созданной с ограничением FOREIGN KEY, приведен в листинге 7.8. MATCH FULL | MATCH PARTIAL. Секция MATCH указывает, разрешается ли смешивание значений NULL и обычных значений при вставке в таблицу, у которой внешний ключ ссылается на несколько полей. Таким образом, на практике секция MATCH приносит пользу лишь в ограничениях таблиц, хотя формально она может использоваться и при ограничении полей. Конструкция MATCH FULL запрещает вставку данных, у которых часть полей внешнего ключа содержит псевдозначение NULL (кроме случая, когда NULL содержится во всех полях). В PostgreSQLV.l.x конструкция MATCH PARTIAL не поддерживается. Если секция MATCH отсутствует, считается, что поля с псевдозначениями NULL удовлетворяют ограничению. Также будет уместно напомнить, что ограничения полей относятся лишь к одному полю, поэтому секция MATCH используется лишь в ограничениях таблиц. ON DELETE операция. При выполнении команды DELETE для заданной таблицы с ограничиваемым полем выполняется одна из следующих операций: N0 ACTION (если удаление приводит к нарушению целостности ссылок, происходит ошибка; используется по умолчанию, если операция не указана), RESTRICT (аналогично NO ACTION), CASCADE (удаление всех записей, содержащих ссылки на удаляемую запись), SET NULL (поля, содержащие ссылки на удаляемую запись, заменяются псевдозначениями NULL), SET DEFAULT (полям, содержащим ссылки на удаляемую запись, присваивается значение по умолчанию). ON UPDATE операция. При выполнении команды UPDATE для заданной таблицы выполняется одна из операций, описанных выше. По умолчанию используется значение NO ACTION. Если выбрана операция CASCADE, все записи, содержащие ссылки на обновляемую запись, обновляются новым значением (вместо удаления, как в случае с ON DELETE CASCADE). DEFERRABLE | NOT DEFERRABLE. Значение DEFERRABLE позволяет отложить выполнение ограничения до конца транзакции (вместо немедленного выполнения после завершения команды). Значение NOT DEFERRABLE означает, что ограничение всегда проверяется сразу же после завершения очередной команды. В этом случае пользователь не может отложить проверку ограничения до конца транзакции. По умолчанию выбирается именно этот вариант. INITIALLY DEFERRED | INITIALLY IMMEDIATE. Секция INITIALLY задается только для ограничений, определенных с ключевым словом DEFERRED. Значение INITIALLY DEFERRED откладывает проверку ограничения до конца транзакции, а при установке значения INITIALLY IMMEDIATE проверка производится после каждой команды. При отсутствии секции INITIALLY по умолчанию используется значение INITIALLY IMMEDIATE.

В листинге 7.7 приведен пример создания таблицы employees с несколькими ограничениями.

Ограничения таблиц

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

[ CONSTRAINT ограничение ] { UNIQUE ( поле [. ...] ) | PRIMARY KEY ( поле [. ...] ) | CHECK ( условие ) ] FOREIGN KEY ( поле [. ... ] )

REFERENCES таблица [ ( поле [....])]

[ MATCH FULL | MATCH PARTIAL ]

[ ON DELETE операция ]

[ ON UPDATE операция ]

[ DEFERRABLE | NOT DEFERRABLE ]

[ INITIALLY DEFERRED INITIALLY IMMEDIATE ]

Секция CONSTRAINT ограничение определяет необязательное имя. Ограничениям рекомендуется присваивать содержательные имена вместо автоматически сгенерированных имен, не несущих никакой полезной информации. В будущем имя также может пригодиться и для удаления ограничения (например, в секции DROP CONSTRAINT команды ALTER TABLE). Другие секции относятся к четырем разновидностям ограничений таблиц.

PRIMARY KEY ( поле [. ...] ). Ограничение таблицы PRIMARY KEY имеет много общего с аналогичным ограничением поля. В ограничении таблицы PRIMARY KEY могут перечисляться несколько полей, разделенных запятыми. Для перечисленных полей автоматически строится индекс. Как и в случае с ограничением поля, комбинация значений всех полей должна быть уникальной и не может содержать NULL. UNIQUE ( поле [. ...] ). Ограничение означает, что комбинация значений полей, перечисленных за ключевым словом UNIQUE, принимает только уникальные значения. Допускается многократное вхождение псевдозначения NULL, поскольку оно формально не совпадает ни с одним значением. CHECK ( условие ). Команда INSERT или UPDATE для записи завершается успешно лишь при выполнении заданного условия (выражения, возвращающего логический результат). Используется по аналогии с ограничениями полей, но в секции CHECK может содержать ссылки на несколько полей. FOREIGN KEY ( поле [. ... ] ) REFERENCES таблица [ ( поле [. ... ] ) ]. В качестве прототипа для секции REFERENCES можно перечислить несколько полей. Синтаксис части, следующей за секцией FOREIGN KEY, идентичен синтаксису ограничения REFERENCES для полей.

В листинге 7.8 в базе данных booktown создается таблица editions с тремя ограничениями. Развернутое описание приводится ниже.

Ограничения в таблицах

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

Ограничения задаются в секции CONSTRAINT при создании таблицы командой CREATE TABLE. Они делятся на два типа — ограничения полей и ограничения таблиц.

Ограничения полей всегда относятся только к одному полю, тогда как ограничения таблиц могут устанавливаться как для одного, так и для нескольких полей. В команде CREATE TABLE ограничения полей задаются сразу же после определения поля, тогда как ограничение таблицы устанавливается в специальном блоке, отделенном запятой от всех определений полей. Поля, на которые распространяется ограничение таблицы, задаются самим определением, а не его расположением в команде.

Ниже описаны различные правила, устанавливаемые при помощи ограничений.

Операции с последовательностями

Выборка атрибутов последовательности требуется относительно редко. Как правило, все операции с последовательностями выполняются при помощи трех специальных функций PostgreSQL.

nextval ('последовательность'). Увеличивает текущее значение заданной последовательности и возвращает новое значение в виде величины типа 1 nteger. currval ('последовательность'). Возвращает значение, полученное при последнем вызове nextval О. Значение ассоциируется с определенным сеансом PostgreSQL, поэтому если функция nextval () еще не вызывалась для заданного подключения в текущем сеансе, функция не сможет вернуть значение. setval ('последовательность'. п). Присваивает число п текущему значению заданной последовательности. Следующий вызов nextval О возвращает значение п+приращение, где приращение — изменение текущего значения последовательности при каждой итерации. setval ('последовательность'. п. Ь). Также присваивает число п текущему значению заданной последовательности. Если параметр b (тип boo! ean) равен f al se, то следующий вызов nextval () вернет значение п, а если параметр равен true, то будет возвращено значение п+приращение, как при вызове функции setval О без дополнительного аргумента.

Чаще всего при работе с последовательностями используется функция nextval О, при вызове которой и происходит увеличение текущего значения. В качестве аргумента функция получает имя последовательности, заключенное в апострофы, а возвращает значение типа Integer.

В листинге 7.30 выводится пара очередных значений последовательности с именем shipments_ship_id_seq.

Определение количества элементов

Чтобы узнать количество значении, хранящихся в массиве, следует воспользоваться функцией array_dims(). В качестве параметра функции передается идентификатор — имя поля-массива, для которого вызывается функция. Результат возвращается в виде строки, содержащей описание массива в синтаксисе среза. В листинге 7.25 приведен пример вызова функции array_dims() для поля books таблицы favorite_books.

Перегрузка операторов

Операторы, как и функции, поддерживают возможность перегрузки. Иначе говоря, в программе можно создать оператор с таким же именем, как у существующего

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

Пример перегрузки оператора !# приведен в листинге 7.55. Первая команда CREATE OPERATOR создает оператор, аналогичный оператору из листинга 7.53. Тем не менее в новой версии оператора вместо LEFTARG указано ключевое слово RIGHTARG, поэтому новый оператор работает с операндом типа integer, находящимся не слева, а справа. Вторая команда создает третью версию оператора !#, работающую с обоими операндами.

Перемещение курсора

Курсор поддерживает информацию о текущей позиции в итоговом наборе команды SELECT. Перемещение курсора к заданной записи выполняется командой MOVE. Синтаксис команды MOVE:

MOVE [ FORWARD | BACKWARD | RELATIVE ]

[ число ALL | NEXT | PRIOR ]

{ IN | FROM } курсор

Как видно из приведенного объявления, синтаксис команды MOVE очень близок к синтаксису команды FETCH. Впрочем, команда MOVE никаких записей не возвращает и лишь перемещает текущую позицию курсора. Смещение задается целочисленной константой или ключевым словом ALL (перемещение в заданном направлении на максимально возможное расстояние), NEXT или PRIOR. В листинге 7.44 текущая позиция курсора перемещается на 10 записей вперед.

Получение информации о триггерах

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

Последовательности

Последовательностью (sequence) в PostgreSQL называется объект базы данных, который фактически представляет собой автоматически увеличивающееся число. В других СУБД последовательности часто называются счетчиками. Последовательности очень часто используются для присваивания уникальных значении идентификаторов в таблицах. Последовательность определяется текущим числовым значением и набором характеристик, определяющих алгоритм автоматического увеличения (или уменьшения) используемых данных.

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

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

Просмотр последовательностей в базе данных

Команда \d клиента psql показывает, к какому типу относится тот или иной объект базы данных — последовательность, таблица, представление или индекс. Для получения более конкретной информации можно воспользоваться командой \ds, выводящей список всех последовательностей в текущей базе данных. Пример:

booktown=# \ds

List of relations Name Type | Owner

book_ids | sequence | manager

shipments_ship_id_seq j sequence | manager

subject_ids j sequence | manager

(3 rows)

К последовательности также можно обратиться командой SELECT, как к таблице или представлению (хотя такая возможность используется относительно редко). При составлении запроса к последовательности в списке выборки вместо полей указываются атрибуты последовательности, перечисленные в табл. 7.1.

Расширение PostgreSQL

PostgreSQL не ограничивает пользователя встроенными функциями и операторами, позволяя ему создавать собственные расширения. Если вам приходится часто выполнять некоторую стандартную последовательность команд SQL или программных операций, пользовательские функции помогут решить эту задачу более надежно и эффективно. Также в PostgreSQL предусмотрена возможность определения операторов для вызова пользовательских (или встроенных) функций, что делает команды SQL понятнее и эффективнее.

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

Если некоторые общие функции или операторы должны использоваться в разных базах данных, создайте их в базе данных template 1. В этом случае объекты функций и операторов будут автоматически копироваться из шаблона template 1 при создании новой базы данных.

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

Создание функций на языке С

СУБД 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, если хотя бы один из переданных аргументов равен нулю.

Создание функций SQL

Из всех разновидностей функций в PostgreSQL проще всего создаются «чистые» функции SQL, поскольку их создание не требует ни знания других языков, ни серьезного опыта программирования. Функция SQL определяется как обычная команда с позиционными параметрами.

Позиционный параметр представляет собой ссылку на один из аргументов, переданных при вызове функции SQL. Он называется позиционным, поскольку в ссылке указывается его позиция в списке переданных аргументов. Позиционный параметр состоит из знака $, за которым следует номер (нумерация начинается с 1). Например, $1 означает первый аргумент в переданном списке.

В листинге 7.46 создается функция i sbn_to_ti tl e, которая возвращает название книги по заданному коду ISBN. Функция получает один аргумент типа text и возвращает результат того же типа.

Создание индекса

Индексы создаются командой SQL CREATE INDEX. Синтаксис команды:

CREATE [ UNIQUE ] INDEX индекс ON таблица

[ USING тип ] ( поле [ класс ] [. ...] )

Здесь индекс — имя создаваемого индекса, таблица — имя таблицы, для которой строится индекс, а поле — имя индексируемого поля. Необязательный параметр тип позволяет выбрать нужную реализацию индекса, а параметр класс описывает операторный класс, используемый для сортировки входных данных.

ВНИМАНИЕ

В PostgreSQL операторные классы хранятся в поле pg_opclass. Используйте этот параметр лишь в том случае, если вы досконально разбираетесь во всех тонкостях операторных классов PostgreSQL.

Команда может содержать список из нескольких индексируемых полей, разделенных запятыми; в этом случае индекс строится для всех перечисленных полей. Составные индексы используются в PostgreSQL только при выполнении команд SQL, осуществляющих поиск по всем индексированным полям с объединением условий ключевым словом AND. В стандартной установке PostgreSQL составной индекс содержит не более 16 полей и реализуется только в виде В-дерева.

Перед построением индекса следует решить, какие поля чаще всего требуются при поиске. Например, хотя таблица books проиндексирована по полю id (первичный ключ), поле title также часто проверяется в условиях WHERE. Включение вторичного индекса по полю title заметно ускорит работу команд SQL, в которых значение этого поля сравнивается с некоторой величиной.

В листинге 7.1 приведен пример построения индекса и просмотра таблицы books при помощи управляющей команды \d psql. Помимо типов полей команда также выводит имена индексов таблицы.

Создание новых функций

Разновидность команды 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 результат возвращается сразу, без фактического выполнения функции.

Примечание 1
Примечание 1

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

Создание новых операторов

Кроме пользовательских функций 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. Оператор может состоять из следующих символов:

*-*/<>=~!@#*Л&|-?$

Примечание 2
Примечание 2

За дополнительной информацией об остальных секциях команды CREATE OPERATOR и ограничениях на символы операторов обращайтесь к документации.

Если в команде CREATE OPERATOR указан только тип данных LEFTARG, оператор работает только с левым операндом (константой или идентификатором). И наоборот, если указан только тип RIGHTARG, оператор работает только с правым операндом. При указании обоих типов данных, LEFTARG и RIGHTARG, оператор работает с обоими операндами, левым и правым.

Хорошим примером встроенного оператора, использующего только левый операнд, является оператор факториала (!), а оператор сложения работает с обоими операндами. Количество аргументов функции, указанной в команде CREATE OPERATOR, должно соответствовать использованию ключевых слов LEFTARG и RIGHTARG (один или два аргумента). Более того, типы аргументов функции должны соответствовать типам, указанным в команде CREATE OPERATOR.

В листинге 7.53 создается оператор !#, левый операнд которого передается функции is_zero() (см. листинг 7.49). Следовательно, обозначение х !# эквивалентно вызову функции is_zero(x).

Создание полей со значениямимассивами

Создание полей со значениями-массивами

Чтобы создать простейшее поле-массив, включите в команду CREATE TABLE или ALTER TABLE пару квадратных скобок после имени поля. Квадратные скобки показывают, что вместо одного значения в поле может храниться массив указанного типа. Например, команда для создания поля single_array типа type выглядит так:

single_array type[] -- Одномерный массив

Дополнительные квадратные скобки определя ют многомерные массивы, то есть «массивы массивов». Пример:

mu1ti_array type[][] -- Многомерный массив

Теоретически в квадратных скобках можно указать целое число, чтобы созданный массив имел фиксированный размер (то есть всегда состоял из п элементов по указанному измерению и не более). Тем не менее в PostgreSQL 7.1.x это ограничение не соблюдалось, и на практике массив фиксированной длины ничем не отличался от обычного массива.

В листинге 7.16 создается таблица с именем favorite_books, связывающая целочисленный код работника с одномерным массивом строк books.

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

Последовательности создаются командой SQL CREATE SEQUENCE с положительным или отрицательным приращением. Синтаксис команды CREATE SEQUENCE:

CREATE SEQUENCE последовательность
[ INCREMENT приращение ]
[ MINVALUE минимум ]
[ MAXVALUE максимум ]
[ START начало ]
[ CACHE кэш ]
[ CYCLE ]

В этом определении единственный обязательный параметр последовательность определяет имя создаваемой последовательности. Значения последовательности .представляются типом Integer, поэтому максимальное и минимальное значения должны лежать в интервале от 2 147 483 647 до -2 147 483 647.

Ниже описаны необязательные секции команды CREATE SEQUENCE.

INCREMENT приращение. Числовое изменение текущего значения последовательности. Используется при вызове для последовательности функции nextvaK). Отрицательное приращение создает убывающую последовательность. По умолчанию приращение равно 1. MINVALUE минимум. Минимальное допустимое значение последовательности. Попытка уменьшить текущее значение ниже заданного минимума приведет к ошибке или циклическому переходу к максимальному значению (если последовательность создавалась с ключевым словом CYCLE). По умолчанию минимальное значение равно 1 для возрастающих последовательностей или -2 147 483 647 для убывающих последовательностей. MAXVALUE максимум. Максимальное допустимое значение последовательности. Попытка увеличить текущее значение выше заданного максимума приведет к ошибке или циклическому переходу к минимальному значению. По умолчанию максимальное значение равно 2 147 483 647 для возрастающих последовательностей или -1 для убывающих последовательностей. START начало. Начальное значение последовательности, которым является любое целое число в интервале между минимальным и максимальным значениями. По умолчанию последовательность начинается с нижнего порога для возрастающих последовательностей или с верхнего порога для убывающих последовательностей. CACHE кэш. Возможность предварительного вычисления и хранения значений последовательности в памяти. Кэширование ускоряет доступ к часто используемым последовательностям. Минимальное значение, заданное по умолчанию, равно 1; увеличение объема кэша приводит к увеличению числа кэшируемых значений. CYCLE. При достижении нижнего или верхнего порога последовательность продолжает генерировать новые значения. В этом случае она переходит к минимальному значению (для возрастающих последовательностей) или к максимальному значению (для убывающих последовательностей).

В листинге 7.28 создается простая возрастающая последовательность с именем shipments_ship_Td_seq, которая начинается со значения 0 и увеличивается со стандартным приращением 1 до тех пор, пока не достигнет максимального значения по умолчанию 2 147 483 647. Ключевое слово CYCLE не указано, поэтому последовательность заведомо принимает уникальные значения.

Создание производной таблицы

Производная таблица создается командой SQL CREATE TABLE, в которую включается секция INHERITS. Секция состоит из ключевого слова INHERITS и имени базовой таблицы (или нескольких таблиц).

Часть команды CREATE TABLE, относящаяся к наследованию, выглядит так:

CREATE TABLE производная_таблица определение

INHERITS ( базовая_таблица [. ...] )

В этом определении производная таблица — имя создаваемой таблицы, определение — полное определение таблицы со всеми стандартными секциями команды CREATE TABLE, а базовая _таблица — таблица, структура которой наследуется новой таблицей. Дополнительные имена базовых таблиц перечисляются через запятую.

В листинге 7.11 создается таблица distinguished_authors, определение которой состоит из единственного текстового поля award. Поскольку в команде создания таблицы указано ключевое слово INHERITS, таблица содержит четыре поля — одно собственное и три унаследованных.

Создание триггера

Триггер создается на основе существующей функции. PostgreSQL позволяет создавать функции на разных языках программирования, в том числе на SQL, PL/ pgSQL и С. В PostgreSQL 7.1.x триггеры могут вызывать функции, написанные на любом языке, но за одним исключением: функция не может быть полностью реализована на SQL.

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

CREATE TRIGGER триггер { BEFORE | AFTER } { событие [ OR событие ...]}

ON таблица

FOR EACH { ROW STATEMENT }

EXECUTE PROCEDURE функция ( аргументы )

Ниже приводятся краткие описания компонентов этого определения.

CREATE TRIGGER триггер. В аргументе триггер указывается произвольное имя создаваемого триггера. Имя может совпадать с именем триггера, уже существующего в базе данных — при условии, что этот триггер установлен для другой таблицы. Кроме того, по аналогии с большинством других несистемных объектов баз данных, имя триггера (в сочетании с таблицей, для которой он устанавливается) должно быть уникальным лишь в контексте базы данных, в которой он создается. { BEFORE AFTER }. Ключевое слово BEFORE означает, что функция должна выполняться перед попыткой выполнения операции, включая все встроенные проверки ограничений данных, реализуемые при выполнении команд INSERT и DELETE. Ключевое слово AFTER означает, что функция вызывается после завершения операции, приводящей в действие триггер. { событие [ OR событие ... ] }. События SQL, поддерживаемые в PostgreSQL При перечислении нескольких событий в качестве разделителя используется ключевое слово OR. ON таблица. Имя таблицы, модификация которой заданным событием приводит к срабатыванию триггера. FOR EACH { ROW STATEMENT }. Ключевое слово, следующее за конструкцией FOR EACH и определяющее количество вызовов функции при наступлении указанного события. Ключевое слово ROW означает, что функция вызывается для каждой модифицируемой записи. Если функция должна вызываться всего один раз для всей команды, используется ключевое слово STATEMENT. EXECUTE PROCEDURE функция ( аргументы ). Имя вызываемой функции с аргументами.

Примечание 3
Примечание 3

Создание триггеров разрешено только владельцу базы данных или суперпользователю.

Механизм ограничений PostgreSQL позволяет реализовать простое сравнение данных со статическими значениями, но иногда проверка входных данных должна производиться по более сложным критериям. Это типичный пример ситуации, в которой удобно воспользоваться триггером.

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

Предположим, вы написали на процедурном языке функцию, которая проверяет данные, переданные при вызове команды INSERT или UPDATE для таблицы shipments, и затем обновляет таблицу stock, снимая поставленный товар со складского учета. Такую функцию можно написать на любом языке, поддерживаемом PostgreSQL (кроме «чистого» SQL, о чем говорилось выше).

Прежде всего функция убеждается в том, что переданный код покупателя (customerj d) и код ISBN (i sbn) присутствуют в таблицах customers и ech ti ons. Если хотя бы один из кодов отсутствует, функция возвращает признак ошибки. Если оба кода присутствуют в таблицах, команда SQL выполняется, и после успешного завершения количество товара на складе в таблице stock автоматически уменьшается в соответствии с объемом поставки.

Триггер, создаваемый в листинге 7.35, срабатывает непосредственно перед выполнением команды INSERT или UPDATE в таблице shi pments. Триггер вызывает функцию check_sh1pment addition() для каждой изменяемой записи.

Срезы

В PostgreSQL также поддерживается возможность создания срезов при выборке из массива. Срез аналогичен обычному обращению к элементам по индексу, но он описывает интервал значений. Срез задается парой целочисленных индексов, разделенных двоеточием и заключенных в квадратные скобки. Например, конструкция [2:5] описывает второй, третий, четвертый и пятый элемент заданного массива. Результат среза возвращается в виде константы-массива, которая фактически описывает подмножество элементов исходного массива (впрочем, срез может содержать все элементы исходного массива).

В листинге 7.24 выбираются первые два элемента массива books в записях таблицы favorite_books. Хотя первая запись содержит только одно название книги, оно возвращается в виде массива из одного элемента.

Атрибуты последовательностей

Таблица 7.1. Атрибуты последовательностей

Атрибут	Тип
sequence name	name
last_value	integer
increment by	integer
max value	integer
min value	integer
cache value	integer
log cnt	integer
is_cycled	"char"
is called	"char"

В листинге 7.29 приведен пример запроса к последовательности shipments_ship_id_seq. Запрос возвращает атрибуты last_value (текущее значение последовательности) и increment_by (приращение при очередном вызове nextval О).

Таблица shipments

Таблица 7.2. Таблица shipments

Поле	Тип	Модификатор
Id	Integer	NOT NULL DEFAULT nextval ( 'shipments^ship id seq1)
customerjd	Integer
isbn	text
ship_date	timestamp with time zone

Команда создания таблицы shipments с автоматически увеличивающимся значением по умолчанию и ограничением первичного ключа выглядит так:

CREATE TABLE shipments

(id integer DEFAULT nextval ('shipments_shipjd_seq')

PRIMARY KEY. customerjd integer, isbn text. ship_date timestamp)

В качестве значения по умолчанию для поля id назначается результат вызова nextval () для последовательности shi pments_shi p_i d_seq. Таким образом, при вставке записей без указания поля id значение автоматически генерируется по результату вызова функции.

ВНИМАНИЕ

Простая установка ограничения DEFAULT не гарантирует его применения. Пользователь способен вручную задать любое значение, что может привести к потенциальному нарушению уникальности в будущем. Для предотвращения конфликтов можно воспользоваться триггером. За дополнительной информацией обращайтесь к подразделу «Триггеры» этого раздела.

После вызова функции nextval О для последовательности в некотором сеансе (то есть подключении к PostgreSQL) функция currval () возвращает значение, полученное при предыдущем вызове nextval () в активном сеансе.

Примечание 2
Примечание 2

Текущее значение последовательностей ассоциируется с определенным сеансом, чтобы одновременные обращения со стороны нескольких пользователей не приводило к ошибкам. Два пользователя могут работать с одной последовательностью в разных сеансах, но при этом функция currval () возвращает последнее текущее значение последовательности для того сеанса, в котором она была вызвана.

В листинге 7.31 в таблицу shi pments вставляется новая запись, в которой не указано значение поля id. В этой ситуации используется значение по умолчанию, которое (см. табл. 7.2) определяется результатом приращения последовательности

shipments_ship_id_seq функцией nextvaK).

Затем функция currva() используется для выборки только что вставленной записи.

Таблица pgjrigger

Таблица 7.3. Таблица pgjrigger

Поле	Тип
tgrelid	old
tgname	name
tgfoid	old
tgtype	smallint
tgenabled	boo'i ean
tgisconstraint	boolean
tgconstrname	name
tgconstrrelid	oid
tgdeferrable	boolean
tginltdef erred	boolean
tgnargs	small int
tgattr	int2vector
tgargs	bytea

Большинство полей, перечисленных в табл. 7.3, в прямых запросах не используется. Среди атрибутов триггеров в системной таблице pg_trigger центральное место занимают атрибуты tgrelid и tgname.

В поле tgrel id хранится идентификатор отношения, с которым связан данный триггер. Значение относится к типу oid и соответствует содержимому поля rel f i I enode системной таблицы pg_cl ass. В поле tgname хранится имя триггера, указанное в команде CREATE TRIGGER при его создании.

Типы индексов

Необязательная секция USING задает реализуемый тип индекса. В PostgreSQL 7.1.x поддерживаются три типа индексов:

В-дерево; R-дерево; кэш.

В первом варианте с высокой степенью параллельности используются алгоритмы В-деревьев Лемана-Яо (Lehman-Yao). Это самый распространенный способ индексации, обладающий наибольшими возможностями. По этой причине он используется по умолчанию.

Реализация R-дерева, основанная на квадратичном разбиении по алгоритму Гуттмана (Guttman), применяется главным образом при операциях с геометрическими типами данных. Реализация хэша основана на алгоритмах линейного хэширования Литвина (Litwin), которые традиционно используются для индексов с частой проверкой равенства (то есть ориентированы на оператор =).

На момент написания книги в PostgreSQL версии 7.1.x реализация индексов на основе В-дерева значительно превосходила остальные типы но универсальности и широте возможностей. В-дерево рекомендуется использовать вместо хэша даже при прямых сравнениях оператором =. Хэш поддерживается в первую очередь по соображениям совместимости, хотя ничто не мешает вам выбрать эту реализацию, если вы твердо уверены в выигрыше от перехода на нее от В-дерева.

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

Тип индекса задается в секции USING при помощи ключевых слов BTREE, RTREE и HASH. По умолчанию используется тип BTREE.

В листинге 7.4 создается таблица с именем polygons, предназначенная для хранения геометрических данных типа polygon. Затем для поля shape создается индекс spaci a I _1 ndex типа RTREE.

Транзакции и курсоры

В PostgreSQL используется специфический подход к обработке транзакций в базах данных. В терминологии SQL транзакцией называется процесс синхронизации результатов выполнения команды с состоянием данных в базе. Данные не записываются на диск немедленно, а лишь отражаются в «текущей» информации состояния, хранящейся в базе. В результате фиксации транзакции последствия выполнения команды окончательно фиксируются в текущем состоянии базы данных.

Возникает очевидная проблема — что произойдет, если два пользователя одновременно попытаются зафиксировать взаимоисключающие изменения в одном объекте базы данных? В некоторых СУБД подобные конфликты предотвращаются путем блокировки (locking).

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

В PostgreSQL используется механизм MVCC (Multi-Version Concurrency Control), позволяющий выполнять команды SQL в отложенных транзакционных блоках. Таким образом, каждое подключение к серверу PostgreSQL до фиксации результатов фактически поддерживает временный «образ» объектов базы данных, модифицируемых в транзакцпопном блоке.

Если в программе отсутствует команда начала транзакциопного блока, все команды SQL, передаваемые PostgreSQL, фиксируются автоматически, то есть база данных синхронизируется с. результатами команд сразу же после их выполнения. Тем не менее при явном создании транзакщюнного блока изменения в базе данных остаются невидимыми для других пользователей вплоть до фиксации. Таким образом, появляется возможность одновременного внесения изменений в разные объекты базы данных. Далее все изменения либо фиксируются, либо откатываются (отменяются).

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

В PostgreSQL также поддерживаются курсоры — универсальный и гибкий механизм ссылок на выполненные запросы SQL. Курсор позволяет перебрать содержимое итогового набора и включить в выборку только некоторые из полученных записей. При правильном использовании курсоры повышают эффективность работы со статическими наборами записей в приложениях. Курсоры выполняются только в транзакционных блоках.

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

Транзакционные блоки

Транзакционные блоки создаются командой SQL BEGIN, за которой могут следовать необязательные ключевые слова WORK или TRANSACTION. Эти ключевые слова делают команду более наглядной, но никак не влияют на работу ее пли транзакци-онного блока.

В листинге 7.38 приведен пример создания транзакционного блока в базе данных booktown.

Триггеры

Довольно часто перед некоторыми событиями SQL или после них должны выполняться определенные операции — например, проверка логической целостности данных, заносимых в базу, предварительное форматирование данных перед вставкой или модификация других таблиц, логически обусловленная удалением или модификацией записей. Традиционно такие операции выполнялись на программном уровне приложением, подключившимся к базе данных, а не самой СУБД.

В PostgreSQL поддерживаются нестандартные расширения, называемые триггерами (trigger) и упрощающие взаимодействие приложения с базой данных. Триггер определяет функцию, которая должна выполняться до или после некоторой операции с базой данных. Триггеры реализуются на языке С, PL/pgSQL или любом другом функциональном языке (кроме SQL), который может использоваться в PostgreSQL для определения функций. Дополнительная информация о создании функций приведена в разделе «Расширение PostgreSQL» этой главы, а языки PL/ pgSQL описаны в главе 11.

ВНИМАНИЕ

Триггеры относятся к числу специфических расширений PostgreSQL, поэтому их не рекомендуется использовать в решениях, требующих высокой степени совместимости с другими РСУБД.

Триггеры срабатывают при выполнении с таблицей команды SQL INSERT, UPDATE или DELETE.

Удаление индекса

Для удаления индексов из таблицы используется команда DROP INDEX. Синтаксис команды DROP INDEX: DROP INDEX индекс [. ...]

В качестве параметра команде передается имя удаляемого индекса. Допускается одновременное удаление нескольких индексов, перечисленных через запятую. В листинге 7.6 удаляется индекс upper_title_idx, созданный в листинге 7.5.

Удаление ограничений

В PostgreSQL 7.1.x не поддерживается прямое удаление ограничений из таблицы. Добиться нужного результата можно лишь одним способом — создать копию таблицы, практически полностью повторяющую оригинал, но не содержащую удаляемых ограничений. Данные копируются из исходной таблицы в новую, после чего таблицы переименовываются командой ALTER TABLE и копия заменяет оригинал.

ВНИМАНИЕ

Применяя этот прием, следует учитывать, что кто-то из пользователей может подключиться к базе данных и работать с модифицируемыми таблицами. Вставка или любые модификации данных в процессе копирования недопустимы; таким образом, если таблица активно используется, вы можете временно запретить подключения к PostgreSQL, внести необходимые изменения и перезапустить систему.

В листинге 7.10 снятие ограничений продемонстрировано на примере ограничения FOREIGN KEY с именем legal_subjects, установленного для таблицы books (см. листинг 7.9). Обратите внимание на удаление индекса books_1d_pkey перед созданием новой таблицы, что позволяет создать таблицу с индексом books_id_pkey. На самом деле это не обязательно, но имя индекса первичного ключа лучше сохранить.

Удаление оператора

Операторы удаляются командой DROP OPERATOR. Выполнение этой команды разрешено либо пользователю, создавшему оператор, либо суперпользователю PostgreSQL.

ВНИМАНИЕ

Команда DROP OPERATOR применима не только к пользовательским, но и к встроенным операторам, поэтому при выполнении этой команды с правами суперпользователя необходимо действовать очень осторожно.

Так как операторы определяются не только именем, но и типом операндов, в команде DROP OPERATOR необходимо задать типы левого и правого операндов. Если какой-либо из операндов не используется, вместо типа указывается ключевое слово NONE.

Синтаксис команды DROP OPERATOR:

DROP OPERATOR оператор ( { левый_тип \ NONE } .

{ правый_тип \ NONE } )

В листинге 7.57 удаляется версия оператора ! #, работающая с двумя операндами.

Удаление последовательности

Команда SQL DROP SEQUENCE удаляет последовательность или несколько последовательностей одновременно. Синтаксис команды DROP SEQUEICE: DROP SEQUENCE последовательность [. ...]

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

В листинге 7.33 удаляется последовательность shipments_ship_id_seq.

Удаление триггера

Команда DROP TRIGGER удаляет триггер из базы данных. Удаление триггеров, как и их создание командой CREATE TRIGGER, может выполняться только владельцем триггера или суперпользователем.

Синтаксис удаления существующих триггеров:

DROP TRIGGER имя ON таблица

В листинге 7.36 приведен пример удаления триггера check_shipment, установленного для таблицы shipments.

Уничтожение функций

Функции уничтожаются владельцем или суперпользователем при помощи команды SQL DROP FUNCTION. Синтаксис команды DROP FUNCTION:

DELETE FUNCTION имя ( [ тип_аргуменга [. ...] ] ):

В листинге 7.52 приведен пример удаления функции 1sbn_to_t1tle(text). Обратите внимание: типы аргументов должны указываться обязательно, даже если функция и не перегружалась.

Уникальные индексы

Создание индекса с ключевым словом UNIQUE говорит о том, что индекс является уникальным, то есть индексируемое поле (или поля) не может содержать повторяющихся значений. Фактически создание уникального индекса эквивалентно созданию таблицы с ограничением уникальности (см. ниже подраздел «Ограничения в таблицах» в разделе «Нетривиальное использование таблиц»).

В листинге 7.3 для поля name таблицы publ ishers создается уникальный индекс unique_publ IsheMdx. Это означает, что в таблице не могут присутствовать два издательства с одинаковыми названиями.

Вставка значений в полямассивы

Вставка значений в поля-массивы

В PostgreSQL предусмотрен специальный синтаксис вставки нескольких значений в одно поле. Этот синтаксис основан на определении массивов-констант. Как упоминалось в главе 3, массив-константа (предназначенный для ссылки на массивы PostgreSQL в командах SQL) состоит из фигурных скобок, апострофов и запятых, заключенных в апострофы. Кавычки нужны только при работе с массивами строк. Таким образом, обобщенная форма массива-константы выглядит так:

'{ "текст" [. ...] }' -- массив строк

'{ число [. ...]}' -- числовой массив

В этих примерах использованы строковые и числовые массивы, но поле может определяться как массив произвольного типа (включая типы boolean, date и time). Как правило, если для описания величины в скалярном контексте должны использоваться апострофы (например, в строковых константах или данных типа timestamp), в контексте массива эта величина заключается в кавычки.

В листинге 7.18 в таблицу favorite_books вставляются две записи. Первая команда создает массив с одним элементом для работника с кодом 102, а вторая запись создает массив с двумя элементами для работника с кодом 103. В обеих командах INSERT используются массивы-константы.

Выборка из курсора

Выборка записей из курсора производится командой FETCH. Синтаксис команды

FETCH:

FETCH [ FORWARD BACKWARD | RELATIVE ]

[ число ALL | NEXT | PRIOR ]

{ IN | FROM } курсор

В этом объявлении курсор — имя курсора, из которого производится выборка записей. Курсор всегда указывает па «текущую» позицию итогового набора выполненной команды, а в выборке могут участвовать записи, находящиеся до или после текущей позиции. Направление выборки определяется ключевыми словами FORWARD и BACKUARD, но умолчанию используется прямая выборка (FORWARD). Ключевое слово RELATIVE не обязательно и поддерживается лишь для совместимости со стандартом SQL92.

ВНИМАНИЕ

В команде также может использоваться ключевое слово ABSOLUTE, но в PostgreSQL 7.1.x возможности абсолютного позиционирования и выборки в курсорах не реализованы. Курсор использует относительное позиционирование и выводит сообщение о том, что абсолютное позиционирование не поддерживается.

За ключевым словом, идентифицирующим направление, может указываться следующий аргумент — количество записей. Допускается указание конкретного числа записей (в виде целочисленной константы) или одного из нескольких ключевых слов. Ключевое слово ALL означает, что команда возвращает все записи, начиная с текущей позиции курсора. С ключевым словом NEXT (используется по умолчанию) команда возвращает следующую запись от текущей позиции курсора. С ключевым словом PRIOR возвращается запись, находящаяся перед текущей позицией курсора.

Ключевые слова IN и FROM эквивалентны, из них в команде должно присутствовать одно.

В листинге 7.43 выбираются первые четыре записи итогового набора, на который ссылается курсор all_books. Направление не указано, поэтому по умолчанию используется ключевое слово FORWARD. Далее команда FETCH с ключевым словом NEXT выбирает пятую запись, после чего команда FETCH с ключевым словом PRIOR снова возвращается к четвертой записи.

Выборка из полеймассивов

Выборка из полей-массивов

При выборке из поля-массива весь массив возвращается в формате константы, описанном в предыдущем разделе. В листинге 7.20 команда SELECT выбирает все элементы массивов в поле books таблицы favorite_books.

Закрытие курсора

Команда CLOSE закрывает ранее открытый курсор. Курсор также автоматически закрывается при выходе из транзакционного блока, в котором он находится, при фиксации транзакции командой COMMIT или ее откате командой ROLLBACK. Синтаксис команды CLOSE (курсор — имя закрываемого курсора):

CLOSE курсор

В листинге 7.45 курсор all_books закрывается и освобождает занимаемую им память, вследствие чего данные курсора становятся недоступными.