Переменные
Переменные используются в программах PL/pgSQL для хранения изменяемых данных заранее определенного типа. Все переменные программного блока должны быть предварительно объявлены с ключевым словом DECLARE. Если переменная не инициализируется при объявлении, по умолчанию ей присваивается псевдозначение SQL NULL.
ПРИМЕЧАНИЕ
Как будет показано в разделе «Передача управления», в одной из команд — цикле FOR — предусмотрена возможность инициализации управляющей переменной. Переменную цикла FOR не нужно заранее объявлять в секции DECLARE того блока, в котором находится цикл. Таким образом, переменные цикла FOR составляют единственное исключение из правила, согласно которому все переменные должны объявляться в начале соответствующего блока.
Типы данных
Переменные PL/pgSQL могут относиться к любому из стандартных типов данных
SQL (например, integer или char). Помимо типов данных SQL, в PL/pgSQL
также предусмотрен дополнительный тип RECORD, предназначенный для хранения
записей без указания полей — эта информация передается при сохранении данных в переменной. Дополнительная информация о типе данных RECORD приводится ниже. Типы данных SQL были описаны в разделе «Типы данных» главы 3. Самые распространенные типы PL/pgSQL: Boolean, text, char, integer, double precision, date, time.
Объявление
Переменные, используемые в программном блоке PL/pgSQL, должны быть объявлены в секции объявлений этого блока, начинающейся с ключевого слова DECLARE в начале блока. Переменные, объявленные в блоке, доступны во всех его вложенных блоках, но обратное неверно: как упоминалось выше в разделе «Структура языка», переменные, объявленные во вложенном блоке, уничтожаются в конце этого блока и недоступны во внешнем блоке. Синтаксис объявления переменной приведен в листинге 11.11.
Листинг 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 не упоминается в этой главе.
Присваивание
Присваивание в PL/pgSQL выполняется оператором присваивания (:=) в форме левдя_переменндя := правая_переменная
Команда присваивает левой переменной значение правой переменной. Также допускается запись вида левая_переменная := выражение
В этом случае левой переменной присваивается результат выражения, расположенного справа от оператора присваивания.
Значения по умолчанию также могут присваиваться переменным в секции объявлений программных блоков PL/pgSQL. Инициализация переменной производится оператором присваивания (:=) в одной строке с объявлением переменной. Эта тема подробно рассматривается ниже, а в листинге 11.14 приведен небольшой пример.
Листинг 11.14. Инициализация переменной
CREATE FUNCTION идентификатор (аргументы) RETURNS тип AS '
DECLARE
anjnteger int4 := 10:
BEGIN
команда: С...]
END;
' LANGUAGE 'plpgsql':
Возможен н другой вариант — присваивание переменной результата запроса командой SELECT INTO. He путайте этот вариант использования команды SELECT INTO с командой SQL SELECT INTO, которая заносит результаты запроса в новую таблицу.
ПРИМЕЧАНИЕ
Для сохранения результатов запроса в новой таблице в 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 приведены в разделе «Передача управления».
Листинг 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.
Листинг 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.
Листинг 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)
Аргументы
При вызове функции PL/pgSQL могут получать аргументы различных типов. В аргументах пользователь передает исходные данные, необходимые для работы функции. Аргументы делают функции PL/pgSQL более универсальными и значительно расширяют область их возможного применения. Список аргументов приводится после имени функции в круглых скобках и разделяется запятыми.
Количество и типы аргументов должны соответствовать первоначальному определению функции. В листинге 11.21 приведены примеры двух вызовов функции из клиента psql.
Листинг 11.21. Примеры вызовов функций
booktown=# SELECT get_author('John');
get_author
John Worsley
(1 row)
booktown=# SELECT
get_author(1111);
get_author
Ariel Denham
(1 row)
ПРИМЕЧАНИЕ
Функции 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.
Листинг 11.23. Синтаксис использования ключевого слова ALIAS
CREATE FUNCTION функция (аргументы) RETURNS тип AS '
DECLARE
идентификатор ALIAS FOR $1:
идентификатор ALIAS FOR $2:
BEGIN
END.:"
' LANGUAGE 'plpgsql':
В листинге 11.24 приведен простой пример, демонстрирующий применение псевдонимов в функциях PL/pgSQL. Функция tri pi е_рп се() получает вещественное число, умножает его на три и возвращает результат.
Листинг 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.
Листинг 11.25. Результат вызова функции triple_price()
booktown=# SELECT triple_price(12.50);
triple_price
37.5
(1 row)
