Хранимые процедуры, функции и триггеры [АйТи бубен]
Хранимая процедура — это объект базы данных, представляющий собой набор SQL- инструкций, который компилируется один раз и хранится на сервере.
Хранимая функция отличается от хранимой пороцедуры, тем что хранимая функция всегда возвращает только скаляр(то есть единичное значение), а процедура возвращает набор значений.
- Триггер (trigger) — это хранимая процедура, которую пользователь не вызывает непосредственно, а исполнение которой обусловлено действием по модификации данных: добавлением INSERT, удалением DELETE строки в заданной таблице, или изменением UPDATE данных в определенном столбце заданной таблицы реляционной базы данных. В PostgreSQL триггеры создаются на основе существующих функции, т.е. сначала командой CREATE FUNCTION определяется триггерная функция, затем на ее основе командой CREATE TRIGGER определяется собственно триггер. Посмотреть существующие триггеры:
- Пример функции(PL/pgSQL), которая выводит текущую дату
CREATE OR REPLACE FUNCTION getDate() RETURNS text AS $BODY$ BEGIN RETURN CURRENT_TIMESTAMP; END $BODY$ LANGUAGE 'plpgsql' ;
- Функция возвращающая много строк
CREATE OR REPLACE FUNCTION g_peer() RETURNS SETOF text AS $BODY$ DECLARE r text; BEGIN FOR r IN SELECT DISTINCT "peer_id" FROM "ratesheets" ORDER BY peer_id LOOP RETURN NEXT r; END LOOP; RETURN; END $BODY$ LANGUAGE 'plpgsql'
- Функция возвращает сведения о времени последнего процесa autovacuum, autoanalyze. Возвращающей столбец записей.
CREATE OR REPLACE FUNCTION sql_last_v_a() RETURNS SETOF record AS' SELECT schemaname,relname,last_vacuum,last_autovacuum,last_analyze,last_autoanalyze FROM pg_stat_all_tables ORDER BY schemaname, relname; 'LANGUAGE sql;
Триггеры MySQL. Поддержка для триггеров включена, начиная с MySQL 5.0.2.
Триггер представляет собой именованный объект базы данных (хранимая процедура), который связан с таблицей, и он будет активизирован, когда специфическое событие INSERT, UPDATE и DELETE (вставка, обновление строки или удаление) происходит для таблицы. Триггеры могут срабатывать как до BEFORE так и после AFTER изменения таблицы.
CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt
где
trigger_name — название триггера trigger_time — Время срабатывания триггера. BEFORE — перед событием. AFTER — после события. trigger_event — Событие: insert — событие возбуждается операторами insert, data load, replace update — событие возбуждается оператором update delete — событие возбуждается операторами delete, replace. Операторы DROP TABLE и TRUNCATE не активируют выполнение триггера tbl_name — название таблицы trigger_stmt выражение, которое выполняется при активации триггера
- Просмотреть все триггеры в конкретной базе данных. Чтобы вывести все триггеры, определенных для базы данных dbname, нужно выполнить запрос к таблице INFORMATION_SCHEMA.TRIGGERS:
SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, ACTION_STATEMENT FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA='dbname';
или
SHOW TRIGGERS FROM dbname;
- Для удаления триггера нужно указывать схему
mysql> DROP TRIGGER dbname.my_trigger;
Пример MySQL триггера
Задача. Нужно после вставки (INSERT) строки в таблицу cdr, обновить (UPDATE) таблицу actions внеся в нее значение id новой записи таблицы cdr.
mysql> DELIMITER //
mysql> CREATE TRIGGER `ti_cdr` AFTER INSERT ON `mbillcc`.`cdr`
-> FOR EACH ROW
-> BEGIN
-> UPDATE actions SET state = NEW.id WHERE action = 'cdrrow_end';
-> END//
Query OK, 0 ROWS affected (0.00 sec)
mysql> DELIMITER ; NEW — для доступа к новым записям; OLD — для доступа к старым записям
загрузка…
xranimye_procedury_funkcii_i_triggery.txt · Последние изменения: 2018/07/23 07:27 — darkfire
wiki.dieg.info
Создание Хранимых функций и триггеров (важно)
Доброго времени суток.
На днях предстоит экзамен по postgressql, будут два билета про Хранимые процедуры и Триггеры.
Я в общих чертах знаю sql , но ранее с функциями и триггерами не сталкивался…
Почитал про хранимые процедуры
Храни́мая процеду́ра — объект базы данных, представляющий собой набор SQL-инструкций, который компилируется один раз и хранится на сервере.
Нашел такую разницу — Вместо хранения часто используемого запроса, клиенты могут ссылаться на соответствующую хранимую процедуру. При вызове хранимой процедуры её содержимое сразу же обрабатывается сервером.
И то что хранимая процедура вызывается с помощью функции CALL и EXECUTE.
Если есть еще какие то различия подскажите пожалуйста…
По поводу Триггеров
Триггеры предназначены для автоматического выполнения отдельных процедур в зависимости от операции, для которой они были назначены. Триггеры могут быть назначены до или после операций INSERT, UPDATE или DELETE как для случаев изменения записи в таблице так и для случая выполнения оператора SQL. Если произошло событие, на которое был назначен триггер, то вызывается закреплённая за этим триггером процедура.
Начиная с 9.0.x есть триггеры на колонки (столбцы) и кроме того, при объявлении триггера можно использовать ключевое слово WHEN, добавляющее дополнительное условие для срабатывания триггера.
Если с теорией еще более менее есть понимание что это такое… То с практикой вообще беда… А билеты по данным вопросам будут именно по части практики, врядли задания сложные будут, но сейчас на данном этапе я даже простые сделать не смогу потому что не представляю как прописывается в postgresql Хранимые процедуры и триггеры на примере какой нибудь самой простой базы данных.
Помогите, покажите простые но рабочие примеры по хранимым процедурам и тригерам на примере простой какой нибудь базе с пару таблиц, чтоб можно было оттолкнутся от этого и чтоб само понимание пришло…
Нашел вот такой пример функции , но так понимаю не хранимой, а простой, но даже это пример не до конца понимаю..
CREATE FUNCTION sales_tax(subtotal REAL) RETURNS REAL AS $$
BEGIN
RETURN subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;
Потом в теле процедуры снова встречается слово «RETURN» а именно RETURN subtotal * 0.06;
В процедуры нужно дважды указывать слово RETURN? одно в шапке а другое в теле процедуры???
Нашел другой пример той же функции
CREATE FUNCTION sales_tax(REAL) RETURNS REAL AS $$
DECLARE
subtotal ALIAS FOR $1;
BEGIN
RETURN subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;
Только добавилась часть DECLARE в которой обычно описываются переменные и фраза subtotal из шапки перенесена именно в этот раздел…
Помогите все понять это, кто уже этим хорошо владеет а когда то начинал как и я уверен сможет понятно и доходчиво описать все как есть об этих вещах, очень важно для меня, заранее спасибо..
postgresql.ru.net
plpgsql — Функция PostgreSQL или хранимая процедура, которая выводит несколько столбцов?
Если функция func возвращает только 1 строку с тремя значениями, например:
CREATE OR REPLACE FUNCTION func
(
input_val integer,
OUT output_val1 integer,
OUT output_val2 integer,
OUT output_val3 integer
)
AS $$
BEGIN
output_val1 := input_val + 1;
output_val2 := input_val + 2;
output_val3 := input_val + 3;
END;
$$ LANGUAGE plpgsql;
а затем выполните SELECT a, func(a) FROM table1, вы получите:
a | func
integer | record
========|==========
1 | (2, 3, 4)
2 | (3, 4, 5)
3 | (4, 5, 6)
но если вы выполните:
SELECT a, (f).output_val1, (f).output_val2, (f).output_val3 FROM (SELECT a, func(a) AS f FROM table1) AS x
вы получите:
a | output_val1 | output_val2 | output_val3
integer | integer | integer | integer
========|=============|=============|=============
1 | 2 | 3 | 4
2 | 3 | 4 | 5
3 | 4 | 5 | 6
или, используя CTE (Общие выражения таблицы), если вы выполните:
WITH temp AS (SELECT a, func(a) AS f FROM table1)
SELECT a, (f).output_val1, (f).output_val2, (f).output_val3 FROM temp
вы также получите:
a | output_val1 | output_val2 | output_val3
integer | integer | integer | integer
========|=============|=============|=============
1 | 2 | 3 | 4
2 | 3 | 4 | 5
3 | 4 | 5 | 6
Примечание. Вы можете также использовать следующие запросы для получения тех же результатов:
SELECT a, (f).*
FROM (SELECT a, func(a) AS f FROM table1) AS x
или
WITH temp AS (SELECT a, func(a) AS f FROM table1)
SELECT a, (f).* FROM temp
qaru.site
Краткий обзор возможностей PostgreSQL | PostgreSQL
Виктор Вислобоков
Данный обзор кратко описывает основные возможности PostgreSQL, которые заявлены на момент написания обзора. Список этих возможностей может быть неполон или неточен. В будущем возможно расширение и дополнение данного документа.
За дополнительной информацией обращайтесь на сайт PostgreSQL
Содержание
Размер базы данных
Поддерживаемые встроенные типы данных
Типы данных, создаваемые пользователем
Локализация
Языки, которые могут использоваться для написания хранимых процедур (функций)
Функции и операторы
Полнотекстовый поиск
Многоверсионный контроль конкурентых транзакций и изоляция транзакций
Журналы (логи) опережающей записи (WAL)
Репликация и технология Hot Standby
Табличные пространства (tablespaces)
Гибкая настройка сервера
Ограничения целостности
Хранимые процедуры
Триггеры
Система правил
Схемы
Роли и привелегии
Сбор статистики
Резервное копирование и восстановление
Размер базы данных
| Максимальный размер БД | Неограничен |
| Максимальный размер таблицы | 32 TB |
| Максимальный размер записи (строки) в таблице | 1.6 TB |
| Максимальный размер поля в записи (строке) | 1 GB |
| Максимальное количество записей (строк) в таблице | не ограничено |
| Максимальное количество полей (колонок) в таблице | 250 — 1600 в зависимости от типа данных в колонке |
| Максимальное количество индексов на таблицу | не ограничено |
Поддерживаемые встроенные типы данных
| Числовые типы | |
|---|---|
| smallint | короткое 2-х байтовое целое |
| integer | обычное 4-х байтовое целое |
| bigint | большое 8-байтовое целое |
| decimal | дробное с фиксированной точкой |
| numeric | дробное с фиксированной точкой |
| real | дробное с плавающей точкой |
| double precision | дробное с плавающей точкой двойной точности |
| serial | целое с автоувеличением |
| bigserial | большое целое с автоувеличением |
| Денежные типы | |
|---|---|
| money | для хранения денежных значений |
| Символьные типы | |
|---|---|
| character varying(n), varchar(n) | строка переменной длины с ограничением |
| character(n), char(n) | строка фиксированной длины |
| text | строка переменной неограниченной длины |
| Бинарные (двоичные) типы | |
|---|---|
| bytea | бинарная строка переменной длины |
| Дата и время | |
|---|---|
| timestamp [ (p) ] [ без часового пояса ] | дата и время |
| timestamp [ (p) ] с часовым поясом | дата и время с часовым поясом |
| interval [ (p) ] | интервал времени |
| date | только дата |
| time [ (p) ] [ без часового пояса ] | только время |
| time [ (p) ] с часовым поясом | только время с часовым поясом |
| Логические типы | |
|---|---|
| boolean | TRUE или FALSE |
| Геометрические типы | |
|---|---|
| point | Точка на плоскости (x,y) |
| line | Невидимая линия (не полностью реализовано) |
| lseg | Видимый отрезок ((x1,y1),(x2,y2)) |
| box | Четырёхугольник ((x1,y1),(x2,y2)) |
| path | Замкнутый многоугольник (похож на полигон) ((x1,y1),…) |
| path | Ломаная линия [(x1,y1),…] |
| polygon | Полигон (похож на замкнутый многоугольник) ((x1,y1),…) |
| circle | Круг (x,y),r (центр и радиус) |
| Типы для адресов компьютерных сетей | |
|---|---|
| cidr | IPv4 или IPv6 сеть |
| inet | IPv4 или IPv6 хост и сеть |
| macaddr | MAC адрес |
| Битовые строки | |
|---|---|
| bit [ (n) ] | битовая строка фиксированной длины |
| bit varying [ (n) ] | битовая строка переменной длины |
| Типы для поиска текста | |
|---|---|
| tsquery | запрос на поиск текста |
| tsvector | список для поиска текста |
| UUID тип | |
|---|---|
| uuid | универсальный уникальный идентификатор |
| XML типы | |
|---|---|
| xml | данные XML |
Кроме этого набора типов, PostgreSQL предоставляет возможность создания списков (тип ENUM), массивов типов, составных типов наподобие структур в языке C, а также имеет типы для уникальной идентификации объектов (OID) и псевдотипы для хранимых процедур.
Типы данных, создаваемые пользователем
С помощью команды CREATE TYPE пользователи могут создавать новые типы данных для своих нужд.
Локализация
PostgreSQL работает с локализацией, установленной в операционной системе и отвечающей стандарту POSIX. На практике это означает возможность работы с несколькими десятками языков, в том числе и с русским языком во всех возможных кодировках: koi8-r, cp1251, iso8859-5 и UTF-8. Возможность корректной работы PostgreSQL с конкретной кодировкой зависит от корректной поддержки этой кодировки средствами самой операционной системы.
Благодаря использованию библиотеки gettext, сообщения об ошибках и в утилитах переведены на многие языки, в том числе и на русский.
Языки, которые могут использоваться для написания хранимых процедур (функций)
- Java
- Perl
- Python
- Ruby
- Tcl
- C/C++
- PL/pgSQL
Функции и операторы
Для каждого из перечисленных выше типов существует свой набор функций и операторов, который делает работу с данными этого типа максимально удобной, гибкой и производительной. Перечисление этих функций и операторов занимает довольно солидный объём документации, поставляемой с PostgreSQL, поэтому подробности смотрите там.
Индексы
PostgreSQL предлагает 4-ре типа индексов: B-tree, Hash, GiST и GIN. Каждый тип индекса имеет свой алгоритм реализации, что позволяет существенно увеличить быстродействие, если для определённого вида данных выборать определённый типа индекса.
PostgreSQL позволяет создавать индексы с использованием выражений, например: CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));
PostgreSQL позволяет создавать частичные (partial) индексы, используя выражение WHERE, например: CREATE INDEX orders_unbilled_index ON orders (order_nr) WHERE billed IS NOT true;.
Полнотекстовый поиск
Начиная с версии 8.3 в ядро PostgreSQL включен функционал полнотекстового поиска (который раньше поставлялся в виде отдельного модуля-расширения).
Полнотекстовый поиск позволяет создавать запросы к текстовым документами, такие как: «найти все документы, содержащие определённые слова и выдать их в определённом порядке «, где и сами запросы и порядок выдачи могут гибко настраиваться в зависимости от конкретных потребностей.
Многоверсионный контроль конкурентых транзакций и изоляция транзакций
В PostgreSQL реализован (Multiversion Concurrency Control, MVCC) — многоверсионный контроль конкурентных транзакций, который управляет конкурентным доступом к данным на многоверсионной основе. На практике это означает, что при запросе к БД каждая транзакция видит как бы снимок данных (версию) на момент этого снимка, а не текущее состояние данных. Таким образом транзакции защищаются от просмотра нецелостных данных, которые могут ещё только формироваться другими конкурентными транзакциями в тех же самых строках таблицы. Этим же достигается изоляция транзакций для каждой сессии к БД. MMVC позволяет избегать методов явной блокировки, которые применяются в традиционных СУБД и таким образом, минимизирует блокировки данных и позволяет увеличить производительность в многопользовательской работе. Основное преимущество MMVC состоит в том, что чтение данных никогда не блокирует запись, а запись никогда не блокирует чтение.
Также в PostgreSQL реализованы традиционные схемы явных блокировок данных, применяющихся для изоляции транзаций, такие как:
- блокировка на уровне таблицы
- блокировка на уровне записи в таблице (строки)
- advisory блокировки (для собственных блокировок на уровне приложений)
Также реализовано отслеживание deadlocks (взаимных блокировок)
Журналы (логи) опережающей записи (WAL)
PostgreSQL реализует механизм WAL (журналов опережающей записи), что даёт такие преимущества как:
- Повышение производительности работы СУБД за счёт того, что записываются только сделанные изменения без переписывания всех данных в таблицах.
- Повышение надёжности хранения данных за счёт предварительного сохранения буферизируемых данных в WAL
- Возможность отката состояния БД на любой момент времени, путём применения WAL к существующей резервной копии
Репликация и технология Hot Standby
Начиная с 9.0, на основе развития WAL заработала репликация по технологии Hot Standby. Технология позволяет получить на сервере вторую базу данных, которая является актуальной копией оригинальной базы данных, доступной только на чтение. Технология может быть использована также и на удалённом сервере, который подключается к primary или master серверу и загружает с него WAL логи, предоставляя онлайновую репликацию базы данных и поддерживая копию базы данных на удалённом сервере в актуальном состоянии, а также делая эту копию доступной для запросов на чтение. Это некий аналог технологии Active DataGuard в СУБД Oracle.
Табличные пространства (tablespaces)
Табличные пространства в PostgreSQL позволяют задать место хранения объектов БД в файловой системе. Сперва создаётся табличное пространство с определённым именем. Далее, это имя может быть использовано при создании таблиц, чтобы разместить эти таблицы именно в данном табличном пространстве
Гибкая настройка сервера
Основной конфигурационный файл postgresql.conf включает более настраиваемых 150 параметров по разделам:
- Файлы и пути к ним
- Сетевые соединения
- Авторизация и безопасность
- Выделение ресурсов
- WAL — логи обратной записи
- Планирование запросов
- Ошибки и протоколирование
- Статистика запросов
- Оптимизация данных через VACUUM
- Управление блокировками
- Совместимость версий и платформ
- Настройки клиента по умолчанию
Дополнительный конфигурационный файл pg_hba.conf включает в себя настройки доступа к отдельным БД, такие как указание конкретных IP адресов и(или) сетей, с которых разрешён доступ, а также метод(ы) авторизации для доступа к БД и возможность включения безопасных (зашифрованных соединений) через SSL.
Ограничения целостности
Поддерживаются следующие ограничения целостности:
- NOT NULL — не NULL
- UNIQUE — уникальность (начиная с 9.0 введёно понятие DEFFERABLE UNIQUE)
- PRIMARY KEY — первичный ключ
- FOREIGN KEY/REFERENCES — внешний ключ, ссылки
- CHECK — проверка
- EXCLUDE — проверка уникальности по сложному условию (начиная с 9.0)
Хранимые процедуры
Хранимые процедуры в PostgreSQL могут быть написаны на любом из поддерживаемых встроенных языков. Хранимые процедуры могут быть использованы в триггерах и могут возвращать любой из поддерживаемых типов данных, а также массивы и списки.
Начиная с 9.0, вызывать хранимые процедуры можно с указанием именованных параметров, что позволяет создавать хранимые процедуры с переменным числом параметров и перегружаемые функции.
Начиная с 9.0, можно создавать функции без объявления имени (Анонимные блоки) для выполнения блока операторов на любом встроенном языке, который поддерживает PostgreSQL прямо в командной строке.
Триггеры
Триггеры предназначены для автоматического выполнения отдельных процедур в зависимости от операции, для которой они были назначены. Триггеры могут быть назначены до или после операций INSERT, UPDATE или DELETE как для случаев изменения записи в таблице так и для случая выполнения оператора SQL. Если произошло событие, на которое был назначен триггер, то вызывается закреплённая за этим триггером процедура.
Начиная с 9.0.x есть триггеры на колонки (столбцы) и кроме того, при объявлении триггера можно использовать ключевое слово WHEN, добавляющее дополнительное условие для срабатывания триггера.
Система правил
Система правил (более правильно говорить: система правил изменения запросов) позволяет изменять запросы согласно заданным правилам и затем передаёт изменённый запрос планировщику запросов для планирования и выполнения. Система правил является очень мощным инструментом и может быть использована во многих случаях таких как хранимые процедуры, представления (views) и версии.
Схемы
PostgreSQL поддерживает схемы. Схемы являются как бы дополнительными областями видимости внутри базы данных. Также схему можно сравнить и c дополнительным путём (название схемы должно указываться перед названием таблицы) и с каталогом, внутри которого можно разместить таблицы. В любой базе данных по умолчанию существует схема public, в которой по умолчанию создаются все таблицы и которую не нужно указывать специально, но администратор БД может создавать другие схемы (и разграничивать доступ к ним), что обеспечивает ещё один уровень распределения прав доступа для пользователей, позволяет выделить каждому пользователю как бы персональный раздел внутри БД с теми же самыми названиями таблиц, что и у других пользователей.
Роли и привелегии
PostgreSQL управляет привелегиями в БД, используя концепцию ролей. Ролью может являться как отдельный пользователь БД, так и группа пользователей. Роли могут являться владельцами объектов в БД (например таблиц), а также могут назначать привелегии доступа к этим объектам для других ролей. Возможно предоставить одной роли членство в другой роли и соответственно передать этой роли права той роли, членом которой она будет являться. Концепция ролей заменила старую концепцию пользователей и групп, предоставив эту же функциональность.
Начиная с 9.0 поддерживаются права на схемы, а также права по умолчанию.
Сбор статистики
Чтобы построить производительный план запроса, планировщик запросов в PostgreSQL использует так называемую статистику или статистическую информацию, собранную на основе анализа данных в таблицах, которая собирается с помощью команды ANALYZE, в свою очередь являющейся частью процесса обслуживания БД VACUUM. Начиная с версии 8.1, в PostgreSQL появилась возможность вместо ручного вызова команд сбора статистики, работать с новым инструментом, который назвали autovacuum. С помощью autovacuum весь необходимый сбор статистики и процесс обслуживания БД происходит в фоновом режиме автоматически. Исходя из настроек, PostgreSQL сам определяет таблицы, для которых необходимо провести сбор статистики и выполнить обслуживание VACUUM.
Резервное копирование и восстановление
PostgreSQL предлагает несколько режимов резервного копирования и восстановления БД. Поскольку БД располагаются в файловой системе, вполне нормальным методом является резервное копирование на уровне файлов, т.е. самого каталога где размещаются файлы БД. Единственное условие такого режима — полный останов сервера PostgreSQL. Однако, для систем высокой готовности такой режим резервного копирования недопустим, поэтому PostgreSQL позволяет выполнять резервное копирование при запущенном сервере, не прерывая его обычной работы. Наиболее простой режим — это получение дампа БД в текстовом виде (в форме операторов SQL) на стандартный вывод. Для экономии дискового пространства можно сразу же перенаправлять такой дамп на стандартный ввод утилите сжатия (например gzip). Также существует возможность создания дампа БД в двоичной форме, а также возможность задавать специальные параметры для большего удобства в получении резервной копии и её последующего восстановления.
PostgreSQL также предоставляет возможность резервного копирования WAL и за счёт этого, восстановление БД на конкретный момент времени, а также инкрементальное резервное копирование.
postgresql.ru.net