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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Индексы

Индексы

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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.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 указано в условии поиска.

Создание уникального индекса

Листинг 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 может использоваться только для индексов, реализованных в виде В-дерева.

Выбор типа индекса

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

booktown=# CREATE TABLE polygons (shape polygon):

CREATE

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

CREATE

ВНИМАНИЕ

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

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

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

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

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

booktown=# DROP INDEX upper_title_idx:

DROP

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

Листинг 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. Иначе говоря, это поле обязательно для заполнения.

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

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

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

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

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

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

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

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

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

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

Выборка с наследованием

Листинг 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 может показаться обратное.

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

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

Создание таблицы с полеммассивом

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

Вставка с использованием массивовконстант

Листинг 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. Выборка из полей-массивов

booktown=# SELECT books FROM favorite_books;

books

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

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

(2 rows)

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

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

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

Предотвращение выборки 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)

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

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

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)

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

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

booktown-# CREATE SEQUENCE shipments_ship_id_seq

booktown-# MINVALUE 0;

CREATE

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

Листинг 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 остался равным нулю.

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

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

booktown=# SELECT nextvaH 'shipments_shipjd_seq');

nextval

1 (1 row)

booKtown=# SELECT nextval ('shipments_ship_id_seq'):

nextval

2

(1 row)

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

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

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

Функция currval()

Листинг 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, убедитесь в том, что вы хорошо понимаете все возможные последствия.

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

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

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

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

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

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

ВНИМАНИЕ

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

Создание транзакции

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

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

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

booktown=# BEGIN;

BEGIN

booktown=# DECLARE all_books CURSOR

booktown-# FOR SELECT * FROM books;

SELECT

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

Выборка записей из курсора

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

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

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

booktown=# MOVE FORWARD 10

booktown-# IN all_books;

MOVE

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

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

booktown=# CLOSE al1_books;

CLOSE

booktown=# COMMIT:

COMMIT

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

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

Использование функции SQL

Листинг 7.47. Использование функции SQL

booktown=# SELECT isbn_to_title('0929605942');

isbn to title

The Tell-Tale Heart

(1 row)

Созданная функция доступна для всех пользователей, обладающих соответствующими правами. Например, для выполнения функции 1sbn_to_title необходим доступ для чтения к таблицам editions и books (права пользователей описаны в главе 10).

Функции на языке С

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

---

---