# 函数与 PL/pgSQL

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

---

LLMS 索引： [llms.txt](/llms.txt)

---

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

## 本章要点 {#learn}

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

## 示例 {#example}

```sql {num="50.1" caption="函数与 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`。

## 运行示例 {#run}

运行方式：

```text
$ 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)
```

## 常见陷阱 {#pitfalls}

- 定义 `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 使用以分号结束的语句。关键字和未加引号的标识符不区分大小写；带引号的标识符和字符串内容区分大小写。函数体内使用保留字时要小心。

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

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

## 下载 {#downloads}

SQL 文件：[functions-and-plpgsql.sql](/examples/source/functions-and-plpgsql.sql) · [全部示例文件](/examples/examples.zip) · [准备工作](/#prerequisites)