Статьи

Табличные функции PostgreSQL

Февраль 4, 2019

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

1
2
3
4
5
6
7
CREATE OR REPLACE FUNCTION
    f_1 (v1 INTEGER, v2 OUT INTEGER)
AS $$
BEGIN
    v2 := v1;
END
$$ LANGUAGE plpgsql;

… и хотите верьте, хотите нет, но это стол! Мы можем написать:

1
select * from f_1(1);

И выше вернется:

1
2
3
4
5
+----+
| v2 |
+----+
|  1 |
+----+

Это интуитивно понятно, если подумать. Мы просто выдвигаем одну запись с одним столбцом. Если бы мы хотели две колонки, мы могли бы написать:

1
2
3
4
5
6
7
8
CREATE OR REPLACE FUNCTION
    f_2 (v1 INTEGER, v2 OUT INTEGER, v3 OUT INTEGER)
AS $$
BEGIN
    v2 := v1;
    v3 := v1 + 1;
END
$$ LANGUAGE plpgsql;

… а потом:

1
select * from f_2(1);

И выше вернется:

1
2
3
4
5
+----+----+
| v2 | v3 |
+----+----+
|  1 |  2 |
+----+----+

Это полезно, но это только отдельные записи. Что, если мы хотим создать целую таблицу? Это просто, просто измените свои функции, чтобы они фактически возвращали типы TABLE , вместо использования параметров OUT :

01
02
03
04
05
06
07
08
09
10
11
12
CREATE OR REPLACE FUNCTION f_3 (v1 INTEGER)
RETURNS TABLE(v2 INTEGER, v3 INTEGER)
AS $$
BEGIN
    RETURN QUERY
    SELECT *
    FROM (
        VALUES(v1, v1 + 1),
              (v1 * 2, (v1 + 1) * 2)
    ) t(a, b);
END
$$ LANGUAGE plpgsql;

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

1
select * from f_3(1);

И выше вернется:

1
2
3
4
5
6
+----+----+
| v2 | v3 |
+----+----+
|  1 |  2 |
|  2 |  4 |
+----+----+

И мы можем LATERAL объединить эту функцию с другими таблицами, если захотим:

1
2
select *
from book, lateral f_3(book.id)

… которые могут привести, например:

1
2
3
4
5
6
7
8
+----+--------------+----+----+
| id | title        | v2 | v3 |
+----+--------------+----+----+
|  1 | 1984         |  1 |  2 |
|  1 | 1984         |  2 |  4 |
|  2 | Animal Farm  |  2 |  4 |
|  2 | Animal Farm  |  4 |  6 |
+----+--------------+----+----+

На самом деле, кажется, что ключевое слово LATERAL является необязательным в этом случае, по крайней мере для PostgreSQL.

Табличные функции очень мощные!

Обнаружение табличных функций

С точки зрения обратного инжиниринга схемы jOOQ все может стать немного сложнее, как видно из этого вопроса переполнения стека . PostgreSQL работает с параметрами OUT очень похожим образом с типами возвращаемых данных TABLE . Это можно увидеть в следующем запросе к INFORMATION_SCHEMA :

1
2
3
4
SELECT r.routine_name, r.data_type, p.parameter_name, p.data_type
FROM   information_schema.routines r
JOIN   information_schema.parameters p
USING (specific_catalog, specific_schema, specific_name);

… и вывод:

01
02
03
04
05
06
07
08
09
10
routine_name | data_type | parameter_name | data_type
-------------+-----------+----------------+----------
f_1          | integer   | v1             | integer
f_1          | integer   | v2             | integer
f_2          | record    | v1             | integer
f_2          | record    | v2             | integer
f_2          | record    | v3             | integer
f_3          | record    | v1             | integer
f_3          | record    | v2             | integer
f_3          | record    | v3             | integer

Как видите, с этой точки зрения результаты действительно неразличимы. К счастью, мы также можем присоединиться к таблице pg_catalog.pg_proc , которая содержит соответствующий флаг, чтобы указать, возвращает ли функция набор или нет:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
SELECT   r.routine_name,
         r.data_type,
         p.parameter_name,
         p.data_type,
         pg_p.proretset
FROM     information_schema.routines r
JOIN     information_schema.parameters p
USING   (specific_catalog, specific_schema, specific_name)
JOIN     pg_namespace pg_n
ON       r.specific_schema = pg_n.nspname
JOIN     pg_proc pg_p
ON       pg_p.pronamespace = pg_n.oid
AND      pg_p.proname = r.routine_name
ORDER BY routine_name, parameter_name;

Теперь мы получаем:

01
02
03
04
05
06
07
08
09
10
routine_name | data_type | parameter_name | data_type | proretset
-------------+-----------+----------------+-----------+----------
f_1          | integer   | v1             | integer   | f
f_1          | integer   | v2             | integer   | f
f_2          | record    | v1             | integer   | f
f_2          | record    | v2             | integer   | f
f_2          | record    | v3             | integer   | f
f_3          | record    | v1             | integer   | t
f_3          | record    | v2             | integer   | t
f_3          | record    | v3             | integer   | t

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

Теперь удалите все те параметры, которые не являются параметрами OUT , и у вас есть тип таблицы:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
SELECT   r.routine_name,
         p.parameter_name,
         p.data_type,
         row_number() OVER (
           PARTITION BY r.specific_name
           ORDER BY p.ordinal_position
         ) AS ordinal_position
FROM     information_schema.routines r
JOIN     information_schema.parameters p
USING   (specific_catalog, specific_schema, specific_name)
JOIN     pg_namespace pg_n
ON       r.specific_schema = pg_n.nspname
JOIN     pg_proc pg_p
ON       pg_p.pronamespace = pg_n.oid
AND      pg_p.proname = r.routine_name
WHERE    pg_p.proretset
AND      p.parameter_mode = 'OUT'
ORDER BY routine_name, parameter_name;

Что даст нам:

1
2
3
4
routine_name | parameter_name | data_type | position |
-------------+----------------+-----------+----------+
f_3          | v2             | integer   |        1 |
f_3          | v3             | integer   |        2 |

Как запустить такие запросы в jOOQ?

После того, как приведенный выше код сгенерирован, вы можете легко вызвать табличную функцию в любом запросе jOOQ . Рассмотрим снова пример BOOK (на SQL):

1
2
select *
from book, lateral f_3(book.id)

… и с JOOQ:

1
2
3
4
DSL.using(configuration)
   .select()
   .from(BOOK, lateral(F_3.call(BOOK.ID)))
   .fetch();

Возвращенные записи затем содержат значения для:

1
2
record.getValue(F_3.V2);
record.getValue(F_3.V3);

Все эти типы безопасности доступны только в следующем выпуске jOOQ 3.5, бесплатно! (Табличные функции SQL Server, Oracle и HSQLDB уже поддерживаются!)

jooq-The-лучший способ к записи-SQL-в-Java-маленький

Ссылка: Табличные функции PostgreSQL от нашего партнера по JCG Лукаса Эдера из блога JAVA, SQL и JOOQ .
132
Share: