6. Создание пользовательских функций в PostgreSql

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

CREATE [ OR REPLACE ] FUNCTION
имя_функции ([ [ метод_аргумента ][имя_аргумента ] тип_ аргумента [,…] ])
RETURNS тип_возвращаемого_значения
AS 'определение'
LANGUAGE 'язык'
[ WITH ( атрибут […])]

CREATE FUNCTION имя_функции ([[ метод_аргумента ] [имя_аргумента ] тип_ аргумента [,…] ]) - после ключевых слов CREATE FUNCTION указывается имя создаваемой функции, после чего в круглых скобках перечисляются аргументы, разделенные запятыми. Для каждого аргумента достаточно указать только тип, но при желании можно задать метод (in, out, inout; по умолчанию in) и имя. Если список в круглых скобках пуст, функция вызывается без аргументов (хотя сами круглые скобки обязательно должны присутствовать как в определении функции, так и при ее использовании). Ключевые слова OR REPLACE используются для изменения уже существующей функции.
RETURNS тип_возвращаемого_значения - тип данных, возвращаемый функцией.
AS 'определение' - программное определение функции. В процедурных языках (таких, как PL/pgSQL) оно состоит из кода функции. Для откомпилированных функций С указывается абсолютный системный путь к файлу, содержащему объектный код.
LANGUAGE 'язык'. Название языка, на котором написана функция. В аргументе может передаваться имя любого процедурного языка (такого, как plpgsql или plperl, если соответствующая поддержка была установлена при компиляции), С или SQL.
[
WITH ( атрибут [. ...] ) ] - атрибут может принимать два значения: iscachable и isstrict.
iscachable. Оптимизатор может использовать предыдущие вызовы функций для ускоренной обработки будущих вызовов с тем же набором аргументов. Кэширование обычно применяется при работе с функциями, сопряженными с большими затратами ресурсов, но возвращающими один и тот же результат при одинаковых значениях аргументов.
isstrict. Функция всегда возвращает NULL в случае, если хотя бы один из ее аргументов равен NULL. При передаче атрибута isstrict результат возвращается сразу, без фактического выполнения функции.

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

Создание функций SQL

CREATE [ OR REPLACE ] FUNCTION
имя_функции ([ [ метод_аргумента ][имя_аргумента ] тип_ аргумента [,…] ])
RETURNS тип_возвращаемого_значения
AS '
оператор SQL;
[оператор SQL;
…]
'
LANGUAGE sql
[ WITH ( атрибут […])];

В теле функции обращение к параметрам осуществляется по имени или по номеру : $1 – первый параметр, $2- второй параметр и т.д.
В теле функции sql могут стоять только операторы языка SQL, любые (Select, insert, delete,create,...) за исключением операторов управления транзакциями (commit, rollback...). Возвращаемым значением является результат выполнения оператора SELECT, его тип должен совпадать с типом, указанным после RETURNS. Если в теле функции несколько операторов SELECT, функция вернет результат выполнения последнего такого оператора. Если функция sql не содержит операторов SELECT, тип результата для нее следует указать void (фактически это процедура).

Примеры создания и использования функций sql

Пример 1. Создание функции, возвращающей столбец текстовых значений

CREATE FUNCTION onef (integer) RETURNS SETOF character AS '
-- Функция возвращает имена поставщиков с рейтингом больше $1

select names from s where rg>$1;

' LANGUAGE sql;

Использование функции, возвращающей столбец текстовых значений

SELECT onef(10) AS sname ;

Результат

sname

Блейк

Кларк

Адамс

Пример 2. Создание функции, возвращающей столбец записей

CREATE FUNCTION manyf (integer) RETURNS SETOF record AS '

- - Функция возвращает сведения о поставщиках с рейтингом больше $1

select ns,names,rg,town from s where rg>$1 order by ns;

' LANGUAGE sql;

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

Использование функции, возвращающей столбец записей

SELECT manyf(10) AS result ;

Результат

result

(“S3”, “Блейк ”, 30,”Париж ”)

(“S4”, “Кларк ”, 20,“Лондон ”)

(“S5”, “Адамс ”, 30,“Афины ”)

Создание функций plpgSQL

CREATE [ OR REPLACE ] FUNCTION
имя_функции ([ [ метод_аргумента ][имя_аргумента ] тип_ аргумента [,…] ])
RETURNS тип_возвращаемого_значения
AS '
[ DECLARE
    объявления ]
BEGIN
    оператор;
   [оператор;…]
[EXCEPTION
    WHEN условие [ OR условие ... ] THEN
        операторы обработки исключения;
    [ WHEN условие [ OR условие ... ] THEN
          операторы обработки исключения;
      ... ]
END;
'
LANGUAGE plpgsql
[ WITH ( атрибут […])];

В теле функции обращение к параметрам осуществляется по имени или по номеру : $1 – первый параметр, $2- второй параметр и т.д.
В теле функции plpgsql кроме операторов SQL могут применяться конструкции языка PL/pgSQL, представляющего собой процедурное расширение SQL. Это могум быть операторы присваивания, условные операторы, циклы и т.п. Результат возвращается командой RETURN. В теле функции plpgsql обязательно должна быть хотя бы одна команда RETURN, кроме случая, когда тип результата void. Подробнее см. http://www.postgresql.org/docs/8.2/interactive/extend.html
Локальные переменные, используемые в теле функции, объявляются в блоке DECLARE
DECLARE
имя_переменной тип_переменной ;
[имя_переменной тип_переменной;…]
В блоке EXCEPTION обрабатываются ошибки.  Условие – наименование ошибки (перечень см. http://www.sbin.org/doc/pg/doc/errcodes-appendix.html   в колонке Constant). Если все ошибки обрабатываются по одной схеме (или вам просто лень подумать, какие здесь могут быть ошибки и найти, как они называются  ), этот блок может выглядеть так
EXCEPTION
    WHEN others THEN
     RAISE exception 'message of error';

Функция  RAISE  уровня   exception  генерирует исключение и выдает сообщение об ошибке (подробнее о функции    RAISE см. http://www.sbin.org/doc/pg/doc/plpgsql-errors-and-messages.html   ).

Примеры создания и использования функций plpgsql

Пример 3. Создание функции, возвращающей целое значение

CREATE FUNCTION apf (character) RETURNS integer AS '

DECLARE
i integer;
-- Функция вычисляет количество поставок детали $1

BEGIN
select count(*) from spj into i where spj.np=$1;
-- возвращение результата
return i;
END;

' LANGUAGE plpgsql;

Пример 4. Создание функции, возвращающей вещественное значение

CREATE FUNCTION avgves (character) RETURNS real AS '
DECLARE

aves real;

-- Функция вычисляет средний вес поставок детали $1

BEGIN
select avg(spj.kol * p.ves ) from spj,p into aves where spj.np=$1 and spj.np=p.np ;

-- возвращение результата
return aves;

END;
' LANGUAGE plpgsql;

Пример 5. Использование функций в классическом SELECT

SELECT p.np, p.namep,
apf(p.np) AS kol , avgves(p.np) as sves,
apf(p.np)* avgves(p.np) as oves FROM p;

Результат

Np	namep	kol	sves	oves
P1	Гайка	1	1200	1200
P2	Болт	2	2550	5100
P3	Винт	9	6611.11	59500.001953125
P4	Винт	2	9100	18200
P5	Кулачок	4	3300	13200
P6	Блюм	4	6175	24700

Пример 6. Использование функций, нестандартный вариант (только PostgreSql)

SELECT apf('P5') AS kol ;

Результат

kol

Пример 7. Создание функции, возвращающей курсор

CREATE FUNCTION fun1 (integer) RETURNS refcursor AS '

-- если входной параметр - 0 - функция возвращает список поставщиков

-- если входной параметр - 1 - функция возвращает список изделий

DECLARE
ref refcursor;
BEGIN

   if   $1=0 then
        OPEN ref FOR SELECT * FROM s ;
   else
        OPEN ref FOR SELECT * FROM j ;
end if;

RETURN ref;

END;

' LANGUAGE plpgsql;

Просмотр результатов курсорной функции

select * from fun1(0) ; FETCH ALL From "<unnamed portal 1>"

Пример 8. Создание функции, генерирующей исключение

CREATE FUNCTION fun_j (character, character, character) RETURNS void AS '

-- первый входной параметр ($1) - номер изделия

-- если $1 задан - обновляется строка с таким номером

-- если $1 не задан(null) - вставляется новая строка, номер берется из последовательности

-- второй входной параметр ($2)- наименование изделия - д.б. непустой

-- третий входной параметр ($3)- название города - д.б. непустой

DECLARE vns character(6);

BEGIN

if $2 is null then

raise exception 'Некорректное название изделия';

else

begin

if $3 is null then

raise exception 'Некорректное название города';

else

begin

if $1 is null then

vns ='J' || trim( to_char( nextval( 'j_seq' ), '99999' ) );

INSERT INTO j (nj,namej,town) VALUES (vns, $2, $3);

else

UPDATE j SET namej=$2,town=$3 where nj=$1;

end if;

Return;

end;

end if;

end;

end if;

exception when integrity_constraint_violation then

raise exception 'Нарушение ограничений целостности';

END;

' LANGUAGE plpgsql

Перегружаемые функций.

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

Пример 9. Создание перегружаемой функции.

CREATE FUNCTION nvl (integer,integer) RETURNS integer AS '
BEGIN
if $1 is null then return $2;
else return $1;
end if;
END;
' LANGUAGE plpgsql;

CREATE FUNCTION nvl (character, character) RETURNS character AS '
BEGIN
if $1 is null then return $2;
else return $1;
end if;
END;
' LANGUAGE plpgsql;

CREATE FUNCTION nvl (text,text) RETURNS text AS '
BEGIN
if $1 is null then return $2;
else return $1;
end if;
END;
' LANGUAGE plpgsql;

CREATE FUNCTION nvl (real,real) RETURNS rext AS '
BEGIN
if $1 is null then return $2;
else return $1;
end if;
END;
' LANGUAGE plpgsql;

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