本文通过PG邮件列表里的一个问题作为背景介绍pg_get_function_sqlbody函数和begin atomic新形式的SQL函数。
- pg_get_function_sqlbody函数背景
- 新形式的SQL函数：begin atomic语法
一、pg_get_function_sqlbody函数背景
从PG的邮件列表看到如下一个问题：
create or replace function f_fahrenheit_celsius_new(numeric) returns numeric
language sql immutable strict leakproof
return round((($1 - 32.0) * 5.0 / 9.0),3);
在PG14里创建上面的函数之后使用视图information_schema.routines查看定义时为空：
postgres=# select
routine_schema,
routine_name,
routine_definition
from information_schema.routines
where routine_name ='f_fahrenheit_celsius_new';
routine_schema | routine_name | routine_definition
----------------+--------------------------+--------------------
public | f_fahrenheit_celsius_new |
(1 row)
看到回复需要使用pg_get_function_sqlbody函数进行查看
postgres=# select pg_get_function_sqlbody('f_fahrenheit_celsius_new'::regproc);
pg_get_function_sqlbody
----------------------------------------------
RETURN round(((($1 - 32.0) * 5.0) / 9.0), 3)
(1 row)
原来PG14版本提供了一种新式函数，函数体定义存储于pg_proc的prosqlbody列，需要使用pg_get_function_sqlbody进行解析。
二、新形式的SQL函数
当我们声明函数体时，需要使用使用一对$$符号来标记代码为字符串常量，例如下面是两个整数相加的一个简单函数。
create function f_add(int, int)
returns int
language sql
as $$
select $1 + $2;
$$;
如果我们编写另外一个函数，调用上面定义的函数，比如对多个整数使用f_add函数相加：
create function f_add_nest(int, int, int, int)
returns int
language sql
as $$
select f_add(f_add($1, $2), f_add($3, $4));
$$;
创建这两个函数后，运行f_add_nest函数，正常返回如下结果：
postgres=# select f_add_nest(1,1,2,2);
f_add_nest
------------
6
(1 row)
但如果我们要删除f_add函数，如下所示它确实可以被成功删除。
postgres=# drop function f_add(int, int);
DROP FUNCTION
但是删除f_add函数之后，我们再次运行f_add_nest函数会失败。如下所示：
postgres=# select f_add_nest(1,1,2,2);
ERROR: function f_add(integer, integer) does not exist
LINE 2: select f_add(f_add($1, $2), f_add($3, $4));
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:
select f_add(f_add($1, $2), f_add($3, $4));
CONTEXT: SQL function "f_add_nest" during inlining
在PG 14之前我们创建自定义函数缺少依赖性检测，删除某个函数可能会破坏其他函数，PG 14之前无法跟踪函数主体内的依赖关系。
PG 14对使用SQL language的函数或存储过程增加了一种新的语法形式，函数体使用begin atomic语法。新形式的SQL主体不再需要使用一对$$符号标记为字符串常量。
我们再次使用上面的示例，看看新形式的SQL函数有什么变化。
create function f_add2(int, int)
returns int
language sql
begin atomic;
select $1 + $2;
end;
上面的函数体不再表示为字符串，而是实际的代码语句，我们可以通过系统函数pg_get_function_sqlbody来进行查看。
postgres=# select pg_get_function_sqlbody('f_add2'::regproc);
pg_get_function_sqlbody
-------------------------
BEGIN ATOMIC +
SELECT ($1 + $2); +
END
(1 row)
接着再创建新函数f_add_nest2，它调用f_add2函数：
create function f_add_nest2(int, int, int, int)
returns int
language sql
begin atomic;
select f_add2(f_add2($1, $2), f_add2($3, $4));
end;
与之前预期一样，f_add_nest2正常工作：
postgres=# select f_add_nest2(1,1,2,2);
f_add_nest2
-------------
6
(1 row)
现在尝试删除f_add2函数：
postgres=# drop function f_add2(int, int);
ERROR: cannot drop function f_add2(integer,integer) because other objects depend on it
DETAIL: function f_add_nest2(integer,integer,integer,integer) depends on function f_add2(integer,integer)
HINT: Use DROP ... CASCADE to drop the dependent objects too.
可以看出使用begin atomic新形式的SQL函数可以防止我们删除某个函数，避免对有依赖引用的函数产生损坏。
如果我们使用cascade进行删除，会删除所有依赖的函数，如下：
postgres=# drop function f_add2(int, int) cascade;
NOTICE: drop cascades to function f_add_nest2(integer,integer,integer,integer)
DROP FUNCTION
使用cascade清理测试函数比较方便，但在生产环境进行级联删除时要小心，以免意外删除重要对象。
三、结论
新形式的SQL函数可以帮我们更好的管理用户自定义函数，防止我们意外删除函数，推荐使用这个新特性。
保持联系
现组建了一个PG乐知乐享交流群，欢迎关注文章的小伙伴加微信进群吹牛唠嗑，交流技术。
