ПОИСК
Категории книг
ОПРОС
Вопрос: Какой язык программирования вы предпочитаете
С/C++
Delphi
Visual Basic
Perl
Java
PHP
ASP
Другой
ЭТО ПОЛЕЗНО!
ОБРАТНАЯ СВЯЗЬ

/ Главная / Программирование / Иллюстрированный самоучитель по SQL-сервер в Linux
Иллюстрированный самоучитель по SQL-сервер в Linux

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

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)

ВНИМАНИЕ

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



Компьютерные книги © 2006-2013
computers.plib.ru