Написание табличной функции на PL/pgSQL — функция, которая возвращает таблицу

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

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

Начнем с самого простого примера, в котором мы просто напишем функцию и передадим ей параметр, в результате выполнения она вернет нам таблицу, учитывая наш параметр.

Допустим, у нас есть вот такая таблица:

Создание табличной функции на SQL в PostgreSQL

Давайте представим, что нам нужно выбрать всех с тем значением, которое мы укажем в передаваемом параметре, допустим по полю znach. Функция будет выглядеть следующим образом:

   
   CREATE OR REPLACE FUNCTION work.fun_test (IN integer)
      RETURNS TABLE (id numeric, number numeric, name text, znach numeric) AS
   $BODY$ 
      select * from work.test where znach = $1 
   $BODY$
      LANGUAGE 'sql' VOLATILE
      COST 100
      ROWS 1000;
   ALTER FUNCTION work.fun_test (integer) OWNER TO postgres;

Ключевым моментом здесь является то, что мы указываем тип возвращаемого значения TABLE и перечисляем все поля, которые мы хотим получить с указанием их типа.

Для того чтобы увидеть результат выполнения функции, выполните вот такой запрос:

   
   select * from work.fun_test (10)

где, 10 и есть то значение параметра, по которому мы хотим сделать выборку.

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

В этом примере мы использовали язык SQL без использования различных приемов программирования, например, условий (if then else), циклов (for) и других.

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

Теперь давайте попробуем написать тот же самый пример, но уже на PL/pgSQL.

Функция будет выглядеть следующим образом:

   
   CREATE OR REPLACE FUNCTION work.fun_test_2 ()
     RETURNS TABLE(id numeric, number numeric, name text, znach numeric) AS
   $BODY$ 
    DECLARE
    rec RECORD;
   BEGIN
   FOR rec IN EXECUTE 'select * from work.test where znach = 10'
        LOOP
                id = rec.id;
                number =rec.number;
                name = rec.name;
                znach = rec.znach;
                RETURN next;
        END LOOP;
   END;
   $BODY$
     LANGUAGE 'plpgsql' VOLATILE
     COST 100
     ROWS 1000;
   ALTER FUNCTION work.fun_test_2 () OWNER TO postgres;

Здесь таблица возвращается с помощью цикла.

Но таким способом мы не можем передать параметр в запрос, приходиться писать статическое значение, но здесь мы можем уже использовать все возможности PL/pgSQL.

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

   
   select * from work.fun_test_2 ()

Создание табличной функции на PL/pgSQL с использованием курсора

А если Вы все-таки хотите предавать параметры в запрос, это можно сделать с помощью курсоров.

Кстати, можно легко написать две функции, первая просто возвращала бы таблицу, с передачей параметров, а вторая, например, как в предыдущем примере, обрабатывала таблицу, которую вернет первая функция, и соответственно выполняла какие-нибудь другие действия, но вместо запроса, как в предыдущем примере, мы будем использовать cursor, кстати, познакомимся еще и с курсорами (если Вы не хотите писать две функции, вместо вызова первой функции в курсоре, напишите запрос). Функция будет выглядеть следующим образом:

   
   CREATE OR REPLACE FUNCTION work.fun_test_3 (IN numeric)
     RETURNS TABLE(id numeric, number numeric, name text, znach numeric) AS
   $BODY$ 
    DECLARE
     rec RECORD;
     var ALIAS FOR $1; 
     cur_test CURSOR FOR select * from work.fun_test (var)
   BEGIN
        FOR rec IN cur_test
                LOOP
                id = rec.id;
                number =rec.number;
                name = rec.name;
                znach = rec.znach;
                RETURN next;
        END LOOP;
        END;
   $BODY$
     LANGUAGE 'plpgsql' VOLATILE
     COST 100
     ROWS 1000;
   ALTER FUNCTION work.fun_test_3 (numeric) OWNER TO postgres;

Вызывается она также как и все предыдущие:

   
   select * from work.fun_test_3 (10)

Здесь мы с Вами объявили курсор и записали в него результат выполнения нашей функции select * from work.fun_test (var), а потом также циклом перебрали все значения. Помимо всего этого в теле цикла можно выполнять различного рода операции, доступные в PL/pgSQL, например, отработка условий if then else.

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