POSTGRESQL V14中更好的SQL函数

我们都知道并重视SQL函数作为一种便捷的快捷方式。PostgreSQL v14引入了一种新的、更好的编写SQL函数的方法。本文将展示新语法的优点。

SQL函数示例

让我们创建一个具有“经典”语法的SQL函数的简单示例，以便我们有一些用于演示的材料：

CREATE EXTENSION unaccent;
 
CREATE FUNCTION mangle(t text) RETURNS text
   LANGUAGE sql
   AS 'SELECT lower(unaccent(t))';

您可以像使用其他数据库函数一样使用新函数：

SELECT mangle('Schön dumm');
 
   mangle   
════════════
 schon dumm
(1 row)

为什么使用SQL函数？

你可能会问SQL函数有多好。毕竟，数据库函数的主要目的是能够在数据库内运行过程代码，而这是SQL无法做到的。但SQL函数有其用途：

• 不同SQL语句中常用表达式的代码重用

• 通过将部分代码分解成具有有意义名称的函数，使SQL语句更具可读性

• 无论何时出于语法原因需要函数，例如在CREATE AGGREGATE或CREATE OPERATOR中

此外，简单的SQL函数可以内联，也就是说，优化器可以在查询规划时用函数定义替换函数调用。这可以使SQL函数效率极高：

• 它消除了实际函数调用的开销

• 由于函数（大部分）是优化器的黑匣子，用其定义替换函数通常可以提供更好的估计

如果在示例函数中使用EXPLAIN（VERBOSE），我们可以看到函数内联：

EXPLAIN (VERBOSE, COSTS OFF) SELECT mangle('Schön dumm');
 
                  QUERY PLAN                   
═══════════════════════════════════════════════
 Result
   Output: lower(unaccent('Schön dumm'::text))
(2 rows)

PostgreSQL函数的缺点

PostgreSQL函数很棒。一个很好的方面是，您不局限于一种编程语言。PostgreSQL支持用SQL、C、PL/pgSQL（Oracle PL/SQL的克隆）、Perl、Python和Tcl编写的函数。但这并不是全部：在PostgreSQL中，您可以编写一个插件，允许您在数据库中使用您选择的任何语言。为了实现这种灵活性，PostgreSQL函数的函数体只是一个字符串常量，当PostgreSQL执行函数时，过程语言的调用处理程序会对其进行解释。这有一些不良副作用：

缺乏依赖项跟踪

通常，PostgreSQL跟踪pg_depend和pg_shdepend目录表中数据库对象之间的依赖关系。这样，数据库就知道了对象之间的关系：它要么阻止您删除其他对象依赖的对象（如带有外键引用的表），要么自动删除依赖对象（如删除表会删除表上的所有索引）。

由于函数体只是PostgreSQL无法解释的字符串常量，因此它不会跟踪函数和函数中使用的对象之间的依赖关系。过程语言可以提供一个验证器，用于检查函数体的语法正确性（如果check\u function\u bodies=on）。验证器还可以测试函数中引用的对象是否存在，但它不能阻止您稍后删除函数使用的对象。

让我们用我们的例子来演示：

DROP EXTENSION unaccent;
 
SELECT mangle('boom');
ERROR:  function unaccent(text) does not exist
LINE 1: SELECT lower(unaccent(t))
                     ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:  SELECT lower(unaccent(t))
CONTEXT:  SQL function "mangle" during inlining

我们将通过再次创建扩展来解决问题。然而，当我们在不使用级联选项的情况下运行DROP扩展时，最好得到一条错误消息。

搜索路径作为安全问题

由于PostgreSQL在查询执行时解析函数体，因此它使用当前的search\u path设置来解析对不符合模式名称的数据库对象的所有引用。这不仅限于表和视图，还扩展到函数和运算符。我们可以使用示例函数来演示该问题：

SET search_path = pg_catalog;
 
SELECT public.mangle('boom');
ERROR:  function unaccent(text) does not exist
LINE 1: SELECT lower(unaccent(t))
                     ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:  SELECT lower(unaccent(t))
CONTEXT:  SQL function "mangle" during inlining

在我们的例子中，这仅仅是一种烦扰，我们可以通过使用public来避免。函数调用中的uncent（）。但它可能比这更糟糕，尤其是对于安全定义函数。由于模式限定每个函数和运算符很麻烦，建议的解决方案是在函数上强制搜索路径：

ALTER FUNCTION mangle（text）SET search_path=public；

请注意，search_path上的模式应该只允许特权用户创建，因此在v15之前的版本中，上面的内容不是一个好主意！

设置search_path的一个不愉快的缺点是它会阻止SQL函数的内联。

PostgreSQL v14中新的SQL函数语法

从PostgreSQL v14开始，SQL函数和过程的主体不再需要是字符串常量。现在，您可以对函数体使用以下形式之一：

CREATE FUNCTION function_name(...) RETURNS ...
RETURN expression;
 
CREATE FUNCTION function_name(...) RETURNS ...
BEGIN ATOMIC
   statement;
   ...
END;

第一种形式要求函数体是表达式。因此，如果要执行查询，必须将其括在括号中（将其转换为子查询，这是一个有效的表达式）。例如：

CREATE FUNCTION get_data(v_id bigint) RETURNS text
RETURN (SELECT value FROM data WHERE is = v_id);

第二种形式允许您使用多个SQL语句编写函数。与以前的多语句SQL函数一样，函数的结果将是最终SQL语句的结果。您还可以使用第二种形式的新语法来创建SQL过程。第一种形式显然不适用于过程，因为过程没有返回值。

我们可以轻松地重写示例函数以使用新语法：

CREATE OR REPLACE FUNCTION mangle(t text) RETURNS text
RETURN lower(unaccent(t));

注意，这些新的SQL函数可以像旧的一样内联到SQL语句中！

新SQL函数语法的优点

主要区别在于，新型SQL函数和过程在函数定义时进行解析，并以解析后的形式存储在pg_proc系统目录的prosqlbody列中。因此，上述两个缺点消失了：

使用新型SQL函数进行依赖项跟踪

由于函数体以解析形式提供，PostgreSQL可以跟踪依赖项。让我们用重新定义的示例函数来试试：

DROP EXTENSION unaccent;
ERROR:  cannot drop extension unaccent because other objects depend on it
DETAIL:  function mangle(text) depends on function unaccent(text)
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

使用新型SQL函数修复了search_path

search_path仅在解析SQL时相关。由于这种情况现在在CREATE函数运行时发生，因此我们不必担心该参数在函数执行时的当前设置：

SET search_path = pg_catalog;
 
SELECT public.mangle('Schön besser');
 
    mangle    
══════════════
 schon besser
(1 row)

交互式客户端的问题

您可能注意到，用于定义SQL函数的多语句表单包含分号以终止SQL语句。这不仅会混淆像HeidiSQL（从未学习过美元引号）这样的常见嫌疑犯，而且对于任何将分号识别为SQL语句之间分隔符的客户机来说都是一个问题。即使是旧版本的psql也存在语法问题：

psql (13.7, server 15beta2)
WARNING: psql major version 13, server major version 15.
         Some psql features might not work.
Type "help" for help.
 
test=> CREATE FUNCTION tryme() RETURNS integer
BEGIN ATOMIC
   SELECT 42;
END;
ERROR:  syntax error at end of input
LINE 3:    SELECT 42;
                     ^
WARNING:  there is no transaction in progress
COMMIT

psql认为“SELECT 42”后的分号终止CREATE FUNCTION语句。截断的语句导致错误。最后一个结束被视为它自己的语句，它是COMMIT的同义词并引起警告。

在v14及更高版本中，psql正确处理此类语句。pgAdmin 4已经学习了6.3版本的新语法。但我确信，还有许多客户端尚未收到消息。

结论

PostgreSQL v14引入的新的SQL函数语法在可用性和安全性方面具有很大优势。获取一个支持新语法的客户端，并开始将其用于SQL函数。您应该考虑重写现有函数以利用这些优点。

阅读另一篇提高PostgreSQL语法理解能力的文章：我关于PostgreSQL中交叉连接的文章。

通过Hans关于如何查找和修复缺失索引的帖子，了解如何使PostgreSQL数据库发挥最大性能的更多信息。

原文标题：BETTER SQL FUNCTIONS IN POSTGRESQL V14
原文作者：Laurenz Albe
原文链接：https://www.cybertec-postgresql.com/en/better-sql-functions-in-postgresql-v14/