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

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

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

Индексы

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

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

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

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

Индексы создаются командой 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. Помимо типов полей команда также выводит имена индексов таблицы.

Листинг 7.1. Построение индекса

booktown=# CREATE INDEX books_title_idx

booktown-# ON books (title);

CREATE

booktown=# \d books

Table "books" Attribute | Type | Modifier

id | integer | not null

title | text | not null

authorjd |integer |

subjectjd I integer

Indices: books_id_pkey. books_titlejdx

Установка некоторых типов ограничений (прежде всего, ограничения PRIMARY KEY и UNIQUE) также приводит к автоматическому построению индекса. В листинге 7.2 при создании таблицы authors для поля id устанавливается ограничение первичного ключа (PRIMARY KEY). В результате автоматически строится индекс authors_pkey.

Листинг 7.2. Автоматическое построение индекса

booktown=# CREATE TABLE authors (id integer PRIMARY KEY,

booktown(# last_name text,

booktown(# first_name text);

NOTICE: CREATE TABtE/PRIMARY KEY will create implicit index 'authors_pkey' for

table 'authors'

CREATE

example=# \d authors

Table "authors"

Attribute | Type Modifier

id integer | not null

lastjiame | text

firstjiame text |

Index: authors_pkey

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

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

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

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

Листинг 7.3. Создание уникального индекса

booktown-* CREATE UNIQUE INDEX unique_publisherjdx

booktown-# ON publishers (name):

CREATE

booktown=# \d publishers

Table "publishers" Attribute | Type | Modifier

id integer | not null

name | text

address j text j Indices: publishers_pkey.

urn que_publ i sher_i dx

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

ВНИМАНИЕ

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

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

Необязательная секция 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.

Листинг 7.4. Выбор типа индекса

booktown=# CREATE TABLE polygons (shape polygon):

CREATE

booktown=f CREATE INDEX spadaljdx ON polygons USING RTREE (shape);

CREATE

ВНИМАНИЕ

Если у вас нет твердой, обоснованной уверенности в том, что для конкретной ситуации лучше подойдет другой тип индекса, мы рекомендуем использовать стандартный тип BTREE.

Функциональные индексы

В слегка измененном виде команда CREATE INDEX позволяет индексировать данные не по значениям поля, а по некоторой функции этих значений. Такая форма индекса называется функциональной.

Команда создания функционального индекса имеет следующий синтаксис:

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

[ USING тип ] ( функция ( поле [. ...])[ класс ] )

Единственное отличие этой команды от описанной выше заключается в том, что индекс строится по результатам применения функции к каждому значению поля. Остальные элементы те же.

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

В листинге 7.5 приведен пример построения индекса upper_title для таблицы books. Данные индексируются по результатам применения функции upper () к полю title. Затем выполняется запрос SQL, который благодаря наличию функционального индекса выполняется более эффективно.

Листинг 7.5. Построение функционального индекса

booktown=# CREATE INDEX upperjtitlejdx ON books

booktown-f (upper(title));

CREATE

booktown=# SELECT title FROM books WHERE upper(title) = 'DUNE';

title

Dune

(1 row)

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

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

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

Листинг 7.6. Удаление индекса

booktown=# DROP INDEX upper_title_idx:

DROP

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

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

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

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

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

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

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

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

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

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

При выполнении команды \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 с несколькими ограничениями.

Листинг 7.7. Создание таблицы с ограничениями полей

booktown=# CREATE TABLE employees

booktown-f (id integer PRIMARY KEY CHECK (id > 100).

booktown(# lastjiame text NOT NULL.

booktown(# first_name text);

NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'employeesjjkey'

for table 'employees'

CREATE

В листинге 7.7 создается поле id типа integer, для которого устанавливаются ограничения PRIMARY KEY и CHECK. Ограничение PRIMARY KEY также подразумевает ограничения NOT NULL и UNIQUE и приводит к автоматическому созданию индекса empl oyees_pkey для ограничиваемого поля. Ограничение CHECK гарантирует, что значение поля id всегда больше 100. Это означает, что любые попытки вставки или обновления в таблице empl oyees записей, у которых поле id меньше либо равно 100, завершаются неудачей.

Таблица employees, созданная в листинге 7.7, также содержит текстовое поле 1 astjiame, для которого установлено ограничение NOT NULL. Это более простое ограничение запрещает появление в таблице записей, у которых поле lastjiame содержит NULL. Иначе говоря, это поле обязательно для заполнения.

ПРИМЕЧАНИЕ

Условия в секциях CHECK должны оперировать со значениями сравнимых типов данных.

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

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

[ 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 с тремя ограничениями. Развернутое описание приводится ниже.

Листинг 7.8. Использование ограничений таблицы

booktown=# CREATE TABLE editions

booktown-# (isbn text,

booktown(# bookjid integer,

booktown(# edition integer,

booktown(# publisherjd integer,

booktownCl publication date.

booktown(# type char,

booktown(# CONSTRAINT pkey PRIMARY KEY (isbn),

booktown(# CONSTRAINT integrity CHECK (bookjd IS NOT NULL

booktown(# AND edition IS NOT NULL).

booktown(# CONSTRAINT book_exists FOREIGN KEY (bookjd)

booktown(# REFERENCES books (id)

booktown(# ON DELETE CASCADE

booktown(# ON UPDATE CASCADE);

NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pkey1 for table

'editions'

NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)

CREATE

Первое ограничение, pkey, относится к типу PRIMARY KEY и устанавливается для таблицы по полю isbn. Оно практически не отличается от ограничения PRIMARY KEY для поля, поскольку список в данном примере состоит всего из одного поля.

Ограничение i ntegri ty гарантирует, что поля book i d и edi ti on не содержат псевдозначения NULL.

Наконец, ограничение book_exists при помощи конструкций FOREIGN KEY и REFERENCES гарантирует, что значение поля book_id встречается в поле id таблицы books. Более того, поскольку в секциях ON DELETE и ON ACTION встречается ключевое слово CASCADE, любые модификации поля ids таблице books приведут к каскадным изменениям записей в таблице editions, а при удалении записей из таблицы books будут удалены соответствующие записи таблицы editions.

Для этих ограничений в базе данных автоматически строится индекс editions_pkey по полю isbn, а также создается триггер. Индекс обеспечивает выполнение ограничения PRIMARY KEY, а триггер относится к ограничению FOREIGN KEY.

Добавление ограничений в существующую таблицу

Команда ALTER TABLE позволяет включать ограничения в существующую таблицу. Впрочем, в PostgreSQL 7.1.x поддерживается только возможность добавления ограничений CHECK и FOREIGN KEY.

Установка ограничений в команде ALTER TABLE имеет следующий синтаксис:

ALTER TABLE таблица

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

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

[ MATCH FULL | MATCH PARTIAL ]

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

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

[ DEFERRABLE | NOT DEFERRABLE ]

[ INITIALLY DEFERRED INITIALLY IMMEDIATE ] }

В листинге 7.9 устанавливается новое ограничение FOREIGN KEY для поля subjected, которое связывается с полем id таблицы subjects. Ограничение гарантирует, что в результате вставки или обновления данных в поле subjected таблицы books не появятся значения, отсутствующие в поле id таблицы subjects.

Листинг 7.9. Добавление ограничений в существующую таблицу

booktown=# ALTER TABLE books

booktown-# ADD CONSTRAINT legal_subjects

booktown-# FOREIGN KEY (subjectjd)

booktown-# REFERENCES subjects (id);

NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for

FOREIGN KEY check(s)

CREATE

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

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

ВНИМАНИЕ

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

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

Листинг 7.10. Удаление ограничений

booktown=*# DROP INDEX books_id_pkey;

DROP

booktown=# CREATE TABLE new_books

booktown-# (id integer CONSTRAINT books_id_pkey PRIMARY KEY.

booktown(# title text NOT NULL.

booktown(# author_id integer.

booktown(# subjected integer):

NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'books_id_pkey'

for table 'new_books'

CREATE

booktown=# INSERT INTO new_books SELECT * FROM books:

INSERT 0 15

booktown=f ALTER TABLE books RENAME TO old_books:

ALTER

booktown=# ALTER TABLE new_books RENAME TO books;

ALTER

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

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

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

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

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

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

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

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

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

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

Листинг 7.11. Создание производной таблицы

booktown=# CREATE TABLE distinguished_authors (award text)

booktown-# INHERITS (authors): CREATE

booktown=# \d distinguished_authors Table "distinguished_authors"

Attribute | Type Modifier

id | integer | not null

lastjiame text |

firstjiame | text

award text

Как видите, несмотря на то что в листинге 7.11 определено всего одно поле, таблица distinguished_authors унаследовала все поля исходной таблицы authors.

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

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

ПРИМЕЧАНИЕ

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

В листинге 7.12 в таблицу di sti ngui shed_authors заносятся данные о новом авторе Nei I Simon с текстом Pul itzer Prize в поле award. Обратите внимание: что первые три переданных значения являются общими для обеих таблиц, базовой и производной.

Листинг 7.12. Вставка данных в производную таблицу

booktown=# INSERT INTO distinguished_authors

booktown-# VALUES (nextvaK 'authorjds'),

booktown(# 'Simon'. 'Neil', 'Pulitzer Prize');

INSERT 3629421 1

Поскольку первые три поля таблицы di sti ngui shed_authors были унаследованы от таблицы authors, данные этой записи косвенно включаются в таблицу authors (хотя непосредственная вставка в таблицу authors не выполнялась). Тем не менее поле award будет присутствовать только в таблице di sti ngui shed_authors, поскольку наследование действует только в одну сторону (от родителя к потомку).

В листинге 7.13 выполняются три команды SELECT. В секциях FROM указываются разные цели, тогда как условия в секциях WHERE всех трех команд одинаковы.

Листинг 7.13. Выборка с наследованием

booktown=# SELECT * FROM distinguished_authors

booktown-# WHERE lastjiame - 'Simon';

id | last_name firstjname award

25043 | Simon | Neil | Pulitzer Prize

(1 row)

booktown=# SELECT * FROM authors WHERE last_name - 'Simon';

Id last_name first_name

25043 | Simon | Neil (1 row)

booktown=# SELECT * FROM ONLY authors WHERE

lastjname = 'Sinon': id i last_name | first_name

(0 rows)

Все три запроса в листинге 7.13 производят выборку записей, у которых поле 1 astjname совпадает со строковой константой Simon. Первый запрос извлекает данные из таблицы distinguished_authors, в которую они были первоначально занесены (см. листинг 7.12).

Второй запрос в листинге 7.13 производит выборку из таблицы authors, базовой по отношению к distinguished_authors. В этом случае запись также возвращается, но в нее включаются только поля, унаследованные таблицей di st i ngui shed_authors.

Следует хорошо понимать, что данные в действительности не заносятся в базовую таблицу, а лишь становятся видимыми в ней благодаря отношению наследования. Это доказывает третий и последний запрос в листинге 7.13, в котором перед именем таблицы authors находится ключевое слово ONLY. Оно означает, что выборка производится только из базовой таблицы, а производные таблицы игнорируются; в результате запрос не возвращает ни одной записи.

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

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

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

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

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

Листинг 7.14. Модификация базовых и производных таблиц

booktown=# UPDATE authors SET firstjiame - 'Paul'

booktown-# WHERE last_name = 'Simon';

UPDATE 1

booktown=# SELECT * FROM distinguished_authors;

id | lastjiame first_name award ---25043

Simon | Paul Pulitzer Prize

(1 row)

Ключевое слово ONLY выполняет в командах UPDATE и DELETE те же функции, что и в команде SELECT — оно предотвращает каскадные модификации, продемонстрированные в листинге 7.14. Согласно правилам синтаксиса SQL ключевое слово ONLY всегда предшествует имени производной таблицы.

Пример использования ключевого слова ONLY приведен в листинге 7.15. Сначала в таблице distinginshed_authors создается запись, в которой заполняется поле award. В результате в таблице authors появляются две разные записи для одного автора. Затем старая запись (физически находящаяся в таблице authors) удаляется командой SQL DELETE с ключевым словом ONLY.

Листинг 7.15. Модификация базовых таблиц с ключевым словом ONLY

booktown=# INSERT INTO distinguished_authors

booktown-* VALUES (1809. 'Geisel'.

booktown(# 'Theodor Seuss', 'Pulitzer Prize');

INSERT 3629488 1

booktown=# SELECT * FROM authors

booktown-# WHERE lastjname = 'Geisel':

id | lastjname | firstjiame

1809 | Geisel | Theodor Seuss 1809 | Geisel |

Theodor Seuss

(2 rows)

booktown=# DELETE FROM ONLY authors

booktown-# WHERE lastjiame = 'Geisel';

DELETE 1

В итоге после выполнения листинга 7.15 запись появляется в таблице distinguished_authors и удаляется из таблицы authors:

booktown=# SELECT * FROM authors

booktown-# WHERE lastjiame = 'Geisel':

id | lastjiame | firstjiame

1809 Geisel | Theodor Seuss

(1 row)

booktown=# SELECT * FROM distinguishedjauthors

booktown-# WHERE lastjiame = 'Geisel1;

id | lastjiame | firstjiame | award

1809 | Geisel | Theodor Seuss | Pulitzer Prize

(1 row)

Массивы

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

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

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

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

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

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

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

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

Листинг 7.16. Создание таблицы с полем-массивом

booktown=# CREATE TABLE favorite_books

booktown-# (employeejid integer, books text[]);

CREATE

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

Многомерные массивы создаются аналогичным образом, просто за первой парой квадратных скобок добавляются дополнительные пары. В листинге 7.17 создается таблица favorite_authors с целочисленным полем employee_id и многомерным массивом author_and_titles. Фактически мы создаем массив текстовых массивов.

Листинг 7.17. Создание таблицы с полем, содержащим многомерный массив

booktown=# CREATE TABLE favorite_authors (employee_id Integer,

booktowntf authors_and_titles text[][]);

CREATE

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

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

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

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

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

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

Листинг 7.18. Вставка с использованием массивов-констант

booktown=# INSERT INTO favorite_books VALUES

booktown-# (102, '{"The HitchhikerVs Guide to the Galaxy"}');

INSERT 3628399 1

booktown=# INSERT INTO favorite_books VALUES

booktown-# (103, '{"The Hobbit". "Kitten, Squared"}');

INSERT 3628400 1

Как видно из листинга, даже при вставке одного элемента массив заключается в фигурные скобки. Кроме того, обратите внимание, что апостроф в названии книги (первая команда INSERT) экранируется символом \, хотя и находится внутри кавычек. Это связано с тем, что массив-константа сначала обрабатывается как одна длинная строка, а затем интерпретируется как массив по контексту целевого поля.

При вставке значений в многомерный массив все подмассивы заключаются в отдельные фигурные скобки и разделяются запятыми. В листинге 7.19 приведен пример вставки одной записи с многомерным массивом-константой в таблицу favorite_authors, созданную в листинге 7.17.

Листинг 7.19. Вставка данных в многомерный массив

booktown=# INSERT INTO favorite_authors

bOOktown-# VALUES (102,

booktown(# '{{"J.R.R. Tolkien". "The Silmarillion"},

booktown'# {"Charles Dickens", "Great Expectations"},

booktown'l {"Ariel Denham", "Attic Lives"}}');

INSERT 3727961 1

Многомерный массив в листинге 7.19 содержит три текстовых массива, каждый из которых состоит из двух элементов. Между массивами не существует никаких систематических связей, хотя из контекста следует, что первые элементы каждого массива обозначают авторов, а вторые элементы обозначают названия книг, написанных этими авторами.

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

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

Листинг 7.20. Выборка из полей-массивов

booktown=# SELECT books FROM favorite_books;

books

{"The Hitchhiker's Guide to the Galaxy"}

{"The Hobbit"."Kitten. Squared")

(2 rows)

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

Индексы элементов

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

В отличие от таких языков программирования, как С, в PostgreSQL индексация в массивах начинается с 1, а не с 0. Так, в листинге 7.21 индекс [1] для поля books таблицы favorite_books описывает первый элемент массива. Обратите внимание: данные, возвращаемые запросом, не заключаются в кавычки или фигурные скобки. Это связано с тем, что отдельное текстовое значение должно возвращаться в виде одной текстовой константы, а не массива.

Листинг 7.21. Выборка отдельного элемента массива

booktown=# SELECT books[l] FROM favorite_books:

books

The Hitchhiker's Guide to the Galaxy The Hobbit

(2 rows)

При указании индекса несуществующего элемента массива выборка возвращает NULL. Обычно для обработки таких ситуаций используется конструкция IS NOT NULL. В листинге 7.22 приведены два запроса; первый возвращает две записи — для NULL и для названия книги. Второй запрос возвращает только название, а запись с NULL исключается из выборки в результате использования секции WHERE с проверкой условия NOT NULL.

Листинг 7.22. Предотвращение выборки NULL в массивах

booktown=# SELECT books[2] FROM favorite_books;

books

Kitten. Squared (2 rows)

booktown=# SELECT books[2] FROM favorite_books

booktown-# WHERE books[2] IS NOT NULL;

books

Kitten. Squared

(1 row)

При выборке из многомерного массива за исходным индексом перечисляются дополнительные индексы. В листинге 7.23 из таблицы favorite_authors, созданной в листинге 7.19, выбираются два элемента — имя автора и название книги.

Листинг 7.23. Выборка из многомерного массива

booktown=# SELECT authors_and_titles[l][l] AS author,

booktown-# authors_and_titles[l][2] AS title

booktown-# FROM favorite authors;

author I title

J.R.R. Tolkien | The Silmarillion

(1 row)

Срезы

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

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

Листинг 7.24. Выборка с использованием среза

booktown=# SELECT books[l:2] FROM favorite_books;

books

{"The Hitchhiker's Guide to the Galaxy"}

{"The Hobbit"."Kitten, Squared"}

(2 rows)

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

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

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

Листинг 7.25. Функция array_dims()

booktown=# SELECT array_dims(books) FROM favorite_books;

array_dims

[1:1]

[1:2]

(2 rows)

Обновление данных в полях-массивах

Существует три варианта модификации данных в полях-массивах.

Полная модификация. Все содержимое массива заменяется новыми данными, заданными в виде массива-константы. Модификация среза. Срез (то есть интервальное подмножество элементов) заменяется новыми данными, заданными в виде массива-константы. Количествоэлементов в константе должно соответствовать количеству элементов в обновляемом срезе. Модификация элемента. Отдельный элемент массива заменяется новой константой, относящейся к базовому типу данных массива. Элемент задается индексом. В первом случае количество элементов в новом массиве может не совпадать с количеством элементов в существующем массиве. Допустим, работник с кодом 1d=102 хочет добавить данные о новой книге в список, хранящийся в таблице favorite_books. Команда UPDATE, приведенная в листинге 7.26, заменяет все текущее содержимое массива.

Листинг 7.26. Полная модификация массива

booktown=# UPDATE favorite_books

booktown-# SET books='{"The HitchhikerVs Guide to the Galaxy",

booktown'# "The Restaurant at the End of the Universe"}'

booktown-# WHERE employeejd = 102;

UPDATE 1

Способ, продемонстрированный в листинге 7.26, подходит и для модификации среза массива. Для этого в конец идентификатора поля добавляется определение среза, например, books[l:3] означает первый, второй и третий элементы массива. Впрочем, на практике чаще возникает задача замены не всего массива и не среза, а отдельных элементов.

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

Листинг 7.27. Модификация отдельного элемента

booktown=# SELECT books[l] FROM favorite_books;

books

The Hitchhiker's Guide to the Galaxy The Hobbit

(2 rows)

booktown=# UPDATE favorite_books

booktown-# SET books[l] = 'There and Back Again: A HobbitVs Holiday'

booktown-# WHERE books[l] = 'The Hobbit';

UPDATE 1

booktown=# SELECT books[l] FROM favorite_books;

books

The Hitchhiker's Guide to the Galaxy

There and Back Again: A Hobbit's Holiday

(2 rows)

Автоматизация стандартных процедур

PostgreSQL является объектно-реляционной СУБД, что позволило включить в нее ряд нестандартных расширений SQL. Часть этих расширений связана с автоматизацией часто выполняемых операций с базами данных.

В этом разделе описаны две категории расширений: последовательности и триггеры.

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

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

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

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

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

Последовательности создаются командой 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 не указано, поэтому последовательность заведомо принимает уникальные значения.

Листинг 7.28. Создание последовательности

booktown-# CREATE SEQUENCE shipments_ship_id_seq

booktown-# MINVALUE 0;

CREATE

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

Команда \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.

Таблица 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 О).

Листинг 7.29. Вывод атрибутов последовательности

booktown=# SELECT 1ast_value, increment_by

booktown-# FROM shipments_ship_id_seq;

last_value increment_by

0 | 1

(1 row)

Запрос обращен к только что созданной последовательности, поэтому атрибут 1 ast_val ue остался равным нулю.

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

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

Листинг 7.30. Изменение текущего значения последовательности

booktown=# SELECT nextvaH 'shipments_shipjd_seq');

nextval

1 (1 row)

booKtown=# SELECT nextval ('shipments_ship_id_seq'):

nextval

2

(1 row)

ПРИМЕЧАНИЕ

При первом вызове функция nextval О возвращает начальное значение последовательности (заданное с ключевым словом START). Причина — функция не вызывалась, поэтому приращение еще не произошло. При всех последующих вызовах nextval () атрибут last_value изменяется.

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

Таблица 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 () в активном сеансе.

ПРИМЕЧАНИЕ

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

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

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

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

Листинг 7.31. Функция currval()

booktown=# INSERT INTO shipments (customer_id, isbn, ship_date)

booktown-# VALUES (221. '0394800753', 'now'); INSERT 3628625 1

booktown=# SELECT * FROM shipments

booktown-# WHERE Id = currval('shipments_ship_id_seq'):

id | customerjd | Isbn | ship_date

1002 ! 107 | 0394800753 | 2001-09-22 11:23:28-07

(1 row)

Наконец, функция setval () присваивает атрибуту 1 ast_val lie последовательности произвольное число из интервала допустимых значений. Первый аргумент функции содержит имя последовательности, заключенное в апострофы, а второй аргумент содержит целочисленную константу, представляющую новое значение last_value.

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

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

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

Листинг 7.32. Изменение текущего значения последовательности

booktown=# SELECT setval('shipments_ship_1d_seq'. 1010);

setval

1010

(1 row)

booktown=# SELECT nextval('shipments_ship_id_seq');

nextval

1011

(1row)

booktown=# SELECT setvalСshipments_ship_id_seq', 1010. false);

setval

1010

(1 row)

booktown=# SELECT nextval ('shipnients_ship_id_seq');

nextval

1010

(1 row)

ВНИМАНИЕ

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

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

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

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

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

Листинг 7.33. Удаление последовательности

booktown=# DROP SEQUENCE shipments_ship_id_seq;

DROP

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

SELECT p.relname. a.adsrc FROM pg_class p

JOIN pg_attrdef a ON (p.relfilenode = a.adrelid)

WHERE a.adsrc - '"последовательность"';

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

Листинг 7.34. Проверка зависимостей

Dooktown=# SELECT p.relname. a.adsrc FROM pg_class p JOIN pg_attrdef a

booktown-# ON (p.relfilenode = a.adrelid)

booktown-# WHERE a.adsrc - '"shipments_ship_id_seq"';

relname | adsrc

shipments | nextvaK'"sh1pments_ship_id_seq"'::text)

(1 row)

Триггеры

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

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

ВНИМАНИЕ

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

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

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

Триггер создается на основе существующей функции. 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 функция ( аргументы ). Имя вызываемой функции с аргументами.

ПРИМЕЧАНИЕ

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

Механизм ограничений 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() для каждой изменяемой записи.

Листинг 7.35. Создание триггера check_shipment

booktown=# CREATE TRIGGER check_shipment

booktown-# BEFORE INSERT OR UPDATE

booktown-# ON shipments FOR EACH ROW

booktown-# EXECUTE PROCEDURE check_shipment_addition();

CREATE

Триггер check_shipment настроен на выполнение функции check_shipment_addition() для команд INSERT и UPDATE, поэтому он достаточно надежно обеспечивает логическую целостность данных в полях customerjd и i sbn. Ключевое слово ROW гарантирует, что каждая добавляемая или модифицируемая запись будет обработана функцией проверки check_argument_addition().

Функция check_shipment_addition() вызывается без аргументов, поскольку для проверки записей в ней используются внутренние переменные PL/pgSQL. Реализация функции check_shipments_addition() на языке PL/pgSQL приведена в главе 11.

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

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

Таблица 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 при его создании.

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

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

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

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

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

Листинг 7.36. Удаление триггера

booktown=# DROP TRIGGER check_shipment ON shipments:

DROP

Сообщение DROP означает, что триггер успешно удален. Обратите внимание: при удалении указывается не только имя удаляемого триггера, но и имя таблицы.

Если вы не помните, в какой таблице был установлен удаляемый триггер, необходимую информацию можно получить из системных таблиц PostgreSQL. Например, можно провести объединение полей tgrelid системной таблицы pg_trigger и поля rel f i I enode системной таблицы pg_cl ass и сравнить имя триггера с полем tgname. Запрос, приведенный в листинге 7.37, возвращает имя отношения (rel name), связанного с триггером check_shipment.

Листинг 7.37. Получение имени таблицы, связанной с триггером

booktown=# SELECT relname FROM pg_class

booktown-# INNER JOIN pg_trigger

booktown-# ON (tgrelid = relfilenode)

booktown-# WHERE tgname = 'check_shipment': .

relname

shipments

(1 row)

ВНИМАНИЕ

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

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

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

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

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

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

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

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

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

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

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

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

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

Листинг 7.38. Создание транзакции

bOOktown=# BEGIN;

BEGIN

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

Транзакционный блок завершается командой SQL COMMIT, за которой также могут следовать необязательные ключевые слова WORK или TRANSACTION. В листинге 7.39 команда SQL COMMIT синхронизирует состояние базы данных с результатами команды UPDATE.

Листинг 7.39. Фиксация транзакции

booktown-# BEGIN; BEGIN

booktown=# UPDATE subjects SET location = NULL

booktown-f WHERE id = 12;

UPDATE 1

booktown=# SELECT location FROM subjects WHERE id - 12:

location

(1 row)

booktown=# COMMIT;

COMMIT

Как видно из листинга, хотя результаты команды UPDATE немедленно отражаются на выборке, выполняемой командой SELECT, другие пользователи, подключенные к той же базе данных, ничего не будут знать о них вплоть до выполнения команды COMMIT.

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

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

Листинг 7.40. Откат транзакции

booktown=# и

BEGIN

booktown=# SELECT * FROM subjects WHERE id = 12;

id | subject | location

12 | Religion | (1 row)

booktown=# UPDATE subjects SET location = 'Sunset Dr'

booktown-# WHERE id = 12;

UPDATE 1

booktown=# SELECT * FROM subjects WHERE id = 12;

id | subject | location

12 | Religion | Sunset Dr

(1 row)

booktown=# ROLLBACK;

ROLLBACK

booktown=# SELECT * FROM subjects WHERE id = 12;

id | subject I location

12 ] Religion |

(1 row)

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

(COMMIT или ROLLBACK).

Листинг 7.41. Выход из аварийного состояния

booktown=# BEGIN:

BEGIN

booktown=# SELECT * FROM;

ERROR: parser: parse error at or near ";"

booktown=# SELECT * FROM books;

NOTICE: current transaction is aborted, queries ignored until end of transaction

*ABORT STATE*

booktown=# COMMIT;

Использование курсоров

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

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

Курсоры часто обладают абстрактным представлением в прикладных интерфейсах (пример — класс PgCursor в libpq++), хотя приложение может напрямую создавать курсоры и работать с ними при помощи стандартных команд SQL. В этом подразделе описаны обобщенные принципы работы с курсорами в SQL, продемонстрированные на примере клиента psql. В PostgreSQL существуют четыре команды, предназначенные для работы с курсорами: DECLARE, FETCH, MOVE и CLOSE.

Команда DECLARE определяет и одновременно открывает курсор, после чего заполняет его информацией по результатам итогового набора выполненного запроса. Команда FETCH позволяет получить записи из открытого курсора. Команда MOVE перемещает «текущую» позицию курсора в итоговом наборе, а команда CLOSE закрывает курсор.

ПРИМЕЧАНИЕ

Если вас интересует тема использования курсоров в конкретном интерфейсе API, обращайтесь к документации на API.

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

Команда 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.

Листинг 7.42. Объявление курсора

booktown=# BEGIN;

BEGIN

booktown=# DECLARE all_books CURSOR

booktown-# FOR SELECT * FROM books;

SELECT

Сообщение SELECT в конце листинга 7.42 говорит о том, что команда была выполнена успешно, а записи, полученные в результате запроса, стали доступными для курсора a! l_books.

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

Выборка записей из курсора производится командой 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.43. Выборка записей из курсора

booktown=# FETCH 4 FROM all_books;

Id | title | authored | suojectjd

7808 | The Shining | 4156 | 9

4513 | Dune 1 1866 | 15

4267 I 2001: A Space Odyssey | 2001 | 15

1608 I The Cat in the Hat j 1809 2

(4 rows)

booktown=# FETCH NEXT FROM all_books;

id | title | authorjd | subjectjd

1590 Bartholomew and the Oobleck 1809 2

(1 row)

booktown=# FETCH PRIOR FROM all_books:

id | title | authorjd subjectjd

1608 | The Cat in the Hat | 1809 | 2

(1 row)

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

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

MOVE [ FORWARD | BACKWARD | RELATIVE ]

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

{ IN | FROM } курсор

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

Листинг 7.44. Перемещение текущей позиции курсора

booktown=# MOVE FORWARD 10

booktown-# IN all_books;

MOVE

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

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

CLOSE курсор

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

Листинг 7.45. Закрытие курсора

booktown=# CLOSE al1_books;

CLOSE

booktown=# COMMIT:

COMMIT

Расширение 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

---

---