Как написать функцию на PL/pgSQL?

В прошлых уроках мы изучали язык SQL, в основном это было связанно с запросами на выборку, теперь пришло время переходить к программированию в базах данных, поэтому сегодня мы с Вами будем учиться создавать функции на языке PL/pgSQL в СУБД PostgreSQL.

И начинаем как обычно с теории.

Что такое функции в SQL?

Функции – это объекты базы данных, которые используются для автоматизации и упрощения расчетов. Функции пишутся уже с использованием настоящего языка программирования в PostgreSQL — это PL/pgSQL.

PL/pgSQL – язык программирования, который используется в СУБД PostgreSQL для написания функций, триггеров и других управляющих конструкций.

Сразу скажу, что писать функции без базовых знаний SQL практически невозможно, поэтому советую ознакомиться с основами SQL, например, Вам помогут вот эти статьи:

• SQL код – самоучитель по SQL для начинающих программистов
• Основы языка запросов SQL
• Строковые функции SQL
• Как добавить новый столбец в таблицу на SQL?

Теперь давайте поговорим о том, для чего нам нужны эти самые функции. Как и в любом языке программирования есть «встроенные» функции, а также есть возможность писать свои «пользовательские» функции и PL/pgSQL не исключение, если быть точнее, то SQL не исключение, так как PL/pgSQL является расширением языка SQL.

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

Также, например, можно написать функции, которые будут просто упрощать написание запросов на SQL. Другими словами, можно прописать в функции какое-нибудь действие, которое Вы часто используете при написании запросов и просто вызывать ее в самом запросе. Тем самым запрос становится намного короче, проще и быстрей выполняется, а также к запросу добавляется динамика, так как функции могут быть динамические, т.е. возвращать разный результат в зависимости от входящего параметра или изменений в базе.

В функциях в СУБД PostgreSQL можно использовать все операторы SQL такие как: INSERT, DELETE, UPDATE и другие. И Вы теперь представьте, что можно прописать в функции!? а потом просто  вызвать ее одной строкой.

Создание функций на PL/pgSQL

Теперь давайте будем учиться писать эти самые функции. И для начала ниже представлен общий синтаксис написания функции в PL/pgSQL:

   
   CREATE OR REPLACE FUNCTION название функции (типы передаваемых данных через запятую)
        RETURNS тип возвращаемого значения AS
   $BODY$
        DECLARE
        Объявление переменных
        BEGIN
        Тело программы
        RETURN возвращаемый результат;
        END;
   $BODY$
        LANGUAGE язык, на котором написана функция (например, SQL или plpgsql) VOLATILE

Тип возвращаемого значения может быть разный, например, numeric, integer, text или, например void это тип, который не возвращает значение, а функция просто отрабатывает (например, добавляет новые строки).

Сейчас давайте напишем простенькую функцию, пока без использования plpgsql. Допустим, у нас есть две таблицы с одинаковой строуктурой: test и test1:

Test1

и Test2

Например, мы часто используем в запросах объединение этих двух таблиц, для того чтобы подтянуть к таблице test1, столбец number из таблицы test2. Для упрощения всего этого давайте напишем простенькую функцию:

   
   CREATE OR REPLACE FUNCTION "work".test_number(numeric)
           RETURNS numeric AS
   $BODY$
           SELECT "number" 
           FROM "work"."test2" 
           WHERE id = $1;
   $BODY$
        LANGUAGE 'sql' VOLATILE

Где,

• test_number — название функции;
• numeric — тип входящего параметра (он у нас один, но их может быть много);
• SELECT «number» FROM «work».»test2″ WHERE id = $1 – запрос, т.е. тело функции. $1 передаваемый параметр;
• LANGUAGE ‘sql’ VOLATILE – язык, на котором написана функция.

Теперь давайте вызовем нашу функцию в запросе, это проще простого:

  
   SELECT *, "work".test_number(id) AS Number 
   FROM import.test1

где, «work».test_number(id) – это и есть вызов нашей функции. Мы передали ему параметр id, т.е. целую колонку, результат будет таким:

Если нужно кого-то конкретного подтянуть, можно написать вот так, т.е. передать параметр 1 (id=1).

  
  SELECT *, "work".test_number(1) AS Number
  FROM import.test1
  WHERE id = 1

Таким образом, у нас выведется одна строка:

Здесь мы с Вами обошлись без всякого рода объединений, согласитесь так намного проще, запрос короче и выполняется быстрей.

Теперь попробуем написать функцию уже с использованием языка PL/pgSQL. Допустим, мы хотим знать, кто в нашей таблице соответствует тому или иному признаку. Например, кто из наших сотрудников работает. Определять будем по признаку flag (1 – работает; 0 – не работает). При этом нам не удобно вспоминать чему соответствует этот признак, например 1, поэтому давайте напишем функцию, чтобы видеть надпись, напротив того или иного сотрудника. Пишем функцию:

   
   CREATE OR REPLACE FUNCTION "work".test_if(numeric)
           RETURNS text AS
   $BODY$
        DECLARE
           val ALIAS FOR $1;
           val1 integer;
           val2 text;
        BEGIN 
        
        val1 :=1;
        
        IF val1 = val THEN
           val2 := 'Он работает'; 
        ELSE 
           val2 := 'Не работает';
        END IF;
        
        RETURN val2;
        
        END;
   $BODY$
        LANGUAGE 'plpgsql' VOLATILE

Здесь мы уже используем объявление переменных, условие и возвращение значения.

Вызываем функцию.

   
   SELECT *, "work".test_if(flag) AS Status
   FROM import.test1

Получаем результат:

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