Самоучитель по SQL-сервер в Linux



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

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

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

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

DECLARE

объявление:

[...] BEGIN

команда:

[...]

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

END:

' LANGUAGE 'plpgsql :

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

Атрибуты

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

Атрибут %TYPE

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

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

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

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

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

Листинг 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().

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

booktown=# SELECT get_author('Andrew');

get_author

Andrew Brook Ins

(1 row)

Атрибут %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.

Листинг 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.

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

booktown=# SELECT get_author(1212): get_author

John Worsley

(1 row)

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

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

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

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

Листинг 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)