(Татьяна Леонидовна Стасышина, НГТУ, учебное пособие)

---

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

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

4

 

Пример 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;

Пример 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 здесь.