Функции и процедуры postgresql
Простая функция в PostgreSQL | Документация на русском по PostgreSQL
PostgreSQL достаточно серьёзно отличается от других языков запросов SQL. Хотя бы тем, что является типизированным. И подход PostgreSQL очень похож в некоторых местах на языки программирования. Например, на C/C++.
В PostgreSQL нет хранимых процедур. Вместо них хранимые функции. При чём функции могут и не иметь выходных данных.
Каждая функция может выводить только определённый тип данных. Пример:
/**Создаём или обновляем функцию с названием print в схеме public со входным параметром cif типа данных integer (целые числа)**/ CREATE OR REPLACE FUNCTION public.print (cif integer) /**Указываем, что типом выходных данных является integer (целые числа)**/ RETURNS integer AS /** Начинаем тело процедуры**/ $body$ /**Программные скобки**/ BEGIN /**Выводим тот самый результат cif **/ RETURN cif; /**Закрываем программные скобки**/ END; /**Закрываем тело**/ $body$ /**Указываем, что синтаксис plsql**/ LANGUAGE 'plpgsql';Конструкция CREATE OR REPLACE FUNCTION создаёт или изменяет функцию, если она создана. PUBLIC - это схема. А print - это название функции. Назвать функцию можно как угодно. Но нужно пытаться не использовать системные (зарезервированные имена). Странность PostgreSQL заключается в том, что системным именем здесь является даже "name". Переменная cif целочисленного типа integer является входными данными.
RETURNS integer ASЭта конструкция указывает, что на выходе будут данные в виде целочисленного числа.
$body$Если вы знаете хоть немного HTML, то поймёте эту конструкцию, которая отображает тело функции.
BEGINПрограммные скобки являются словесными синтаксическими конструкциями.
return cif;Выводить можно что угодно, но в данной функции это "Что угодно" должно быть целочисленным.
END;Это закрытие программных скобок.
LANGUAGE 'plpgsql';Сразу предупреждаем тех, кто хочет просто взять, скопировать код и думает, кто код тут же заработает. Возможно, это и так. На самом деле, у PGAdmin есть ошибка правильной сборки при копировании из разных источников с разным форматированием (видать, PGAdmin его как-то учитывает). Если вы хотите, чтобы указанный пример у вас заработал, перепишите его вручную или скопируйте сначала в блокнот (в Windows программа NotePad, в Linux Gedit или Nano), а после копируйте в редактор кода PGAdmin.
postgresql-help.ru
Создание Хранимых функций и триггеров (важно)
Доброго времени суток.На днях предстоит экзамен по postgressql, будут два билета про Хранимые процедуры и Триггеры.Я в общих чертах знаю sql , но ранее с функциями и триггерами не сталкивался...Почитал про хранимые процедурыХрани́мая процеду́ра — объект базы данных, представляющий собой набор SQL-инструкций, который компилируется один раз и хранится на сервере.Хочется понять разницу между обычной процедурой и Хранимой процедурой.Нашел такую разницу - Вместо хранения часто используемого запроса, клиенты могут ссылаться на соответствующую хранимую процедуру. При вызове хранимой процедуры её содержимое сразу же обрабатывается сервером.И то что хранимая процедура вызывается с помощью функции CALL и EXECUTE.Если есть еще какие то различия подскажите пожалуйста...По поводу ТриггеровТриггеры предназначены для автоматического выполнения отдельных процедур в зависимости от операции, для которой они были назначены. Триггеры могут быть назначены до или после операций INSERT, UPDATE или DELETE как для случаев изменения записи в таблице так и для случая выполнения оператора SQL. Если произошло событие, на которое был назначен триггер, то вызывается закреплённая за этим триггером процедура.
Начиная с 9.0.x есть триггеры на колонки (столбцы) и кроме того, при объявлении триггера можно использовать ключевое слово WHEN, добавляющее дополнительное условие для срабатывания триггера.
Если с теорией еще более менее есть понимание что это такое... То с практикой вообще беда... А билеты по данным вопросам будут именно по части практики, врядли задания сложные будут, но сейчас на данном этапе я даже простые сделать не смогу потому что не представляю как прописывается в postgresql Хранимые процедуры и триггеры на примере какой нибудь самой простой базы данных.Помогите, покажите простые но рабочие примеры по хранимым процедурам и тригерам на примере простой какой нибудь базе с пару таблиц, чтоб можно было оттолкнутся от этого и чтоб само понимание пришло...
Нашел вот такой пример функции , но так понимаю не хранимой, а простой, но даже это пример не до конца понимаю..CREATE FUNCTION sales_tax(subtotal REAL) RETURNS REAL AS $$BEGINRETURN subtotal * 0.06;END;$$ LANGUAGE plpgsql;В первой строчке понимаю что идет создание функции, название функции sales_tax, тип передаваемого или используемого аргумента типа REAL, А вот эта часть не совсем понятна "RETURNS REAL AS $$ " Ну RETURNS REAL я так понимаю вернуть значение типа Real, AS обычно используется для объявления псевдонима но тут не вижу какого именно псевдонима... Не понятные два значка $$ что они значат не пойму...Потом в теле процедуры снова встречается слово "RETURN" а именно RETURN subtotal * 0.06;В процедуры нужно дважды указывать слово RETURN? одно в шапке а другое в теле процедуры???Нашел другой пример той же функции
CREATE FUNCTION sales_tax(REAL) RETURNS REAL AS $$DECLAREsubtotal ALIAS FOR $1;BEGINRETURN subtotal * 0.06;END;$$ LANGUAGE plpgsql;Только добавилась часть DECLARE в которой обычно описываются переменные и фраза subtotal из шапки перенесена именно в этот раздел...Помогите все понять это, кто уже этим хорошо владеет а когда то начинал как и я уверен сможет понятно и доходчиво описать все как есть об этих вещах, очень важно для меня, заранее спасибо..
postgresql.ru.net
Краткий обзор возможностей PostgreSQL | PostgreSQL
Виктор Вислобоков
Данный обзор кратко описывает основные возможности PostgreSQL, которые заявлены на момент написания обзора. Список этих возможностей может быть неполон или неточен. В будущем возможно расширение и дополнение данного документа.
За дополнительной информацией обращайтесь на сайт PostgreSQL
Содержание
Размер базы данных
| Максимальный размер БД | Неограничен |
| Максимальный размер таблицы | 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 | универсальный уникальный идентификатор |
| 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
