Аргументы

При вызове функции PL/pgSQL могут получать аргументы различных типов. В аргументах пользователь передает исходные данные, необходимые для работы функции. Аргументы делают функции PL/pgSQL более универсальными и значительно расширяют область их возможного применения. Список аргументов приводится после имени функции в круглых скобках и разделяется запятыми.

Количество и типы аргументов должны соответствовать первоначальному определению функции. В листинге 11.21 приведены примеры двух вызовов функции из клиента psql.

Атрибут %ROWTYPE

Атрибут UROWTYPE используется в PL/pgSQL для переменной-записи, имеющей одинаковую структуру с записями заданной таблицы. Не путайте атрибут &ROWTYPE с типом данных RECORD — переменная с атрибутом OTWTYPE точно воспроизводит структуру записи конкретной таблицы, а переменная RECORD не структурирована и ей можно присвоить запись любой таблицы.

В листинге 11.30 приведена перегруженная версия функции get_author() (см. листинг 11.28). Она делает то же, что и прототип, но получает аргумент типа integer вместо text и ищет автора, сравнивая код с переданным аргументом.

Обратите внимание: в реализации функции используется переменная, объявленная с атрибутом UROWTYPE. Возможно, в данном случае применение UROWTYPE только напрасно усложняет очень простую задачу, но по мере изучения PL/pgSQL важность атрибута &ROWTYPE становится все более очевидной.

Точка (.) после имени переменной found_author в листинге 11.30 используется для ссылки на имя поля, входящего в структуру found_author.

Атрибут %TYPE

Атрибут UTYPE используется при объявлении переменных с типом данных, совпадающих с типом некоторого объекта базы данных (чаще всего поля). Синтаксис объявления переменной с атрибутом UYPE приведен в листинге 11.27.

Атрибуты

Для упрощения работы с объектами базы данных в PL/pgSQL существуют атрибуты переменных — ШРЕ и UROWTYPE. Атрибуты требуются для объявления переменной, тип которой совпадает с типом объекта базы данных (атрибут ШРЕ) или структурой записи (атрибут UROWTYPE). Переменные объявляются с атрибутами в том случае, если они будут использоваться в программном блоке для хранения значений, полученных от объекта базы данных. Таким образом, при объявлении переменной с атрибутом знать тип объекта базы данных не обязательно. Если в будущем тип изменится, то переменная также автоматически переключится на новый тип данных, причем это не потребует дополнительных усилий со стороны программиста.

Безусловный цикл

Ключевое слово LOOP начинает простейший безусловный цикл. Команды безусловного цикла выполняются до тех пор, пока не будет достигнуто ключевое слово EXIT. За ключевым словом EXIT может следовать секция WHEN с выражением, определяющим условие выхода. Выражение должно относиться к логическому типу. Например, оно может проверять, достигла ли переменная некоторой величины. Ниже приведен синтаксис безусловного цикла (без ключевого слова LOOP):

LOOP

команде:

[...]

END LOOP:

Команда EXIT завершает работу безусловного цикла и может дополнительно содержать метку и/или условие завершения.

Метка представляет собой произвольный идентификатор, заключенный между префиксом « и суффиксом ». Чтобы назначить метку циклу, следует расположить ее непосредственно перед началом цикла. Синтаксис определения цикла с меткой:

«метка»

LOOP

[...]

END LOOP:

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

Если команда EXIT содержит условие, цикл прерывается только в том случае, если это условие истинно.

Синтаксис вызова EXIT в цикле LOOP:

[ «метка» ]

LOOP

statement;

[...]

EXIT [ метка ] [ WHEN условие ]:

END LOOP:

В листинге 11.42 приведен пример безусловного цикла и команды EXIT, завершающей цикл при выполнении некоторого условия. Функция square_i ntegerj oop() возводит целое число в квадрат (умножает его само на себя) до тех пор, пока его значение не превысит 10 000, после чего возвращает полученный результат.

Блоки

Программы PL/pgSQL состоят из блоков. Такой метод организации программного кода обычно называется блочной структурой. Программные блоки вводятся в командах SQL CREATE FUNCTION, которые используются для определения функций PL/pgSQL в базах данных PostgreSQL. Команда CREATE FUNCTION определяет имя функции, типы ее аргументов и возвращаемого значения. Основной блок функции начинается с секции объявлений.

Все переменные объявляются (а также могут инициализироваться значениями по умолчанию) в секции объявлений программного блока. В объявлении указывается имя и тип переменной. Секция объявлений обозначается ключевым словом DECLARE, а каждое объявление завершается символом точки с запятой (;).

После объявления переменных следует ключевое слово BEGIN, обозначающее начало основного программного блока. За ключевым словом BEGIN находятся команды, входящие в блок.

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

Структура программного блока PL/pgSQL он пеана в листинге 11.5.

Цикл FOR

Возможно, циклы FOR — самая важная разновидность циклов, реализованных в PL/ pgSQL. Цикл FOR выполняет программный блок для целых чисел из заданного интервала. У циклов FOR в PL/pgSQL существуют аналоги в других процедурных языках программирования (например, С).

Заголовок цикла FOR начинается с объявления целочисленной переменной, управляющей выполнением цикла. Затем указывается интервал принимаемых ею значений, а далее следует блок команд. Управляющая переменная уничтожается сразу же после выхода из цикла, причем ее не нужно объявлять в секции объявлений блока. Синтаксис цикла FOR:

[ «метке» ]

FOR переменная IN [ REVERSE ] выражение! . . выражение? LOOP

команда:

[...]

END LOOP:

Цикл FOR выполняет одну итерацию для каждого значения переменной переменная в интервале, границы которого определяются выражениями выражение! и выражекие2 (включительно). В начале цикла переменная инициализируется значением выражения выражение! и увеличивается на 1 после каждой итерации. Если в заголовке цикла присутствует ключевое слово REVERSE, то переменная не увеличивается, а уменьшается.

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

Управляющую переменную цикла не обязательно объявлять вне блока FOR, если вы не собираетесь работать с ней после завершения цикла.

Циклы FOR также используются для перебора результатов запросов. Пример приведен в листинге 11.45, где цикл FOR работает с переменными RECORD и &ROWTYPE. Синтаксис цикла FOR с перебором записей:

[ «метка» ]

FOR { переменная_record %пврененная_гоы1уре } IN xonaHaa_select LOOP

команда :

[...]

END LOOP:

В листинге 11.45 функция extract_all_titles() получает из базы данных список всех названий книг, упорядоченных по теме. Если по какой-либо теме в базе данных не находится ни одной книги, выводится пустая строка. Список возвращается в виде текстовой переменной. Перебор тем по кодам в функции extract_a1I_tit1es() осуществляется в цикле FOR.

Внутри первого цикла FOR находится другой, вложенный цикл FOR. Он перебирает все книги в базе данных и отбирает те из них, у которых поле subjectj d совпадает с управляющей переменной исходного цикла (текущим кодом темы). В листинге 11.45 управляющая переменная i инициализируется нулевым значением, поскольку нумерация кодов тем в таблице subjects начинается с 0.

Цикл WHILE

Цикл WHILE выполняет блок команд до тех пор, пока заданное условие не станет ложным. При каждой итерации цикла WHILE условие проверяется перед выполнением первой команды блока, и если условие равно TRUE — блок выполняется. Таким образом, если условие никогда не становится равным FALSE, блок выполняется в бесконечном цикле вплоть до принудительного завершения клиентского процесса. Синтаксис цикла WHILE:

[ «метка» ]

WHILE условие LOOP

команда:

[...]

END LOOP;

В листинге 11.44 циклы WHILE продемонстрированы на примере функции add_two_loop(). Функция увеличивает число на 1 до тех пор, пока не будет достигнуто некоторое пороговое значение. Начальное и конечное значения передаются функции в виде аргументов. Обозначение !=, встречающееся в листинге 11.44, является оператором неравенства. В данном примере условие означает следующее: цикл WHILE продолжает выполняться, пока переменная result не равна переменной highjiumber. Иначе говоря, цикл WHILE завершается в тот момент, когда переменная result становится равной highjiumber.

Циклы

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

В PL/pgSQL реализованы три типа циклов: простейший (безусловный) цикл, цикл WHILE и цикл FOR. Вероятно, из этих трех циклов чаще всего применяется цикл FOR, подходящий для широкого круга задач программирования, хотя и другие циклы также достаточно часто встречаются на практике.

Хороший стиль комментирования

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

Дальнейшие примеры кода PL/pgSQL снабжаются подробными комментариями. Это сделано для того, чтобы новичкам в программировании PL/pgSQL было проще освоить язык и его практические применения.

Команда IF/THEN

Команда IF/THEN задает команду (или блок команд), выполняемых в случае истинности некоторого условия. Синтаксис команды IF/THEN показан в листинге 11.34.

Команда IF/THEN/ELSE

В команде IF/THEN/ELSE задаются два блока команд. Первый блок выполняется в том случае, если условие истинно, а второй — если оно ложно. Синтаксис команды IF/THEN/ELSE приведен в листинге 11.37.

Команда IF/THEN/ELSE/IF

Команда IF/THEN/ELSE/IF предназначена для последовательной проверки нескольких условий. Сначала проверяется первое условие; если оно окажется равным FALSE, проверяется следующее условие и т. д. Последняя секция ELSE содержит команды, выполняемые в том случае, если пи одно из проверенных условий не было истинным. Синтаксис команды IF/THEN/ELSE/IF:

CREATE FUNCTION функция (аргументы) RETURNS тип AS '

DECLARE

объявление BEGIN

IF условие THEN команда;

[...]

ELSE IF условие команда;

[...]

END IF:

END:

' LANGUAGE 'plpgsql ' :

В листинге 11.40 приведен практический пример применения функции с командой IF/THEN/ELSE/IF. Функция books_by_subject() сначала использует переданный аргумент (тему книги) для выборки кода темы. Затем первая команда IF проверяет, не содержит ли переданный аргумент строку al 1.

Если при вызове был передай аргумент all, команда IF/THEN вызывает функцию extract_al l_titles() и присваивает полученный список книг и тем (возвращаемый в виде текстовой переменной) переменной found_text.

Если аргумент отличен от all, следующая команда ELSE IF проверяет, является ли код темы нулем или положительным числом. Если значение sub_i d больше либо равно нулю, выполняются команды, содержащиеся в теле конструкции ELSE IF -сначала вызывается функция extract_titl e(), которая возвращает список всех существующих книг по заданной теме, после чего тема возвращается вместе с полученным списком.

Затем другая команда ELSE IF сравнивает код темы с псевдозначением NULL. Если значение subjd равно NULL, значит, переданная при вызове функции тема не встречается в базе данных booktown, а выполненная в самом начале команда SELECT INTO завершилась неудачей. В этом случае функция возвращает строку «subject not found».

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

Функции extract_all_titles() и extract_title(), используемые в листинге 11.40, будут рассмотрены ниже, когда речь пойдет о циклах.

Команды

Команда выполняет некоторое действие в коде PL/pgSQL — например, присваивает значение переменной или выполняет запрос. Последовательность команд в программных блоках PL/pgSQL определяет порядок выполнения действий в этом блоке. Большая часть команд обычно размещается в основной части блока, находящейся между ключевыми словами BEGIN и END. Некоторые команды также могут присутствовать в секции объявлений (после ключевого слова DECLARE), но они всего лишь объявляют и/или инициализируют переменные, используемые в программном блоке.

Каждая команда завершается символом точки с запятой (;). В этом прослеживается сходство с языком SQL, в котором команды завершаются этим же символом. Почти вся оставшаяся часть этой главы посвящена типам команд, их использованию и основным задачам, решаемым при помощи команд в PL/pgSQL.

Команды и выражения

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

pgSQL поддерживаются два вида комментариев,

Комментарии

В PL/ pgSQL поддерживаются два вида комментариев, у которых имеются аналоги в других языках программирования: однострочные и блочные (многострочные) комментарии.

Конкатенация

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

Конкатенация используется только со строками. Оператор конкатенации (| ) ставится между объединяемыми компонентами (литералами или строковыми переменными). Вы можете объединить две строки в одну составную строку или сформировать более сложную комбинацию из нескольких исходных строк.

В листинге 11.32 операция конкатенации создает строку, возвращаемую функцией.

Создание обработчика вызовов PL/pgSQL

Листинг 11.1. Создание обработчика вызовов PL/pgSQL

booktown=# CREATE FUNCTION plpgsql_call_handler ()

booktown-# RETURNS OPAQUE

booktown-# AS '/usr/local/pgsql/lib/plpgsql.so'

booktown-l LANGUAGE 'C';

CREATE

В листинге 11.1 создается только обработчик, а поддержка языка включается командой CREATE LANGUAGE. Синтаксис включения PL/pgSQL в базу данных:

CREATE LANGUAGE 'plpgsqV HANDLER plpgsql_call_handler

LANCOMPILER 'PL/pgSQL'

Здесь pi pgsql — обозначение языка, pi pgsql _ca! IJiand I er — имя обработчика, созданного командой CREATE FUNCTION (см. листинг 11.1), а строковая константа PL/ pgSQL, следующая за ключевым словом LANCOMPILER, содержит произвольное описание.

В листинге 11.2 команда CREATE LANGUAGE включает в базу данных booktown поддержку PL/pgSQL.

Включение поддержки

Листинг 11.2. Включение поддержки PL/pgSQL командой CREATE LANGUAGE

booktown=# CREATE LANGUAGE 'plpgsql' HANDLER pipgsql_call_handler

booktown-# LANCOMPILER 'PL/pgSQL';

CREATE

За ключевым словом HANDLER указывается то же имя, которое было указано при создании обработчика вызовов. В листинге 11.1 создавался обработчик с именем plpgsql_cal IJiandler, поэтому в листинге 11.2 используется то же имя.

Секция LANCOMPILER считается устаревшей, она унаследована от старых версий и ни на что не влияет. Несмотря на это, в PostgreSQL 7.1.x эта секция является обязательной. Обычно в ней вводится краткое описание языка.

Использование приложения

Листинг 11.3. Использование приложения createlang суперпользователем базы данных

$ cd /usr/local/pgsql/bin

booktown=# createlang plpgsql booktown

Передача имени суперпользователя

Листинг 11.4. Передача имени суперпользователя при вызове createlang

$ cd /usr/local/pgsql/bin

$ createlang plpgsql -U manager booktown

При успешном завершении программа createlang возвращает управление режиму командной строки без какого-либо вывода.

Структура программного блока PL/pgSQL

Листинг 11.5. Структура программного блока PL/pgSQL

CREATE FUNCTION идентификатор (аргументы) RETURNS тип AS '

DECLARE

объявление: [...]

BEGIN команда: [...]

END:

' LANGUAGE 'plpgsql':

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

Вложенные блоки упрощают структуру кода в больших функциях PL/pgSQL. Структура вложенных блоков не отличается от структуры обычных блоков: они также начинаются с ключевого слова DECLARE, за которым следует ключевое слово BEGIN и последовательность команд, а затем ключевое слово END.

Однострочный комментарий

Листинг 11.6. Однострочный комментарий

-- This will be interpreted as a single-line comment.

Комментарии второго типа — блочные комментарии — знакомы каждому, кто когда-либо программировал на других языках. Блочный комментарий начинается с последовательности символов /* и завершается последовательностью */. Они могут распространяться на несколько строк, при этом весь текст между начальной и завершающей парой /* и */ считается комментарием. Пример блочного комментария приведен в листинге 11.7.

Блочный комментарий

Листинг 11.7. Блочный комментарий

* This is a

* block comment. */

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

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

Использование выражений

Листинг 11.8. Использование выражений

CREATE FUNCTION a_function () RETURNS int4 AS '

DECLARE

an_integer 1nt4;

BEGIN

an_integer := 10 * 10:

return an_integer;

END:

' LANGUAGE 'plpgsql';

Результат вызова функции a_function()

Листинг 11.9. Результат вызова функции a_function()

booktown=# SELECT a_function() AS output:

output

100

(1 row)

Если не считать динамических запросов (запросов SQL, выполняемых с ключевым словом EXECUTE), все выражения PL/pgSQL в функциях обрабатываются только один раз на протяжении работы серверного процесса PostgreSQL. Поскольку выражения обрабатываются однократно, константные значения (такие, как временные метки now и current) в выражениях PL/pgSQL тоже обрабатываются только один раз, что приводит к нарушению работы программ, требующих интерпретации констант на стадии выполнения. В листинге 11.10 показано, как в PL/pgSQL организуется обработка константных временных меток во время работы функции.

Функция add_sh1pment в листинге 11.11 использует многие приемы и особенности языка, описанные ниже в этой главе. Функция получает код покупателя и код ISBN книги и вычисляет код следующей поставки, увеличивая на 1 текущий максимальный код поставки, после чего вставляет запись в таблицу shipments с временной меткой now.

Если бы метка now использовалась непосредственно в команде INSERT INTO, то строка now была бы преобразована в текущее время при создании функции и полученное значение использовалось бы во всех последующих вызовах функции.

Правильное использование временных меток

Листинг 11.10. Правильное использование временных меток

CREATE FUNCTION add_shipment (integer, text) RETURNS timestamp AS '

DECLARE

-- Объявление псевдонимов для аргументов функции,

customerjd ALIAS FOR $1:

isbn ALIAS FOR $2;

-- Объявление переменных для хранения кода поставки и текущего времени.

shipment_1d integer;

rightjiow timestamp;

BEGIN

-- Присвоить переменной текущего времени строку ''now'

right_now := ''now'':

-- Упорядочить существующие поставки по убыванию кодов

-- и присвоить первый код переменной shipment_id.

SELECT INTO shlpmentjd id FROM shipments ORDER BY id DESC:

-- Увеличить переменную shipment_id на 1.

shipment_id := shipment_id + 1:

-- Вставить запись в таблицу shipments.

-- Переменная rightjiow преобразуется к временной пометке на стадии

-- выполнения программы, вследствие чего константное значение now

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

INSERT INTO shipments VALUES ( shipmentjd. customeMd. isbn. rightjnow ):

-- Вернуть временную пометку, используя константу now.

RETURN rightjiow:

END:

' LANGUAGE 'plpgsql';

Объявление переменной PL/pgSQL

Листинг 11.11. Объявление переменной PL/pgSQL

имя_переменной тип_данных [ := значение ]:

Таким образом, объявление состоит из имени и типа переменной (следующих именно в этом порядке) и завершается символом точки с запятой (;).

В листинге 11.12 приведены объявления переменных типов integer, varchar (число в круглых скобках обозначает максимальную длину строки в символах) и f I oat.

Объявление переменных

Листинг 11.12. Объявление переменных

CREATE FUNCTION identifier (arguments) RETURNS type AS '

DECLARE

-- Объявить числовую переменную типа integer

subjectjd integer:

-- Объявить строковую переменную переменной длины.

book_title varchar(10);

-- Объявить вещественную числовую переменную, book price float:

BEGIN

команды END:

' LANGUAGE 'plpgsql':

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

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

Ключевое слово DEFAULT определяет значение по умолчанию для переменной. Вместо него можно воспользоваться оператором :=, эффект будет тем же.

Ниже приведен расширенный синтаксис объявления переменной:

имя_переменной [ CONSTANT ] тип_данных [ NOT NULL ]

[ { DEFAULT | :- } значение ]:

В листинге 11.13 приведены примеры объявлений целочисленной константы, равной 5, переменной со значением 10, которой не может быть присвоено псевдозначение NULL, и символьной переменной, содержащей символ «а».

Объявления переменных

Листинг 11.13. Объявления переменных

CREATE FUNCTION example_function О RETURNS texi AS '

DECLARE

-- Объявление целочисленной константы.

-- инициализированной значением 5.

five CONSTANT integer := 5:

-- Объявление целочисленной переменной.

-- инициализированной значением 10.

-- Переменной не может присваиваться NULL,

ten integer NOT NULL := 10;

-- Объявление символьной переменной.

-- инициализированной значением "а",

letter char DEFAULT ' 'а" ;

BEGIN

-- Функция возвращает символ и прекращает работу,

return letter;

END;

' LANGUAGE 'plpgsql':

ВНИМАНИЕ

Ключевое слово RENAME, упоминаемое в электронной документации PL/pgSQL и предназначенное для переименования существующих переменных, не работает в PostgreSQL 7.1.x. Использование этого ключевого слова с существующей переменной приводит к ошибке лексического анализатора. По этой причине ключевое слово RENAME не упоминается в этой главе.

Инициализация переменной

Листинг 11.14. Инициализация переменной

CREATE FUNCTION идентификатор (аргументы) RETURNS тип AS '

DECLARE

anjnteger int4 := 10:

BEGIN

команда: С...]

END;

' LANGUAGE 'plpgsql':

Возможен н другой вариант — присваивание переменной результата запроса командой SELECT INTO. He путайте этот вариант использования команды SELECT INTO с командой SQL SELECT INTO, которая заносит результаты запроса в новую таблицу.

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

Для сохранения результатов запроса в новой таблице в PL/pgSQL используется альтернативный синтаксис SQL CREATE TABLE AS SELECT.

Команда SELECT INTO в основном требуется для сохранения данных записей в переменных, объявленных с типами UROWTYPE и RECORD. Чтобы команда SELECT INTO могла использоваться с обычной переменной, тип этой переменной должен соответствовать типу поля, упоминаемому в команде SQL SELECT. Синтаксис команды SELECT INTO:

CREATE FUNCTION идентификатор (аргументы) RETURNS тип AS '

DECLARE

команде:

BEGIN

SELECT INTO переменная [. ...] поле [, ...] секции_select;

END:

' LANGUAGE 'plpgsql':

В этом описании переменная — имя переменной, участвующей в присваивании, a ceKit,uu_select — любые поддерживаемые секции команды SQL SELECT, обычно следующие за списком целевых полей в команде SELECT.

В листинге 11.15 приведена простая функция, в которой используется команда SELECT INTO. Ключевое слово ALIAS описано в подразделе «Аргументы» этого раздела. Примеры выполнения команды SELECT INTO для переменных типа RECORD и OTWTYPE приведены в разделе «Передача управления».

Использование команды SELECT INTO

Листинг 11.15. Использование команды SELECT INTO

CREATE FUNCTION get_customer_id (text.text) RETURNS integer AS '

DECLARE

-- Объявление псевдонимов для аргументов.

Ijiame ALIAS FOR $1: f_name ALIAS FOR $2:

-- Объявление переменной для хранения кода клиента,

customerjd integer;

BEGIN

-- Получение кода клиента, имя и фамилия которого

-- совпадают с переданными значениями.

SELECT INTO customerjd id FROM customers

WHERE lastjiame = l_name AND firstjiame = fjiame;

-- Вернуть код. RETURN customerjd:

END;

' LANGUAGE 'plpgsql';

В листинге 11.16 показан результат вызова функции get_customer_id() с аргументами Jackson и Annie. Возвращенное число равно коду клиента «Annie Jackson» в таблице customers.

Вызов функции get_customer_id()

Листинг 11.16. Вызов функции get_customer_id()

booktown=# SELECT get_customer_id{'Jackson','Annie');

get_customer_id

107

(1 row)

Если требуется присвоить несколько значений нескольким переменным, в команду включаются две группы, разделенные запятыми и отделенные друг от друга пробелом. В первой группе перечисляются имена переменных, а во второй — имена полей. Функция, приведенная в листинге 11.17, решает обратную задачу по сравнению с функцией get_customer_id() из листинга 11.15 — она возвращает имя и фамилию клиента по заданному коду.

Использование команды

Листинг 11.17. Использование команды SELECT INTO с несколькими полями

CREATE FUNCTION get_customer_name (integer) RETURNS text AS '

DECLARE

-- Объявление псевдонимов для аргументов,

customerjd ALIAS FOR $1:

-- Объявление переменных для хранения компонентов

-- полного имени клиента.

customer_fname text: customer_lname text:

BEGIN

-- Получение имени и фамилии клиента, код которого

-- совпадает с переданным значением.

SELECT INTO customer_fname. customerJname

firstjiame, 1ast_name

FROM customers WHERE id = customer_id:

-- Вернуть полное имя.

RETURN customer_fname | '' '' | customerj name:

END;

' LANGUAGE 'plpgsql1:

В листинге 11.18 показан результат вызова функции get_customer_name() с аргументом 107.

Вызов функции get_customer_name()

Листинг 11.18. Вызов функции get_customer_name()

booktown=# SELECT get_customer_name(107);

get_customer_name

Annie Jackson

(1 row)

Чтобы узнать, успешно ли были присвоены значения переменным командой SELECT INTO, воспользуйтесь специальной логической переменной FOUND. Кроме того, можно проверить значение заданной переменной ключевыми словами ISNULL или IS NULL (в большинстве случаев положительный результат означает, что команда SELECT INTO завершилась неудаче]'!).

Ключевые слова FOUND, IS NULL и ISNULL следует использовать в условных командах (IF/THEN). Условные команды PL/pgSQL описаны в разделе «Передача управления» этой главы. В листинге 11.19 приведен простейший пример использования логической переменной FOUND в функции get_customer_1d().

Использование логической

Листинг 11.19. Использование логической переменной FOUND в функции get_customer_id()

[...]

SELECT INTO customerjd id FROM customers

WHERE last_name = l_name AND firstjiame = f_name;

-- Если совпадение не найдено, вернуть -1.

-- Другая функция, в которой вызывается

get_customer_id().

-- может интерпретировать -1 как признак ошибки.

IF NOT FOUND THEN

return -1;

END IF;

[...]

В листинге 11.20 показано, что теперь функция get_customer_id() при передаче имени несуществующего клиента возвращает -1.

Вызов нового варианта

Листинг 11.20. Вызов нового варианта функции get_customer_id()

booktown=# SELECT get_customer_id('Schmoe','Joe');

get_customer_id

-1

(1 row)

Примеры вызовов функций

Листинг 11.21. Примеры вызовов функций

booktown=# SELECT get_author('John');

get_author

John Worsley

(1 row)

booktown=# SELECT

get_author(1111);

get_author

Ariel Denham

(1 row)

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

Функции get_author(text) и get_author(integer) будут рассмотрены позднее в этой главе.

Аргументы, полученные функцией, поочередно присваиваются идентификаторам, состоящим из знака доллара ($) и порядкового номера. Первому аргументу соответствует идентификатор $1, второму — $2 и т. д. Максимальное количество аргументов равно 16, поэтому идентификаторы аргументов лежат в интервале от $1 до $16. В листинге 11.22 приведен пример функции, которая удваивает свой целочисленный аргумент.

Непосредственное

Листинг 11.22. Непосредственное использование аргументов в переменных

CREATE FUNCTION double_price (float) RETURNS float AS '

DECLARE BEGIN

-- Вернуть значение аргумента, умноженное на 2.

return $1 * 2:

END:

' LANGUAGE 'plpgsql';

Если функция имеет большое количество аргументов, в обозначениях вида «$+номер» легко запутаться. Чтобы программисту было проще отличить одни аргумент от другого (или если он хочет присвоить переменной аргумента более содержательное имя), в PL/pgSQL предусмотрена возможность определения псевдонимов переменных.

Псевдоним создается при помощи ключевого слова ALIAS и представляет собой альтернативный идентификатор для ссылки на аргумент. Перед использованием все псевдонимы (как и обычные переменные) должны быть объявлены в секции объявлений блока. В листинге 11.23 показан синтаксис применения ключевого слова ALIAS.

Синтаксис использования ключевого слова ALIAS

Листинг 11.23. Синтаксис использования ключевого слова ALIAS

CREATE FUNCTION функция (аргументы) RETURNS тип AS '

DECLARE

идентификатор ALIAS FOR $1:

идентификатор ALIAS FOR $2:

BEGIN

END.:"

' LANGUAGE 'plpgsql':

В листинге 11.24 приведен простой пример, демонстрирующий применение псевдонимов в функциях PL/pgSQL. Функция tri pi е_рп се() получает вещественное число, умножает его на три и возвращает результат.

Псевдонимы PL/pgSQL

Листинг 11.24. Псевдонимы PL/pgSQL

CREATE FUNCTION triple_pnce (float) RETURNS float AS '

DECLARE

-- Переменная input_price объявляется как псевдоним

-- для переменной аргумента, обычно обозначаемой

-- идентификатором $1. input_price ALIAS FOR $1:

BEGIN

- Вернуть аргумент, умноженный на три.

RETURN input_price * 3:

END:

' LANGUAGE 'plpgsql':

Если теперь вызвать функцию triple_pnce() при выполнении команды SQL SELECT в клиенте psql, будет получен результат, показанный в листинге 11.25.

Результат вызова функции triple_price()

Листинг 11.25. Результат вызова функции triple_price()

booktown=# SELECT triple_price(12.50);

triple_price

37.5

(1 row)

Синтаксис команды RETURN

Листинг 11.26. Синтаксис команды RETURN

CREATE FUNCTION функция (аргументы) RETURNS тип AS '

DECLARE

объявление:

[...] BEGIN

команда:

[...]

RETURN { переменная \ значение }

END:

' LANGUAGE 'plpgsql :

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

Объявление переменной с атрибутом %TYPE

Листинг 11.27. Объявление переменной с атрибутом %TYPE

переменная таблица.лолеХТУРЕ

В листинге 11.28 приведена функция, использующая атрибут ITYPE для хранения фамилии автора. В ней задействован оператор конкатенации (11), описанный ниже. Команда SELECT INTO рассматривалась ранее в этой главе.

В листинге 11.28 следует обратить особое внимание на атрибут ШРЕ. Фактически мы объявляем переменную, тип которой совпадает с типом поля таблицы authors. Затем команда SELECT находит запись, у которой поле f I rst_name совпадает с аргументом, переданным при вызове функции. Команда SELECT читает значение поля last_name этой записи и сохраняет его в переменной 1_пагпе. Пример вызова функции с передачей аргумента приведен ниже, в листинге 11.29. Кроме того, передача аргумента пользователем встречается во многих примерах этой главы.

Использование атрибута %TYPE

Листинг 11.28. Использование атрибута %TYPE

CREATE FUNCTION get_author (text) RETURNS text AS '

DECLARE

-- Объявление псевдонима для аргумента функции.

-- в котором должно передаваться имя автора,

f_name ALIAS FOR $1:

-- Объявление переменной, тип которой совпадает

-- с типом поля last_name таблицы authors.

I_name authors.lastjiamelTYPE:

BEGIN

-- Найти в таблице authors фамилию автора.

-- имя которого совпадает с переданным аргументом.

-- и присвоить ее переменной Ijiame.

SELECT INTO Ijiame lastjiame FROM authors WHERE firstjiame - f_name:

- Вернуть имя и фамилию, разделенные пробелом,

return f_name 11 '' " 11 Ijiame:

END:

' LANGUAGE 'plpgsql':

В листинге 11.29 приведен пример вызова функции get_author().

Результат вызова функции get_author()

Листинг 11.29. Результат вызова функции get_author()

booktown=# SELECT get_author('Andrew');

get_author

Andrew Brook Ins

(1 row)

Использование атрибута %ROWTYPE

Листинг 11.30. Использование атрибута %ROWTYPE

CREATE FUNCTION get_author (integer) RETURNS text AS '

DECLARE

-- Объявление псевдонима для аргумента функции.

-- в котором должен передаваться код автора,

author Jd ALIAS FOR $1:

-- Объявление переменной, структура которой

-- совпадает со структурой таблицы authors,

found author authors ROWTYPE:

BEGIN

-- Найти в таблице authors фамилию автора.

-- код которого совпадает с переданным аргументом.

SELECT INTO found_author * FROM authors WHERE id = authorjd:

-- Вернуть имя и фамилию, разделенные пробелом.

RETURN found_author.first_name || " " || found_author.last_name:

END:

' LANGUAGE 'plpgsql':

Обратите внимание па звездочку (*) в списке полей команды SELECT. Поскольку переменная found_author объявлялась с атрибутом UROWTYPE для таблицы authors, она имеет такую же структуру, как и записи таблицы authors. Таким образом, конструкция SELECT * заполняет переменную found_author значениями полей найденной записи. Пример вызова новой версии get_author() приведен в листинге 11.31.

Вызов новой версии функции get_author()

Листинг 11.31. Вызов новой версии функции get_author()

booktown=# SELECT get_author(1212): get_author

John Worsley

(1 row)

Возвращение результата конкатенации

Листинг 11.32. Возвращение результата конкатенации

CREATE FUNCTION compound_word(text. text) RETURNS text AS '

DECLARE

-- Объявление псевдонимов для аргументов функций.

wordl ALIAS FOR $1: word2 ALIAS FOR $2:

BEGIN

-- Вернуть объединение двух слов.

RETURN wordl || word2:

END:

' LANGUAGE 'plpgsql :

Если передать функции аргументы «break» и «fast», функция вернет объединенную строку «breakfast»:

booktown=# SELECT compound_word('break'. 'fast');

compound_word

breakfast

(1 row)

В листинге 11.33 строковой переменной result присваивается результат конкатенации нескольких компонентов.

Присваивание строковой

Листинг 11.33. Присваивание строковой переменной результата конкатенации

CREATE FUNCTION title_and_author (text, text) RETURNS text AS '

DECLARE

-- Объявление псевдонимов для двух аргументов функции,

title ALIAS for $1: author ALIAS for $2:

-- Объявление текстовой переменной для хранения

-- строкового результата конкатенации, result text;

BEGIN

-- Объединить переменные title и author.

-- разделив их запятой. result := title | " . by " 11 author;

-- Вернуть полученную строку.

return result:

END;

' language 'plpgsql';

Если передать этой функции строки «Practical PostgreSQL» и «Command Prompt, Inc.», то функция вернет строку «Practical PostgreSQL, by Command Prompt, Inc.»:

booktown=# SELECT title_and_author('Practical PostgreSQL'.'Command Prompt, Inc.');

title and author

Practical PostgreSQL. by Command Prompt. Inc.

(1 row)

Синтаксис команды IF/THEN

Листинг 11.34. Синтаксис команды IF/THEN

CREATE FUNCTION функция (аргументы) RETURNS тип AS '

DECLARE

объявления BEGIN

IF условие THEN

команда:

[...] END IF;

END;

' LANGUAGE 'plpgsql';

В листинге 11.35 приведена функция, которая проверяет количество экземпляров книги на складе по коду и номеру издания. Код книги используется во внут-

рениих операциях базы данных и присутствует в нескольких таблицах. Следовательно, функция stock_amount() предназначена для вызова из других функций, а не прямого вызова из клиентской программы, поскольку большинству пользователей коды книг неизвестны.

Сначала мы читаем код ISBN командой SELECT INTO. Если команда SELECT INTO не смогла найти код ISBN по заданному коду книги и номеру издания, функция stock_amount() возвращает -1. Функция, вызвавшая stock_amount(), интерпретирует это значение как признак ошибки. Если код ISBN найден успешно, то другая команда SELECT INTO получает количество экземпляров книги на складе и возвращает полученную величину. На этом работа функции завершается.

Использование команды IF/THEN

Листинг 11.35. Использование команды IF/THEN

CREATE FUNCTION stock_amount (integer, integer) RETURNS integer AS '

DECLARE

-- Объявление псевдонимов для аргументов функции.

b_id ALIAS FOR $1: b_edition ALIAS FOR $2:

-- Объявление переменной для кода ISBN.

b_1sbn text:

- Объявление переменной для количества экземпляров.

stock_amount integer: .

BEGIN

- Команда SELECT INTO находит в таблице editions запись.

-- у которой код книги и номер издания совпадают с аргументами

-- функции. Код ISBN из найденной записи присваивается переменной.

SELECT INTO bjsbn isbn FROM editions WHERE

book_id = b_id AND edition = b_edition;

- Проверить, не был ли полученный код ISBN равен NULL.

-- Значение NULL говорит о том. что в базе данных

-- не существует записи книги с кодом и номером издания.

-- переданными в аргументах функции. Если запись не существует.

-- функция возвращает -1 и завершает работу.

IF bjsbn IS NULL THEN

RETURN -1:

END IF:

-- Получить из таблицы stock количество экземпляров книги

- на складе и присвоить его переменной stock_amount.

SELECT INTO stock_amount stock FROM stock WHERE isbn = bjsbn:

-- Вернуть количество экземпляров на складе. RETURN stock_amount;

END:

' LANGUAGE 'plpgsql':

В листинге 11.36 показан результат вызова функции stock_amount() для кода книги 7808 и издания 1.

Результаты вызова функции stock_amount()

Листинг 11.36. Результаты вызова функции stock_amount()

booktowrHf SELECT stock_amount(7808,1):

stock amount

(1 row)

Синтаксис команды IF/THEN/ELSE

Листинг 11.37. Синтаксис команды IF/THEN/ELSE

CREATE FUNCTION функция (аргументы) RETURNS тип AS '

DECLARE

объявления

BEGIN

IF условие THEN

команда:

[...]

ELSE

команда:

[...]

END IF:

END:

' LANGUAGE 'plpgsql':

Функция в листинге 11.38 делает практически то же, что и функция в листинге 11.35: она также определяет код ISBN по коду книги и номеру издания, сохраняет его в переменной и получает количество экземпляров книги на складе.

Затем команда IF/THEN/ELSE проверяет, является ли количество книг на складе положительной величиной. Если число положительно, функция возвращает TRUE — признак наличия книг на складе. В противном случае функция возвращает FALSE. Стоит напомнить, что функция in_stock() предназначена для вызова из других функций, а возвращаемое значение должно интерпретироваться той функцией, из которой она была вызвана.

Команда IF/THEN/ELSE

Листинг 11.38. Команда IF/THEN/ELSE

CREATE FUNCTION in_stock (integer.integer) RETURNS boolean AS '

DECLARE

-- Объявление псевдонимов для аргументов функции.

b_id ALIAS FOR $1:

b_edition ALIAS FOR $2:

-- Объявление текстовой переменной для найденного кода ISBN.

b_isbn text:

-- Объявление целочисленной переменной для количества экземпляров. stock_amount integer:

BEGIN

-- Команда SELECT INTO находит в таблице editions запись.

-- у которой код книги и номер издания совпадают с аргументами

-- функции. Код ISBN из найденной записи присваивается переменной.

SELECT INTO b_sbn isbn FROM editions WHERE

bookjd = b_id AND edition - b_edition:

-- Проверить, не был ли полученный код ISBN равен NULL.

-- Значение NULL говорит о тон. что в базе данных

-- не существует записи книги с кодом и номером издания.

-- переданными в аргументах функции. Если запись не существует.

-- функция возвращает FALSE и завершает работу.

IF b_sbn IS NULL THEN

RETURN FALSE: END IF:

-- Получить из таблицы stock количество экземпляров книги

-- на складе и присвоить его переменной stock_amount.

SELECT INTO stock_amount stock FROM stock

WHERE isbn = bjsbn:

-- Проверить, является ли количество книг на складе

-- положительной величиной. Если количество положительно,

-- функция возвращает TRUE, а если отрицательно

-- или равно нулю - FALSE.

IF stock_amount <= 0 THEN

RETURN FALSE: ELSE

RETURN TRUE: END IF:

END:

' LANGUAGE 'plpgsql':

В листинге 11.39 показан результат вызова in_stock() для кода книги 4513 и издания 2.

Результат вызова функции in_stock()

Листинг 11.39. Результат вызова функции in_stock()

booktown=# SELECT in_stock(4513,2);

in stock

(1 row)

Функция вернула значение TRUE — признак наличия книги на складе.

Команда IF/THEN/ELSE/IF

Листинг 11.40. Команда IF/THEN/ELSE/IF

CREATE FUNCTION books_by_subject (text) RETURNS text AS '

DECLARE

-- Объявление псевдонима для аргумента, содержащего либо

-- строку all. либо тему.

sub_tnie ALIAS FOR $1;

-- Объявление целочисленной переменной для хранения кода темы

- и текстовой переменной для хранения списка найденных книг.

- Текстовая переменная инициализируется пустой строкой.

suD_id integer:

found_text text :

BEGIN

-- Получить код темы, описание которой передано в аргументе.

SELECT INTO subjd id FROM subjects WHERE subject = sub_title:

-- Проверить, запросил ли пользователь информацию обо всех темах

- (строка all). В этом случае вызвать функцию extract_all_titles()

- и вернуть полученную текстовую переменную.

IF sub_t1tle = ' 'all'' THEN

found_text extract_all_titles();
RETURN found text:

-- Если в аргументе НЕ БЫЛА передана строка "all", проверить.

-- входит ли код темы в интервал допустимых значений.

-- Если это так, вызвать функцию extract_title() с кодом темы

-- и присвоить результат переменной found_text.

ELSE IF subjd >
= 0 THEN

found_text :- extract_title(sub_id):

RETURN "\n" || sub_title || ":\n" | found_text;

-- Если код темы равен NULL, вернуть сообщение о том. что

-- заданная тема не найдена.

ELSE IF subjd IS NULL THEN

RETURN "Subject not found.";

END IF:

END IF;

RETURN "An error occurred. .";

END;

' LANGUAGE 'plpgsql':

В листинге 11.41 сначала приведен результат вызова функции books_by_subject() с аргументом а! 1 (признак того, что пользователь хочет получить список книг по всем темам). Затем следуют результаты, полученные при вызове функции с аргументом Computers (получение списка книг, посвященных компьютерам).

Результаты вызова функции books_by_subject()

Листинг 11.41. Результаты вызова функции books_by_subject()

booktown=# SELECT books_by_subject('al1');

books_by_subject

Arts:

Dynamic Anatomy

Business:

Children's Books:

The Cat in the Hat

Bartholomew and the Oobleck

Franklin in the Dark

Goodnight Moon

[...]

Science:

Science Fiction:

Dune

2001: A Space Odyssey

(1 row)

booktown=# SELECT books_by_subject('Computers');

books by_subject

Computers:

Learning Python

Perl Cookbook

Practical PostgreSQL

Programming Python

(1 row)

Использование безусловного цикла

Листинг 11.42. Использование безусловного цикла

CREATE FUNCTION square_integer_loop (integer) RETURNS integer AS '

DECLARE

-- Объявление псевдонима для аргумента,

numl ALIAS FOR $1;

- Объявление целочисленной переменной для хранения результата,

result integer;

BEGIN

- Исходное число присваивается переменной

result, result := numl;

LOOP

result := result * result:

EXIT WHEN result >
= 10000;

END LOOP;

RETURN result:

END; '

LANGUAGE 'plpgsql';

В листинге 11.43 показан результат вызова square_i nteger_l oop() с аргументом 3.

Результат вызова функции square_integer_loop()

Листинг 11.43. Результат вызова функции square_integer_loop()

booktown=# SELECT square_integer_loop(3);

squa re_i nteger_l oop

6561

(1 row)

Использование цикла WHILE

Листинг 11.44. Использование цикла WHILE

CREATE FUNCTION add_two_loop (integer, integer) RETURNS integer AS '

DECLARE

-- Объявление псевдонимов для аргументов.

low_number ALIAS FOR $1: highjiumber ALIAS FOR $2:

-- Объявление переменной для хранения результата,

result integer = 0:

BEGIN

-- Увеличивать переменную result на 1 до тех пор.

-- пока она не станет равна high_number.

WHILE result != highjiumber LOOP

result := result + 1:

END LOOP:

RETURN result;

END:

' LANGUAGE 'plpgsql';

Пример использования цикла FOR

Листинг 11.45. Пример использования цикла FOR

CREATE FUNCTION extract_all __titles2 () RETURNS text AS '

DECLARE

-- Объявление переменной для кода темы. sub_id integer;

-- Объявление переменной для хранения списка названий книг.

text_output text = :

-- Объявление переменной для названия темы.

sub_title text;

-- Объявление переменной для хранения записей.

-- полученных при выборке из таблицы books.

row_data booksSSROWTYPE: BEGIN

-- Внешний цикл FOR: тело цикла выполняется до тех пор.

-- пока переменная 1 не станет равна 15. Перебор начинается с 0.

-- Следовательно, тело цикла будет выполнено 16 раз

-- (по одному пля каждой темы).

FOR i IN 0..15 LOOP

-- Получить из таблицы subjects название темы.

-- код которой совпадает со значением переменной 1.

SELECT INTO sub_title subject FROM subjects WHERE id = 1:

-- Присоединить название темы, двоеточие и символ новой строки

-- к переменной text_output.

text_output = text_output || "\n" | sub_title | ":\n";

-- Перебрать все записи таблицы books.

-- у которых код темы совпадает со значением переменной 1.

FOR row_data IN SELECT * FROM books

WHERE subjectjd = i LOOP

-- Присоединить к переменной text_output название книги

-- и символ новой строки.

text_output := text_output || row_data.title || "\n":

END LOOP;

END LOOP:

-- Вернуть список.

RETURN text_output;

END:

' LANGUAGE 'plpgsql':

В листинге 11.46 приведена другая функция, в которой цикл FOR используется для перебора результатов запроса SQL. При каждой итерации цикла FOR в листинге 11.46 содержимое одной из записей запроса к таблице books помещается в переменную row_data, после чего значение поля title присваивается переменной text_output.

Цикл продолжается до тех пор, пока не будет достигнута последняя запись в таблице books. В конце цикла переменная text_output содержит полный список всех книг по теме, код которой был передан в аргументе функции. Работа функции завершается возвращением переменной text_output.

Использование цикла FOR с атрибутом %ROWTYPE

Листинг 11.46. Использование цикла FOR с атрибутом %ROWTYPE

CREATE FUNCTION extract_title (integer) RETURNS text AS '

DECLARE

-- Объявление псевдонима для аргумента функции,

subjd ALIAS FOR $1:

-- Объявление переменной для хранения названий книг.

-- Переменная инициализируется символом новой строки,

text output text : = ''\n'';

-- Обьявление переменной для хранения записей

-- таблицы books, row data booksXROWTYPE:

BEGIN

-- Перебор результатов запроса.

FOR rowjata IN SELECT * FROM books

WHERE subjectjd = subjd ORDER BY title LOOP

-- Присоединить название книги к переменной text_output.

text_output := text_output || row_data.title || "\n";

END LOOP:

-- Вернуть список книг.

RETURN text_output:

END:

' LANGUAGE 'plpgsql':

В листинге 11.47 показан результат вызова функции extract_title() с аргументом 2. В таблице subjects этот код соответствует теме «Children's Books» (книги для детей).

Результат выполнения функции extract_title()

Листинг 11.47. Результат выполнения функции extract_title()

booktown=# SELECT extract_title(2);

extract_title

Bartholomew and the Oobleck

Franklin in the Dark

Goodnight Moon

The Cat in the Hat

(1 row)

Переменная row_data объявляется с атрибутом UROWTYPE no отношению к таблице books, поскольку она будет использоваться только для хранения записей из таблицы books. С таким же успехом можно было объявить row_data с типом RECORD:

rowjata RECORD:

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

Функция extract_ti tl e() возвращает одинаковые результаты как при объявлении переменной с типом RECORD, так и с атрибутом &ROWTYPE.

Команда RAISE

Листинг 11.48. Команда RAISE

CREATE FUNCTION raise_test () RETURNS integer AS '

DECLARE

-- Объявление целочисленной переменной для тестового вывода.

an_integer integer = 1;

BEGIN

- Вывести отладочное сообщение уровня

DEBUG. RAISE DEBUG "The raise_test() function began.":

an_integer = an_integer * 1;

-- Вывести сообщение об изменении переменной an_integer,

-- а затем вывести другое сообщение с ее новым значением.

RAISE NOTICE "Variable anjnteger was cnanged.";

RAISE NOTICE "Variable anjnteger's value is now £." anjnteger;

-- Инициировать исключение.

RAISE EXCEPTION "Variable % cnanged.

Transaction aborted. ",anjnteger:

RETURN 1:

END;

' LANGUAGE 'plpgsql':

В листинге 11.49 приведены результаты, полученные при вызове функции raise_test() из базы данных booktown. Отладочное сообщение DEBUG отсутствует, поскольку база данных работает не в отладочном режиме.

Результаты вызова raise_test()

Листинг 11.49. Результаты вызова raise_test()

booktown=# SELECT raise_test();

NOTICE: Variable anjnteger was changed.

NOTICE: Variable anjnteger's value is now 2.

ERROR: Variable 2 changed. Aborting transaction.

Использование ключевого слова PERFORM

Листинг 11.50. Использование ключевого слова PERFORM

CREATE FUNCTION shipjtem (text.text.text) RETURNS integer AS '

DECLARE

-- Объявление псевдонимов для аргументов функции.

l_name ALIAS FOR $1: f_name ALIAS FOR $2;

bookjsbn ALIAS FOR $3:

-- Объявление переменной для хранения кода книги.

-- Переменная используется для проверки переданного кода ISBN.

book_id Integer:

-- Объявление переменной для хранения кода покупателя.

-- Переменная используется для проверки переданных данных покупателя.

customeMd integer;

BEGIN

-- Получить код покупателя при помощи ранее определенной функции.

SELECT INTO customer_id get_customer_id(l_name.f_name);

-- Если покупатель не найден, функция get_customerjd

-- возвращает -1. В этом случае вернуть -1 и выйти из функции.

IF customerjd = -1 THEN

RETURN -1:

END IF:

-- Получить код книги с заданным кодом ISBN.

SELECT INTO book id book id FROM editions WHERE isbn = book isbn:

-- Если данные книги отсутствуют в базе, вернуть -1.

IF NOT FOUND THEN

RETURN -1;

END IF;

-- Если книга и покупатель существуют.

-- сохранить информацию о поставке в базе.

PERFORM add_shipment(customer_id.bookjsbn);

-- Вернуть 1 - признак успешного выполнения функции.

RETURN 1:

END;

' LANGUAGE 'plpgsql':

Определение триггерной функции

Листинг 11.51. Определение триггерной функции

CREATE FUNCTION функция () RETURNS opaque AS '

DECLARE

объявления;

[...]

BEGIN

команды;

[...]

END:

' LANGUAGE 'plpgsql':

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

Триггерная функция check_shipment_addition()

Листинг 11.52. Триггерная функция check_shipment_addition()

CREATE FUNCTION check_shipment_addition () RETURNS opaque AS '

DECLARE

-- Объявление переменной для хранения кода покупателя,

idjiumber integer;

-- Объявление переменной для хранения кода ISBN.

book_isbn text;

BEGIN

-- Если в таблице customers существует код. совпадающий с кодом

-- покупателя в таблице new. присвоить его переменной idjiumber.

SELECT INTO idjiumber id FROM customers WHERE id = NEW.customer_id:

-- Если совпадение не найдено, инициировать исключение.

IF NOT FOUND THEN

RAISE EXCEPTION "Invalid customer ID number.":

END IF;

-- Если в таблице editions существует код ISBN, совпадающий с кодом

-- ISBN в таблице new. присвоить его переменной bookjsbn.

SELECT INTO bookjsbn isbn FROM editions WHERE isbn = NEW.isbn;

-- Если совпадение не найдено, инициировать исключение.

IF NOT FOUND THEN

RAISE EXCEPTION "Invalid ISBN.";

END IF:

-- Если обе предыдущие проверки завершились успешно.

-- обновить количество экземпляров.

IF TG_OP - "INSERT" THEN

UPDATE stock SET stock = stock -1 WHERE isbn = NEW.isbn;

END IF:

RETURN NEW:

END;

' LANGUAGE 'plpgsql':

После создания функции check_shi pment_addi ti on() в таблице shi pments устанавливается триггер для ее вызова. В листинге 11.53 приведен синтаксис команды, создающей триггер check_shipment в базе данных booktown (для клиента psql).

Триггер check_shipment

Листинг 11.53. Триггер 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_addition() должна определяться в базе данных booktown до определения триггера, по которому она вызывается. Триггерные функции всегда определяются раньше триггеров.

За дополнительной информацией о триггерах обращайтесь к главе 7.

Объявление

Переменные, используемые в программном блоке PL/pgSQL, должны быть объявлены в секции объявлений этого блока, начинающейся с ключевого слова DECLARE в начале блока. Переменные, объявленные в блоке, доступны во всех его вложенных блоках, но обратное неверно: как упоминалось выше в разделе «Структура языка», переменные, объявленные во вложенном блоке, уничтожаются в конце этого блока и недоступны во внешнем блоке. Синтаксис объявления переменной приведен в листинге 11.11.

Обработка ошибок и исключений

Команда RAISE предназначена для инициирования ошибок и исключений в функциях PL/pgSQL. Она передает заданную информацию механизму PostgreSQL elog (стандартное средство ведения протокола ошибок — данные обычно направляются в файл /var/log/messages или $PGDATA/serverlog с одновременным выводом в поток stderr).

В команде RAISE также указывается уровень ошибки и строка, передаваемая PostgreSQL. Кроме того, в команду можно включить переменные и выражения, значения которых будут содержаться в выходных данных. Соответствующие позиции строки помечаются знаками процента (%). Синтаксис команды RAISE: RAISE уровень ''сообщение'' [. идентификатор [...] ]:

В табл. 11.1 приведены три допустимые значения уровня ошибки с краткими описаниями.

Передача управления

Команды передачи управления существуют практически во всех современных языках программирования, и PL/pgSQL не является исключением. С технической точки зрения сам вызов функции можно рассматривать как передачу управления последовательности команд PL/pgSQL. Тем не менее существуют и другие, более совершенные средства, определяющие последовательность выполнения команд PL/pgSQL. Речь идет об условных командах IF/THEN и циклах.

Переменные

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

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

Как будет показано в разделе «Передача управления», в одной из команд — цикле FOR — предусмотрена возможность инициализации управляющей переменной. Переменную цикла FOR не нужно заранее объявлять в секции DECLARE того блока, в котором находится цикл. Таким образом, переменные цикла FOR составляют единственное исключение из правила, согласно которому все переменные должны объявляться в начале соответствующего блока.

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 и триггеры

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

Не путайте определение триггера с определением триггерлой функции. Триггер определяется командой SQL CREATE TRIGGER, а триггерная функция определяется командой SQL CREATE FUNCTION. Триггеры описаны в главе 7.

Триггерная функция определяется без аргументов и возвращает значение специального типа данных opaque. Синтаксис определения трнггерпой функции PL/ pgSQL командой CREATE FUNCTION приведен в листинге 11.51.

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

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

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

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

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

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

Присваивание

Присваивание в PL/pgSQL выполняется оператором присваивания (:=) в форме левдя_переменндя := правая_переменная

Команда присваивает левой переменной значение правой переменной. Также допускается запись вида левая_переменная := выражение

В этом случае левой переменной присваивается результат выражения, расположенного справа от оператора присваивания.

Значения по умолчанию также могут присваиваться переменным в секции объявлений программных блоков PL/pgSQL. Инициализация переменной производится оператором присваивания (:=) в одной строке с объявлением переменной. Эта тема подробно рассматривается ниже, а в листинге 11.14 приведен небольшой пример.

Синтаксис комментариев

Комментарии первого типа — однострочные комментарии — начинаются с двух дефисов (--) и не имеют специального завершителя. Модуль лексического разбоpa интерпретирует все символы, следующие после двух дефисов, как часть комментария. Пример использования однострочных комментариев приведен в листинге 11.6.

Структура языка

Язык PL/pgSQL имеет относительно простую структуру, что объясняется в основном тем, что каждый логически обособленный фрагмент кода существует в виде функции. Хотя на первый взгляд PL/pgSQL мало похож на другие языки программирования (такие, как язык С), сходство все же существует: логические фрагменты создаются и выполняются в виде функций, все переменные обязательно объявляются перед использованием, функции получают аргументы при вызове и возвращают некоторое значение в конце своей работы.

Регистр символов в именах функций PL/pgSQL не учитывается. В ключевых словах и идентификаторах допускается использование произвольных комбинаций символов верхнего и нижнего регистров. Также обратите внимание на частое удвоение апострофов во многих местах этой главы — всюду, где обычно используются одиночные апострофы. Удвоение экранирует апострофы в определениях функций, поскольку определение функции в действительности представляет собой большую строковую константу в команде CREATE FUNCTION.

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

Допустимые значения уровня ошибки

Таблица 11.1. Допустимые значения уровня ошибки

Значение	Описание
DEBUG	Команда уровня DEBUG направляет заданный текст в виде сообщения DEBUG: в журнал PostgreSQL и клиентской программе, если клиент подключен к кластеру базы данных, работающему в отладочном режиме. Команды RAISE уровня DEBUG игнорируются базами данных, работающими в режиме реальной эксплуатации
NOTICE	Команда уровня NOTICE направляет заданный текст в виде сообщения NOTICE: в журнал PostgreSQL и клиентской программе. Сообщение передается в любом режиме работы PostgreSQL
EXCEPTION	Команда уровня EXCEPTION направляет заданный текст в виде сообщения ERROR: в журнал PostgreSQL и клиентской программе. Ошибка уровня EXCEPTION также вызывает откат текущей транзакции

В листинге 11.48 первая команда RAISE выводит отладочное сообщение, а вторая и третья команды выводят сообщение для пользователя. Обратите внимание на знак % в третьей команде — он отмечает позицию, в которой выводится значение an_i nteger. Наконец, четвертая команда RAISE выводит сообщение об ошибке и инициирует исключение, приводящее к отмене транзакции.

Специальные переменные в триггерных функциях

Таблица 11.2. Специальные переменные в триггерных функциях

Имя	Тип данных	Описание
NEW	RECORD	Новая запись базы данных, созданная командой INSERT или UPDATE при срабатывании триггера уровня записи (ROW). Переменная используется для модификации новых записей
OLD	RECORD	Старая запись базы данных, оставшаяся после выполнения команды INSERT или UPDATE при срабатывании триггера уровня записи (ROW)
TGJAME	name	Имя сработавшего триггера
TG_WHEN	text	Строка BEFORE или AFTER в зависимости от момента срабатывания триггера, указанного в определении (до или после операции)
TG_LEVEL	text	Строка ROW или STATEMENT в зависимости от уровня триггера, указанного в определении
TG_OP	text	Строка INSERT, UPDATE или DELETE в зависимости от операции, вызвавшей срабатывание триггера
TG_RELID	old	Идентификатор объекта таблицы, в которой сработал триггер
TG_RELNAME	name	Имя таблицы, в которой сработал триггер
TG_NARGS	Integer	Количество аргументов триггерной функции, указанных в определении триггера
TG_ARGV[]	Массив text	Аргументы, указанные в команде CREATE TRIGGER. Индексация массива начинается с нуля

В листинге 11.52 приведен пример определения трнггерной функции PL/pgSDL, использующей некоторые из перечисленных переменных. Триггерная функция check_shipment_addition() вызывается после выполнения операции INSERT или UPDATE с таблицей shipments.

Функция check_shipment_addition() убеждается в том, что каждая новая запись содержит действительный код покупателя и код ISBN книги. Затем общее количество экземпляров в таблице stock уменьшается на 1, если триггер сработал по команде SQL INSERT (но не по команде UPDATE!)

Типы данных

Переменные PL/pgSQL могут относиться к любому из стандартных типов данных SQL (например, integer или char). Помимо типов данных SQL, в PL/pgSQL также предусмотрен дополнительный тип RECORD, предназначенный для хранения записей без указания полей — эта информация передается при сохранении данных в переменной. Дополнительная информация о типе данных RECORD приводится ниже. Типы данных SQL были описаны в разделе «Типы данных» главы 3. Самые распространенные типы PL/pgSQL: Boolean, text, char, integer, double precision, date, time.

Условные команды

Условная команда указывает на то, что некоторое действие (или последовательность действий) выполняется в зависимости от результатов проверки заданного логического условия. Определение выглядит запутанно, но на самом деле условные команды весьма просты. В неформальной формулировке условная команда означает следующее: «если условие истинно, выполнить такое-то действие».

Включение поддержки 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 находится в каталоге по умолчанию).

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

Приложение createlang запускается в режиме командной строки. Если имя пользователя операционной системы, с которым вы зарегистрированы в настоящий момент, совпадает с именем суперпользователя целевой базы данных, для вызова createlang можно воспользоваться командой, приведенной в листинге 11.3 (при необходимости вам будет предложено ввести пароль). В противном случае имя суперпользователя базы данных передается приложению createlang с флагом -U, как показано в листинге 11.4.

Возвращение переменных

Тип величины, возвращаемой функцией PL/pgSQL, должен соответствовать типу возвращаемого значения, указанному при создании функции командой CREATE FUNCTION. Значение возвращается командой RETURN. Команда RETURN находится в конце функции, но она также часто встречается в командах IF или других командах, осуществляющих передачу управления в программе. Даже если команда RETURN вызывается в одной из этих команд, функция вес равно должна заканчиваться командой RETURN (даже если управление никогда не будет передано этой завершающей команде). Синтаксис команды RETURN приведен в листинге 11.26.

Выражения

Выражения представляют собой условную запись последовательности операций, результат которой принадлежит одному из базовых типов данных PostgreSQL. В листинге 11.8 приведена простая функция PL/pgSQL, возвращающая результат простого выражения, а в листинге 11.9 продемонстрирован результат вызова этой функции в psql.

Вызов функций

При вызове функции PL/pgSQL из кода PL/pgSQL имя функции обычно включается в команду SQL SELECT или в команду присваивания. Примеры:

SELECT функция (аргументы):

переменндя := функция(аргументы):

Подобный способ вызова функций при выборке и присваивании стал стандартным, поскольку любая функция PostgreSQL должна возвращать значение некоторого типа. Ключевое слово PERFORM позволяет вызвать функцию и проигнорировать возвращаемое значение. Синтаксис вызова функции с ключевым словом PERFORM: PERFORM функция (аргументы'):

В листинге 11.50 приведены примеры вызова функции PL/pgSQL с ключевым словом PERFORM и вызова другой функции PL/pgSQL посредством присваивания (в команде SELECT INTO). Функция ship_item() является удобной «оболочкой» для вызова функции add_sh1pment(). Она получает исходные данные, убеждается в существовании покупателя и книги, а затем передает данные add_shipment().