Функции без параметров

Начнем с примера простой функции без параметров и возвращаемого значения.

=> CREATE FUNCTION fill() RETURNS void AS $$
INSERT INTO t
    SELECT random() FROM generate_series(1,3);
$$ LANGUAGE SQL;

ERROR:  relation "t" does not exist
LINE 2: INSERT INTO t
                    ^

Ой. Таблица-то еще не создана.

Исправляемся.

=> CREATE TABLE t(a float);

CREATE TABLE

=> CREATE FUNCTION fill() RETURNS void AS $$
INSERT INTO t
    SELECT random() FROM generate_series(1,3);
$$ LANGUAGE SQL;

CREATE FUNCTION

Вызвать такую функцию в SQL можно из запроса:

=> SELECT fill();

 fill 
------
 
(1 row)

Не вернуть совсем ничего запрос в данном случае не может, поэтому возвращается NULL. Собственно результат работы виден в таблице:

=> SELECT * FROM t;

         a         
-------------------
 0.960853947792202
 0.124288525432348
 0.781922194175422
(3 rows)

Добавим возвращаемое значение:

=> CREATE OR REPLACE FUNCTION fill() RETURNS bigint AS $$
INSERT INTO t
    SELECT random() FROM generate_series(1,3);
SELECT count(*) FROM t;
$$ LANGUAGE SQL;

Получится?

Увы, таким образом нельзя переопределить тип возвращаемого значения.

ERROR:  cannot change return type of existing function
HINT:  Use DROP FUNCTION fill() first.

Нужно удалить функцию и создать заново:

=> DROP FUNCTION fill();

DROP FUNCTION

=> CREATE OR REPLACE FUNCTION fill() RETURNS bigint AS $$
INSERT INTO t
    SELECT random() FROM generate_series(1,3);
SELECT count(*) FROM t;
$$ LANGUAGE SQL;

CREATE FUNCTION

Вот как она теперь работает:

=> SELECT fill();

 fill 
------
    6
(1 row)

=> SELECT fill();

 fill 
------
    9
(1 row)

А если мы хотим, чтобы таблица предварительно очищалась? Изменить логику можно, не удаляя функцию:

=> CREATE OR REPLACE FUNCTION fill() RETURNS bigint AS $$
TRUNCATE t;
INSERT INTO t
    SELECT random() FROM generate_series(1,3);
SELECT count(*) FROM t;
$$ LANGUAGE SQL;

CREATE FUNCTION

=> SELECT fill();

 fill 
------
    3
(1 row)

=> SELECT fill();

 fill 
------
    3
(1 row)

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

В нашем случае последний оператор - SELECT, и он возвращает единственную строку.

Не все команды SQL можно использовать в функции.

Запрещены:

• команды управления транзакциями (BEGIN, COMMIT, ROLLBACK и т. п.),
• "нетранзакционные" команды (такие, как VACUUM или CREATE INDEX).

=> CREATE FUNCTION do_commit() RETURNS void AS $$
COMMIT;
$$ LANGUAGE SQL;

CREATE FUNCTION

=> SELECT do_commit();

ERROR:  COMMIT is not allowed in a SQL function
CONTEXT:  SQL function "do_commit" during startup

Функции с входными параметрами

Добавим параметр - число строк. В этом случае снова придется предварительно удалить функцию.

=> DROP FUNCTION fill();

DROP FUNCTION

=> CREATE FUNCTION fill(nrows integer) RETURNS bigint AS $$
TRUNCATE t;
INSERT INTO t
    SELECT random() FROM generate_series(1,nrows);
SELECT count(*) FROM t;
$$ LANGUAGE SQL;

CREATE FUNCTION

Попробуем:

=> SELECT fill(5);

 fill 
------
    5
(1 row)

Мы вызвали функцию, указав фактические параметры позиционным способом. Можно указать параметр и по имени (хотя в данном случае это выглядит избыточно):

=> SELECT fill(nrows => 10);

 fill 
------
   10
(1 row)

Можно определить параметр функции без имени; тогда позиционный способ будет единственно возможным. И внутри тела функции на параметры придется ссылаться по номеру:

=> DROP FUNCTION fill(integer);

DROP FUNCTION

=> CREATE FUNCTION fill(integer) RETURNS bigint AS $$
TRUNCATE t;
INSERT INTO t
    SELECT random() FROM generate_series(1,$1);
SELECT count(*) FROM t;
$$ LANGUAGE SQL;

CREATE FUNCTION

=> SELECT fill(10);

 fill 
------
   10
(1 row)

Но так лучше не делать, это неудобно.

Функции с выходными параметрами

Альтернативный способ вернуть значение - использовать выходной параметр.

=> DROP FUNCTION fill(integer);

DROP FUNCTION

=> CREATE FUNCTION fill(nrows IN integer, result OUT bigint) AS $$
TRUNCATE t;
INSERT INTO t
    SELECT random() FROM generate_series(1,nrows);
SELECT count(*) FROM t;
$$ LANGUAGE SQL;

CREATE FUNCTION

=> SELECT fill(10);

 fill 
------
   10
(1 row)

Результат тот же самый.

Можно использовать и RETURNS, и OUT-параметр вместе - результат снова будет тем же:

=> CREATE OR REPLACE FUNCTION fill(nrows IN integer, result OUT bigint)
RETURNS bigint
AS $$
TRUNCATE t;
INSERT INTO t
    SELECT random() FROM generate_series(1,nrows);
SELECT count(*) FROM t;
$$ LANGUAGE SQL;

CREATE FUNCTION

=> SELECT fill(10);

 fill 
------
   10
(1 row)

Или даже так (использовав параметр INOUT и приведение типов):

=> DROP FUNCTION fill(integer);

DROP FUNCTION

=> CREATE FUNCTION fill(nrows INOUT integer) AS $$
TRUNCATE t;
INSERT INTO t
    SELECT random() FROM generate_series(1,nrows);
SELECT count(*)::integer FROM t;
$$ LANGUAGE SQL;

CREATE FUNCTION

=> SELECT fill(10);

 fill 
------
   10
(1 row)

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

=> DROP FUNCTION fill(integer);

DROP FUNCTION

=> CREATE FUNCTION fill(nrows INOUT integer, average OUT float) AS $$
TRUNCATE t;
INSERT INTO t
    SELECT random() FROM generate_series(1,nrows);
SELECT count(*)::integer, avg(a) FROM t;
$$ LANGUAGE SQL;

CREATE FUNCTION

Обратите внимание, что при удалении функции не надо указывать выходные параметры.

=> SELECT fill(10);

          fill          
------------------------
 (10,0.442659470625222)
(1 row)

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

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

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

=> DROP TABLE t;

DROP TABLE

Ошибка возникнет только во время выполнения:

=> SELECT fill(10);

ERROR:  relation "t" does not exist
LINE 3: INSERT INTO t
                    ^
QUERY:  
TRUNCATE t;
INSERT INTO t
    SELECT random() FROM generate_series(1,nrows);
SELECT count(*)::integer, avg(a) FROM t;

CONTEXT:  SQL function "fill" during startup

Единственный способ заметить такие ошибки заранее - тестирование.

Перегруженные функции

Напишем функцию, возвращающую большее из двух целых чисел. (Похожая функция есть в SQL и называется greatest, но мы сделаем ее сами.)

=> CREATE FUNCTION maximum(a integer, b integer) RETURNS integer AS $$
SELECT CASE WHEN a > b THEN a ELSE b END;
$$ LANGUAGE SQL;

CREATE FUNCTION

Проверим:

=> SELECT maximum(10,20);

 maximum 
---------
      20
(1 row)

Допустим, мы решили сделать аналогичную функцию для трех чисел. Благодаря перегрузке, не надо придумывать для нее какое-то новое название:

=> CREATE FUNCTION maximum(a integer, b integer, c integer) RETURNS integer AS $$
SELECT CASE WHEN a > b THEN maximum(a,c) ELSE maximum(b,c) END;
$$ LANGUAGE SQL;

CREATE FUNCTION

Теперь у нас две функции с одним именем, но разным числом параметров:

=> \df maximum

                               List of functions
 Schema |  Name   | Result data type |       Argument data types       |  Type  
--------+---------+------------------+---------------------------------+--------
 public | maximum | integer          | a integer, b integer            | normal
 public | maximum | integer          | a integer, b integer, c integer | normal
(2 rows)

И обе работают:

=> SELECT maximum(10,20), maximum(10,20,30);

 maximum | maximum 
---------+---------
      20 |      30
(1 row)

Пусть наша функция работает не только для целых чисел, но и для вещественных.

Как этого добиться? Можно было бы определить еще такую функцию:

=> CREATE FUNCTION maximum(a real, b real) RETURNS real AS $$
    SELECT CASE WHEN a > b THEN a ELSE b END;
$$ LANGUAGE SQL;

CREATE FUNCTION

Теперь у нас три функции с одинаковым именем:

=> \df maximum

                               List of functions
 Schema |  Name   | Result data type |       Argument data types       |  Type  
--------+---------+------------------+---------------------------------+--------
 public | maximum | integer          | a integer, b integer            | normal
 public | maximum | integer          | a integer, b integer, c integer | normal
 public | maximum | real             | a real, b real                  | normal
(3 rows)

Две из них отличаются типами входных параметров:

=> SELECT maximum(10,20), maximum(1.1,2.2);

 maximum | maximum 
---------+---------
      20 |     2.2
(1 row)

Но дальше нам придется определить функции для всех остальных типов данных, и повторить все то же самое для трех параметров. При том, что тело функции не меняется!

Полиморфные функции

Здесь нам поможет полиморфный тип anyelement.

Удалим все три наши функции и затем создадим новую:

=> DROP FUNCTION maximum(integer, integer);

DROP FUNCTION

=> DROP FUNCTION maximum(integer, integer, integer);

DROP FUNCTION

=> DROP FUNCTION maximum(real, real);

DROP FUNCTION

=> CREATE FUNCTION maximum(a anyelement, b anyelement) RETURNS anyelement AS $$
    SELECT CASE WHEN a > b THEN a ELSE b END;
$$ LANGUAGE SQL;

CREATE FUNCTION

Такая функция должна принимать любой тип данных (а работать будет с любым типом, для которого определен оператор "больше").

=> SELECT maximum('A','B');

Получится?

ERROR:  could not determine polymorphic type because input has type "unknown"

Увы, нет. В данном случае строковые литералы могут быть типа char, varchar, text - конкретный тип нам неизвестен. Но можно применить явное приведение типов:

=> SELECT maximum('A'::text,'B'::text);

 maximum 
---------
 B
(1 row)

Еще пример с другим типом:

=> SELECT maximum(now(), now() + interval '1 day');

            maximum            
-------------------------------
 2017-09-02 15:51:19.508381+03
(1 row)

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

Важно, чтобы типы обоих параметров совпадали, иначе будет ошибка:

=> SELECT maximum(1,'A');

ERROR:  invalid input syntax for integer: "A"
LINE 1: SELECT maximum(1,'A');
                         ^

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

Значение по умолчанию для параметров

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

=> CREATE FUNCTION maximum(a anyelement, b anyelement, c anyelement default null)
RETURNS anyelement AS $$
    SELECT CASE
               WHEN c IS NULL THEN
                   x
               ELSE
                   CASE WHEN x > c THEN x ELSE c END
           END
    FROM (
        SELECT CASE WHEN a > b THEN a ELSE b END
    ) max2(x);
$$ LANGUAGE SQL;

CREATE FUNCTION

Попробуем:

=> SELECT maximum(10,20,30);

 maximum 
---------
      30
(1 row)

Так работает. А так?

=> SELECT maximum(10,20);

ERROR:  function maximum(integer, integer) is not unique
LINE 1: SELECT maximum(10,20);
               ^
HINT:  Could not choose a best candidate function. You might need to add explicit type casts.

А так произошел конфликт перегруженных функций:

=> \df maximum

                                               List of functions
 Schema |  Name   | Result data type |                      Argument data types                       |  Type  
--------+---------+------------------+----------------------------------------------------------------+--------
 public | maximum | anyelement       | a anyelement, b anyelement                                     | normal
 public | maximum | anyelement       | a anyelement, b anyelement, c anyelement DEFAULT NULL::unknown | normal
(2 rows)

Невозможно понять, имеем ли мы в виду функцию с двумя параметрами, или с тремя (но просто не указали последний).

Мы решим этот конфликт просто - удалим первую функцию за ненадобностью.

=> DROP FUNCTION maximum(anyelement, anyelement);

DROP FUNCTION

=> SELECT maximum(10,20), maximum(10,20,30);

 maximum | maximum 
---------+---------
      20 |      30
(1 row)

Теперь все работает.

Категории изменчивости и изоляция

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

Во-первых, функции с изменчивостью volatile на уровне изоляции read committed приводят к рассогласованию данных внутри одного запроса.

Сделаем функцию, возвращающую число строк в таблице:

=> CREATE TABLE t(n integer);

CREATE TABLE

=> INSERT INTO t VALUES (1), (2), (3);

INSERT 0 3

=> CREATE FUNCTION cnt() RETURNS bigint AS $$
SELECT count(*) FROM t;
$$ VOLATILE LANGUAGE SQL;

CREATE FUNCTION

Теперь вызовем ее несколько раз с задержкой, а в параллельном сеансе вставим в таблицу дополнительную строку.

=> BEGIN ISOLATION LEVEL READ COMMITTED;

BEGIN

=> SELECT (SELECT count(*) FROM t), cnt(), pg_sleep(1) FROM generate_series(1,4);

student$ sleep 1

=> INSERT INTO t VALUES (4);

INSERT 0 1

 count | cnt | pg_sleep 
-------+-----+----------
     3 |   3 | 
     3 |   3 | 
     3 |   4 | 
     3 |   4 | 
(4 rows)

=> END;

COMMIT

При изменчивости stable или immutable, либо использовании более строгих уровней изоляции, такого не происходит.

=> ALTER FUNCTION cnt() STABLE;

ALTER FUNCTION

=> TRUNCATE t;

TRUNCATE TABLE

=> BEGIN ISOLATION LEVEL READ COMMITTED;

BEGIN

=> SELECT (SELECT count(*) FROM t), cnt(), pg_sleep(1) FROM generate_series(1,4);

student$ sleep 1

=> INSERT INTO t VALUES (4);

INSERT 0 1

 count | cnt | pg_sleep 
-------+-----+----------
     0 |   0 | 
     0 |   0 | 
     0 |   0 | 
     0 |   0 | 
(4 rows)

=> END;

COMMIT

Второй момент связан с видимостью изменений, сделанных собственной транзакцией.

Функции с изменчивостью volatile видят все изменения, в том числе сделанные текущим, еще не завершенным оператором SQL.

=> ALTER FUNCTION cnt() VOLATILE;

ALTER FUNCTION

=> TRUNCATE t;

TRUNCATE TABLE

=> INSERT INTO t SELECT cnt() FROM generate_series(1,5);

INSERT 0 5

=> SELECT * FROM t;

 n 
---
 0
 1
 2
 3
 4
(5 rows)

Это верно для любых уровней изоляции.

Функции с изменчивостью stable или immutable видят изменения только уже завершенных операторов.

=> ALTER FUNCTION cnt() STABLE;

ALTER FUNCTION

=> TRUNCATE t;

TRUNCATE TABLE

=> INSERT INTO t SELECT cnt() FROM generate_series(1,5);

INSERT 0 5

=> SELECT * FROM t;

 n 
---
 0
 0
 0
 0
 0
(5 rows)

Категории изменчивости и оптимизация

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

Для экспериментов создадим функцию, возвращающую случайное число:

=> CREATE FUNCTION rnd() RETURNS float AS $$
SELECT random();
$$ VOLATILE LANGUAGE SQL;

CREATE FUNCTION

Проверим план выполнения следующего запроса:

=> EXPLAIN(COSTS OFF) SELECT * FROM generate_series(1,10) WHERE rnd() > 0.5;

                   QUERY PLAN                   
------------------------------------------------
 Function Scan on generate_series
   Filter: (random() > '0.5'::double precision)
(2 rows)

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

В этом можно убедиться и воочию (ожидаем в среднем получить 5 строк):

=> SELECT * FROM generate_series(1,10) WHERE rnd() > 0.5;

 generate_series 
-----------------
               1
               2
               3
               5
               9
(5 rows)

Функция с изменчивостью stable будет вызвана всего один раз - поскольку мы фактически указали, что ее значение не может измениться в пределах оператора:

=> ALTER FUNCTION rnd() STABLE;

ALTER FUNCTION

=> EXPLAIN(COSTS OFF) SELECT * FROM generate_series(1,10) WHERE rnd() > 0.5;

                      QUERY PLAN                      
------------------------------------------------------
 Result
   One-Time Filter: (rnd() > '0.5'::double precision)
   ->  Function Scan on generate_series
(3 rows)

Наконец, изменчивость immutable позволяет вычислить функции еще на этапе планирования, поэтому во время выполнения никакие фильтры не нужны:

=> ALTER FUNCTION rnd() IMMUTABLE;

ALTER FUNCTION

=> EXPLAIN(COSTS OFF) SELECT * FROM generate_series(1,10) WHERE rnd() > 0.5;

            QUERY PLAN            
----------------------------------
 Function Scan on generate_series
(1 row)

Ответственность "за дачу заведомо ложных показаний" лежит на разработчике.

Подстановка тела функции в SQL-запрос

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

Упрощенно требуется выполнение следующих условий:

• Тело функции состоит из одного оператора SELECT,
• Нет обращений к таблицам, подзапросов, группировок и т. п.
• Возвращаемое значение должно быть одно,
• Вызываемые функции не должны противоречить указанной категории изменчивости.

Пример мы уже видели: наша функция rnd(), объявленная volatile.

Посмотрим еще раз.

=> ALTER FUNCTION rnd() VOLATILE;

ALTER FUNCTION

=> EXPLAIN(COSTS OFF) SELECT * FROM generate_series(1,10) WHERE rnd() > 0.5;

                   QUERY PLAN                   
------------------------------------------------
 Function Scan on generate_series
   Filter: (random() > '0.5'::double precision)
(2 rows)

В фильтре упоминается функция random(), но не rnd(). Она будет вызываться напрямую, минуя "обертку" в виде функции rnd().

Конец демонстрации.