PostgreSQL的表值函数

表值函数是一件了不起的事情。 许多数据库以一种或另一种方式支持它们，PostgreSQL也是如此。 在PostgreSQL中，（几乎）所有内容都是一个表。 例如，我们可以这样写：

CREATE OR REPLACE FUNCTION 
    f_1 (v1 INTEGER, v2 OUT INTEGER)
AS $$
BEGIN
    v2 := v1;
END
$$ LANGUAGE plpgsql;

…信不信由你，这是一张桌子！ 我们可以这样写：

select * from f_1(1);

以上将返回：

+----+
| v2 |
+----+
|  1 |
+----+

如果您考虑一下，这是一种直观的感觉。 我们只是用单个列推出单个记录。 如果我们需要两列，我们可以编写：

CREATE OR REPLACE FUNCTION 
    f_2 (v1 INTEGER, v2 OUT INTEGER, v3 OUT INTEGER)
AS $$
BEGIN
    v2 := v1;
    v3 := v1 + 1;
END
$$ LANGUAGE plpgsql;

… 然后：

select * from f_2(1);

以上将返回：

+----+----+
| v2 | v3 |
+----+----+
|  1 |  2 |
+----+----+

这很有用，但这些只是单条记录。 如果我们想制作一张整张桌子怎么办？ 很简单，只需将函数更改为实际返回TABLE类型，而不使用OUT参数即可：

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;

从上面非常有用的功能中进行选择时，我们将获得一个如下表：

select * from f_3(1);

以上将返回：

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

如果需要，我们可以将该函数与其他表LATERAL连接：

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

…可能会产生，例如：

+----+--------------+----+----+
| id | title        | v2 | v3 |
+----+--------------+----+----+
|  1 | 1984         |  1 |  2 |
|  1 | 1984         |  2 |  4 |
|  2 | Animal Farm  |  2 |  4 |
|  2 | Animal Farm  |  4 |  6 |
+----+--------------+----+----+

实际上，在这种情况下，至少对于PostgreSQL，关键字LATERAL似乎是可选的。

表值函数非常强大！

发现表值函数

从jOOQ的架构逆向工程角度看，事情可能会变得有些棘手，如在Stack Overflow问题中所见。 PostgreSQL处理OUT参数的方式与TABLE返回类型非常类似。 在以下针对INFORMATION_SCHEMA查询中可以看出：

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);

…和输出：

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表，该表包含相关标志，以指示函数是否返回集合：

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;

现在，我们得到：

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参数的参数，然后使用表类型：

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;

这将给我们：

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

如何在jOOQ中运行此类查询？

生成上述代码后，您可以轻松地在任何jOOQ查询中调用表值函数。 再考虑一下BOOK示例（在SQL中）：

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

…以及使用jOOQ：

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

然后，返回的记录包含以下值：

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

所有这些类型安全性仅在即将发布的jOOQ 3.5中免费提供！ （已经支持SQL Server，Oracle和HSQLDB表值函数！）

翻译自: https://www.javacodegeeks.com/2014/07/postgresqls-table-valued-functions.html