Иллюстрированный самоучитель по PostgreSQL. Pl pgsql учебник

Иллюстрированный самоучитель по PostgreSQL › PL/pgSQL › Поддержка PL/pgSQL [страница - 245] | Самоучители по программированию

Поддержка PL/pgSQL

PL/pgSQL относится к семейству процедурных языков и обладает определенным сходством с процедурным языком Oracle, PL/SQL. Процедурным языком называется язык программирования, в котором желаемый результат достигается последовательностью тэгов.

Язык PL/pgSQL позволяет группировать на сервере код SQL и программные команды, что приводит к снижению затрат сетевых и коммуникационных ресурсов, обусловленных частыми запросами данных со стороны клиентских приложений и выполнением логической обработки этих данных на удаленных хостах.

В программах PL/pgSQL могут использоваться все типы данных, операторы и функции PostgreSQL. "SQL" в название PL/pgSQL указывает на то, что программист может напрямую использовать команды языка SQL в своих программах. Использование SQL в коде PL/pgSQL расширяет возможности, а также повышает гибкость и быстродействие программ. Несколько команд SQL в программном блоке PL/pgSQL выполняются за одну операцию вместо обычной обработки каждой команды.

Другой важной особенность PL/pgSQL является хорошая адаптируемость программ; функции языка совместимы со всеми платформами, на которых работает СУБД PostgreSQL.

Эта глава посвящена использованию процедурного языка PL/pgSQL при работе с базами данных.

Поддержка языков программирования реализуется отдельным объектом базы данных. Таким образом, прежде чем использовать язык PL/pgSQL, необходимо включить его поддержку в базу данных (по умолчанию PL/pgSQL устанавливается вместе с PostgreSQL). В этом разделе описана процедура включения PL/pgSQL в существующую базу данных.

Поддержка PL/pgSQL в базах данных PostgreSQL включается либо приложением createlang в командной строке, либо командой SQL CREATE LANGUAGE в клиенте (например, в psql). Команда CREATE LANGUAGE требует предварительного создания обработчика вызовов PL/pgSQL – функции, которая занимается непосредственной обработкой и интерпретацией кола PL/pgSQL.

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

ПримечаниеУстановка PL/pgSQL в базе данных templatel приводит к тому, в дальнейшем PL/pgSQL автоматически устанавливается во всех базах данных, создаваемых на основе шаблона templatel (используемого по умолчанию).

Включение поддержки PL/pgSQL при помощи psql

Команда SQL CREATE LANGUAGE предназначена для включения поддержки процедурных языков в текущую базу данных. Тем не менее перед вызовом этой команды необходимо предварительно создать обработчик вызовов функцией CREATE FUNCTION. Синтаксис создания обработчика вызовов PL/pgSQL командой CREATE FUNCTION:

CREATE FUNCTION plpgsql_call_handler() RETURNS OPAQUE AS '/библиот-ека_р05£дге5/р1 pgsql.so1 LANGUAGE 'C'

Параметр библиотека jjostgres определяет абсолютный системный путь к установленным библиотечным файлам PostgreSQL. По умолчанию это путь /usr/local/psql/lib. В листинге 11.1 приведен пример создания обработчика вызовов PL/pgSQL функцией CREATE FUNCTION (предполагается, что файл plpgsql.so находится в каталоге по умолчанию).

samoychiteli.ru

PostgreSQL : Документация: 9.6: 41.2. Структура PL/pgSQL : Компания Postgres Professional

41.2. Структура PL/pgSQL

Функции, написанные на PL/pgSQL, определяются на сервере командами CREATE FUNCTION. Такая команда обычно выглядит, например, так:

CREATE FUNCTION somefunc(integer, text) RETURNS integer AS 'тело функции' LANGUAGE plpgsql;

Если рассматривать CREATE FUNCTION, тело функции представляет собой просто текстовую строку. Часто для написания тела функции удобнее заключать эту строку в доллары (см. Подраздел 4.1.2.4), а не в обычные апострофы. Если не применять заключение в доллары, все апострофы или обратные косые черты в теле функции придётся экранировать, дублируя их. Почти во всех примерах в этой главе тело функций заключается в доллары.

PL/pgSQL это блочно-структурированный язык. Текст тела функции должен быть блоком. Структура блока:

[ <<метка>> ] [ DECLARE объявления ] BEGIN операторы END [ метка ];

Каждое объявление и каждый оператор в блоке должны завершаться символом ";"(точка с запятой). Блок, вложенный в другой блок, должен иметь точку с запятой после END, как показано выше. Однако финальный END, завершающий тело функции, не требует точки с запятой.

Подсказка

Распространённой ошибкой является добавление точки с запятой сразу после BEGIN. Это неправильно и приведёт к синтаксической ошибке.

Метка требуется только тогда, когда нужно идентифицировать блок в операторе EXIT, или дополнить имена переменных, объявленных в этом блоке. Если метка указана после END, то она должна совпадать с меткой в начале блока.

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

Комментарии в PL/pgSQL коде работают так же, как и в обычном SQL. Двойное тире (--) начинает комментарий, который завершается в конце строки. Блочный комментарий начинается с /* и завершается */. Блочные комментарии могут быть вложенными.

Любой оператор в выполняемой секции блока может быть вложенным блоком. Вложенные блоки используются для логической группировки нескольких операторов или локализации области действия переменных для группы операторов. Во время выполнения вложенного блока переменные, объявленные в нём, скрывают переменные внешних блоков с такими же именами. Чтобы получить доступ к внешним переменным, нужно дополнить их имена меткой блока. Например:

CREATE FUNCTION somefunc() RETURNS integer AS $$ << outerblock >> DECLARE quantity integer := 30; BEGIN RAISE NOTICE 'Сейчас quantity = %', quantity; -- Выводится 30 quantity := 50; -- -- Создаем вложенный блок -- DECLARE quantity integer := 80; BEGIN RAISE NOTICE 'Сейчас quantity = %', quantity; -- Выводится 80 RAISE NOTICE 'Во внешнем блоке quantity = %', outerblock.quantity; -- Выводится 50 END; RAISE NOTICE 'Сейчас quantity = %', quantity; -- Выводится 50 RETURN quantity; END; $$ LANGUAGE plpgsql;

Примечание

Существует скрытый «внешний блок», окружающий тело каждой функции на PL/pgSQL. Этот блок содержит объявления параметров функции (если они есть), а также некоторые специальные переменные, такие как FOUND (см. Подраздел 41.5.5). Этот блок имеет метку, совпадающую с именем функции, таким образом, параметры и специальные переменные могут быть дополнены именем функции.

Важно не путать использование BEGIN/END для группировки операторов в PL/pgSQL с одноименными SQL-командами для управления транзакциями. BEGIN/END в PL/pgSQL служат только для группировки предложений; они не начинают и не заканчивают транзакции. Функции и триггерные процедуры всегда выполняются в рамках транзакции, начатой во внешнем запросе — они не могут начать или завершить эту транзакцию, так как у них внутри нет контекста для выполнения таких действий. Однако блок содержащий секцию EXCEPTION создаёт вложенную транзакцию, которая может быть отменена, не затрагивая внешней транзакции. Подробнее это описано в Подразделе 41.6.6.

postgrespro.ru

PostgreSQL : Документация: 9.6: 41.11. Советы по разработке на PL/pgSQL : Компания Postgres Professional

41.11. Советы по разработке на PL/pgSQL

Хороший способ разрабатывать на PL/pgSQL заключается в том, чтобы в одном окне с текстовым редактором по выбору создавать тексты функций, а в другом окне с psql загружать и тестировать эти функции. В таком случае удобно записывать функцию, используя CREATE OR REPLACE FUNCTION. Таким образом, можно легко загрузить файл для обновления определения функции. Например:

CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS $$ .... $$ LANGUAGE plpgsql;

В psql, можно загрузить или перезагрузить такой файл определения функции, выполнив:

\i filename.sql

а затем сразу выполнять команды SQL для тестирования функции.

Ещё один хороший способ разрабатывать на PL/pgSQL связан с использованием GUI инструментов, облегчающих разработку на процедурном языке. Один из примеров такого инструмента pgAdmin, хотя есть и другие. Такие инструменты часто предоставляют удобные возможности, такие как экранирование одинарных кавычек, отладка и повторное создание функций.

41.11.1. Обработка кавычек

Код функции на PL/pgSQL указывается в команде CREATE FUNCTION в виде строки. Если писать строку в обычном порядке, внутри одинарных кавычек, то любой символ одинарной кавычки должен быть удвоен, также как и должен быть удвоен каждый знак обратной косой черты (если используется синтаксис с экранированием в строках). Удвоение кавычек в лучшем случае утомительно, а в более сложных случаях код может стать совершенно непонятным, так как легко может потребоваться полудюжина или более кавычек идущих подряд. Вместо этого при создании тела функции рекомендуется использовать знаки доллара в качестве кавычек (см. Подраздел 4.1.2.4). При таком подходе никогда не потребуется дублировать кавычки, но придётся позаботиться о том, чтобы иметь разные долларовые разделители для каждого уровня вложенности. Например, команду CREATE FUNCTION можно записать так:

CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS $PROC$ .... $PROC$ LANGUAGE plpgsql;

Внутри можно использовать кавычки для простых текстовых строк и $$ для разграничения фрагментов SQL-команды, собираемой из отдельных строк. Если нужно взять в кавычки текст, который включает $$, можно использовать $Q$, и так далее.

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

1 кавычка

В начале и конце тела функции, например:

CREATE FUNCTION foo() RETURNS integer AS ' .... ' LANGUAGE plpgsql;

Внутри такой функции любая кавычка должна дублироваться.

2 кавычки

Для строковых литералов внутри тела функции, например:

a_output := ''Blah''; SELECT * FROM users WHERE f_name=''foobar'';

При использовании знаков доллара можно просто написать:

a_output := 'Blah'; SELECT * FROM users WHERE f_name='foobar';

и именно это увидит исполнитель PL/pgSQL в обоих случаях.

4 кавычки

Когда нужны одинарные кавычки в строковой константе внутри тела функции, например:

a_output := a_output || '' AND name LIKE ''''foobar'''' AND xyz''

К a_output будет добавлено: AND name LIKE 'foobar' AND xyz

При использовании знаков доллара это записывается так:

a_output := a_output || $$ AND name LIKE 'foobar' AND xyz$$

будьте внимательны, при этом не должно быть внешнего долларового разделителя $$.

6 кавычек

Когда нужны одинарные кавычки в строковой константе внутри тела функции, при этом кавычки находятся в конце строковой константы. Например:

a_output := a_output || '' AND name LIKE ''''foobar''''''

К a_output будет добавлено: AND name LIKE 'foobar'.

При использовании знаков доллара это записывается так:

a_output := a_output || $$ AND name LIKE 'foobar'$$10 кавычек

Когда нужны две одиночные кавычки в строковой константе (это уже 8 кавычек), примыкающие к концу строковой константы (ещё 2). Вероятно, такое может понадобиться при разработке функции, которая генерирует другие функции, как показано в Примере 41.9. Например:

a_output := a_output || '' if v_'' || referrer_keys.kind || '' like '''''''''' || referrer_keys.key_string || '''''''''' then return '''''' || referrer_keys.referrer_type || ''''''; end if;'';

Значение a_output затем будет:

if v_... like ''...'' then return ''...''; end if;

При использовании знаков доллара:

a_output := a_output || $$ if v_$$ || referrer_keys.kind || $$ like '$$ || referrer_keys.key_string || $$' then return '$$ || referrer_keys.referrer_type || $$'; end if;$$;

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

postgrespro.ru

Postgres Pro Standard : Документация: 10: 41.2. Структура PL/pgSQL : Компания Postgres Professional

41.2. Структура PL/pgSQL

CREATE FUNCTION somefunc(integer, text) RETURNS integer AS 'тело функции' LANGUAGE plpgsql;

PL/pgSQL это блочно-структурированный язык. Текст тела функции должен быть блоком. Структура блока:

[ <<метка>> ] [ DECLARE объявления ] BEGIN операторы END [ метка ];

Подсказка

Примечание

postgrespro.ru

Иллюстрированный самоучитель по PostgreSQL › PL/pgSQL [страница - 244] | Самоучители по программированию

PL/pgSQL

PL/pgSQL относится к семейству процедурных языков и обладает определенным сходством с процедурным языком Oracle, PL/SQL. Процедурным языком называется язык программирования, в котором желаемый результат достигается последовательностью тэгов.
Язык PL/pgSQL имеет относительно простую структуру, что объясняется в основном тем, что каждый логически обособленный фрагмент кода существует в виде функции.
Переменные используются в программах PL/pgSQL для хранения изменяемых данных заранее определенного типа. Все переменные программного блока должны быть предварительно объявлены с ключевым словом DECLARE.
Тип величины, возвращаемой функцией PL/pgSQL, должен соответствовать типу возвращаемого значения, указанному при создании функции командой CREATE FUNCTION. Значение возвращается командой RETURN. Команда RETURN находится в конце функции, но она также часто встречается в командах IF или других командах, осуществляющих передачу управления в программе.
Команды передачи управления существуют практически во всех современных языках программирования, и PL/pgSQL не является исключением. С технической точки зрения сам вызов функции можно рассматривать как передачу управления последовательности команд PL/pgSQL.
Другую категорию команд, передающих управление внутри функций, составляют циклы. В циклах используются разные виды итераций, предназначенные для решения разных задач. Итеративные вычисления значительно расширяют возможности функций PL/pgSQL.
Команда RAISE предназначена для инициирования ошибок и исключений в функциях PL/pgSQL. Она передает заданную информацию механизму PostgreSQL elog (стандартное средство ведения протокола ошибок – данные обычно направляются в файл /var/log/messages или $PGDATA/serverlog с одновременным выводом в поток stderr). | В команде RAISE также указывается уровень ошибки и строка, передаваемая PostgreSQL.
Определения триггеров PostgreSQL могут содержать ссылки на триггерные функции (то есть функции, которые должны вызываться при срабатывании триггера), написанные на языке PL/pgSQL Триггер определяет операцию, которая должна выполняться при наступлении некоторого события в базе данных.

Если Вы заметили ошибку, выделите, пожалуйста, необходимый текст и нажмите CTRL + Enter, чтобы сообщить об этом редактору.

samoychiteli.ru

PostgreSQL : Документация: 9.6: 41.1. Обзор : Компания Postgres Professional

PL/pgSQL это процедурный язык для СУБД PostgreSQL. Целью проектирования PL/pgSQL было создание загружаемого процедурного языка, который:

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

Функции PL/pgSQL могут использоваться везде, где допустимы встроенные функции. Например, можно создать функции со сложными вычислениями и условной логикой, а затем использовать их при определении операторов или в индексных выражениях.

В версии PostgreSQL 9.0 и выше, PL/pgSQL устанавливается по умолчанию. Тем не менее, это по-прежнему загружаемый модуль и администраторы, особо заботящиеся о безопасности, могут удалить его при необходимости.

41.1.1. Преимущества использования PL/pgSQL

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

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

PL/pgSQL позволяет сгруппировать блок вычислений и последовательность запросов внутри сервера базы данных, таким образом, мы получаем силу процедурного языка и простоту использования SQL при значительной экономии накладных расходов на клиент-серверное взаимодействие.

Исключаются дополнительные обращения между клиентом и сервером
Промежуточные ненужные результаты не передаются между сервером и клиентом
Есть возможность избежать многочисленных разборов одного запроса

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

Кроме того, PL/pgSQL позволяет использовать все типы данных, операторы и функции SQL.

41.1.2. Поддерживаемые типы данных аргументов и возвращаемых значений

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

Использование маркера VARIADIC позволяет объявлять функции на PL/pgSQL с переменным числом аргументов. Это работает точно так же, как и для функций на SQL, как описано в Подразделе 36.4.5.

Функции на PL/pgSQL могут принимать и возвращать полиморфные типы anyelement, anyarray, anynonarray, anyenum и anyrange. В таких случаях фактические типы данных могут меняться от вызова к вызову, как описано в Подраздел 36.2.5. Пример показан в Подразделе 41.3.1.

Функции на PL/pgSQL могут возвращать «множества» (или таблицы) любого типа, которые могут быть возвращены в виде одного объекта. Такие функции генерируют вывод, выполняя команду RETURN NEXT для каждого элемента результирующего набора или RETURN QUERY для вывода результата запроса.

Наконец, при отсутствии полезного возвращаемого значения функция на PL/pgSQL может возвращать void.

Функции на PL/pgSQL можно объявить с выходными параметрами вместо явного задания типа возвращаемого значения. Это не добавляет никаких фундаментальных возможностей языку, но часто бывает удобно, особенно для возвращения нескольких значений. Нотация RETURNS TABLE может использоваться вместо RETURNS SETOF.

Конкретные примеры рассматриваются в Подразделе 41.3.1 и Подразделе 41.6.1.

postgrespro.ru

Postgres Pro Standard : Документация: 9.6: 40.2. Структура PL/pgSQL : Компания Postgres Professional

40.2. Структура PL/pgSQL

CREATE FUNCTION somefunc(integer, text) RETURNS integer AS 'тело функции' LANGUAGE plpgsql;

PL/pgSQL это блочно-структурированный язык. Текст тела функции должен быть блоком. Структура блока:

[ <<метка>> ] [ DECLARE объявления ] BEGIN операторы END [ метка ];

Подсказка

Примечание

Существует скрытый «внешний блок», окружающий тело каждой функции на PL/pgSQL. Этот блок содержит объявления параметров функции (если они есть), а также некоторые специальные переменные, такие как FOUND (см. Подраздел 40.5.5). Этот блок имеет метку, совпадающую с именем функции, таким образом, параметры и специальные переменные могут быть дополнены именем функции.

postgrespro.ru