50 函数与 PL/pgSQL

使用适当的易变性和安全设置定义 SQL 与 PL/pgSQL 函数。

PostgreSQL 允许你定义自己的函数。最简单的是SQL 函数——函数体由一条或多条 SQL 语句组成；PostgreSQL 通常可以将它们内联。功能更强的是PL/pgSQL 函数——用服务器内置的过程语言编写，支持变量、循环、条件和异常。PL/pgSQL 用于触发器、复杂业务逻辑和一次性维护例程。还有 PL/Python、PL/Perl 等语言，但 PL/pgSQL 始终可用，也是你应该首先学习的语言。

本章要点

CREATE FUNCTION 语法
LANGUAGE sql 与 LANGUAGE plpgsql 的区别
易变性类别：IMMUTABLE、STABLE、VOLATILE
PL/pgSQL 中的变量、IF、LOOP 和 RAISE NOTICE
SECURITY DEFINER 与 search_path 警告

示例

示例 50.1 函数与 PL/pgSQL

-- A simple SQL function
CREATE OR REPLACE FUNCTION square(x integer)
RETURNS integer
LANGUAGE sql
IMMUTABLE
AS $$
  SELECT x * x;
$$;

SELECT square(7) AS result;

-- A SQL function that returns a set (multiple rows)
CREATE OR REPLACE FUNCTION recent_orders(min_total numeric)
RETURNS TABLE (id integer, customer_id integer, total numeric)
LANGUAGE sql
STABLE
AS $$
  SELECT id, customer_id, total
  FROM orders_example
  WHERE total >= min_total
  ORDER BY id;
$$;

SELECT * FROM recent_orders(20);

-- A PL/pgSQL function with control flow
CREATE OR REPLACE FUNCTION classify_total(total numeric)
RETURNS text
LANGUAGE plpgsql
IMMUTABLE
AS $$
DECLARE
  result text;
BEGIN
  IF total IS NULL THEN
    result := 'unknown';
  ELSIF total < 20 THEN
    result := 'small';
  ELSIF total < 100 THEN
    result := 'medium';
  ELSE
    result := 'large';
  END IF;
  RETURN result;
END;
$$;

SELECT id, total, classify_total(total) AS bucket
FROM orders_example
ORDER BY id;

-- A function that loops and uses RAISE NOTICE (a print statement)
CREATE OR REPLACE FUNCTION ping(n integer)
RETURNS integer
LANGUAGE plpgsql
AS $$
DECLARE
  i integer;
BEGIN
  FOR i IN 1..n LOOP
    RAISE NOTICE 'ping %', i;
  END LOOP;
  RETURN n;
END;
$$;

SELECT ping(3);

-- Cleanup
DROP FUNCTION IF EXISTS square(integer);
DROP FUNCTION IF EXISTS recent_orders(numeric);
DROP FUNCTION IF EXISTS classify_total(numeric);
DROP FUNCTION IF EXISTS ping(integer);

函数声明包括名称、参数列表、返回类型、语言、可选的易变性属性和函数体。函数体由美元引用（$$ ... $$）包围——这样无需转义函数体内的引号。如果需要嵌套美元引用，也可以使用 $tag$ ... $tag$。

易变性类别对于性能至关重要：

IMMUTABLE：相同输入始终产生相同输出，不涉及数据库状态。规划器可以把这些调用折叠为表达式中的常量，甚至在规划时预先求值。用于数学运算、字符串格式化和确定性转换。
STABLE：相同输入在单条语句内产生相同输出，但跨语句可能不同（例如读取表的函数）。这允许进行一些优化，例如将这类函数用于索引扫描条件；但这并不保证每条语句只求值一次。
VOLATILE（默认）：即使在同一条语句内也可能返回不同结果（例如 random()、clock_timestamp()，以及任何会修改数据的函数）；now() 是 STABLE。规划器每次都会重新求值。

错误的易变性声明会导致错误的查询计划——大多数初学者把所有函数都保留为 VOLATILE，因而承担性能代价。只要确实满足条件，就应将函数标记为 IMMUTABLE / STABLE。

运行示例

运行方式：

$ psql -f source/functions-and-plpgsql.sql postgres
CREATE FUNCTION
 result
--------
     49
(1 row)
 ...
NOTICE:  ping 1
NOTICE:  ping 2
NOTICE:  ping 3
 ping
------
    3
(1 row)

常见陷阱

定义 SECURITY DEFINER 函数（以函数所有者身份运行）却不设置 search_path，是已知的权限提升模式。只使用受信任的模式，明确把 pg_temp 放在最后，并为敏感对象名称加上限定名；例如，在 app 受信任时设置 SET search_path = pg_catalog, app, pg_temp。
函数实际读取表却标记为 IMMUTABLE——规划器会缓存错误的值。只有真正的数学/字符串函数才应使用 IMMUTABLE。
允许按参数类型重载，但容易被滥用。recent_orders(numeric) 与 recent_orders(integer) 是不同的函数；传入较小的数值时，可能意外匹配到错误的重载。
PL/pgSQL 使用以分号结束的语句。关键字和未加引号的标识符不区分大小写；带引号的标识符和字符串内容区分大小写。函数体内使用保留字时要小心。

提示

存储过程（CREATE PROCEDURE，PostgreSQL 11+）类似函数，但可以管理事务（在过程体内执行 COMMIT、ROLLBACK）。将函数用于需要返回值的逻辑；将过程用于需要事务控制的批处理操作。

动手练习

编写一个返回折后价格的 IMMUTABLE SQL 函数 discount(price, pct)。在查询中使用它：SELECT id, total, discount(total, 10) FROM orders_example;。然后编写一个 PL/pgSQL 函数，用循环或单条 SQL 查询返回每个客户的最大订单金额（SQL 形式快得多——两种都试试）。

50 函数与 PL/pgSQL

本章要点

示例

运行示例

常见陷阱

下载