# 数据库函数（Database Function）
#### 场景介绍
建表用于数据存储，增删改查实现数据读写，行级安全控制数据按行可见可控。然而，并非所有需求都能归结为"读写某一行"：跨表统计需聚合多张表的数据，上下架需在同一事务内写入多张表，更新时间需在数据变更时自动刷新。这类逻辑难以用单条SQL表达，需流程控制或自动触发，Data API的自动接口无法覆盖；若交由应用侧实现，则前后端需反复对齐字段与逻辑，一处变更往往波及多处。
本章节介绍的数据库函数（Database Function）即存储过程，用SQL或PL/pgSQL把业务逻辑封装成可复用的过程，写入数据库、供应用按需调用。跨表聚合、事务联动、数据校验、变更自动触发、受控对外开放等，皆可一次定义、在数据侧执行，经PostgREST暴露为RPC接口、由前端.rpc()直接调用；也可绑定为触发器，在数据变更时自动执行（如updated_at自动刷新）。
#### 相关概念
更多详细介绍，请参见[参考文档]。
- **数据库函数**
  数据库函数（Database Function）是保存在PostgreSQL数据库中、可被调用的程序（即存储过程），接收输入参数、在数据库内执行、返回结果。它与Data API的CRUD互补：CRUD面向单表读写，函数面向难以用单条SQL表达，需流程控制、事务保障、或自动触发的场景。函数在数据库侧执行，可调用PostgreSQL全部能力（JSONB、窗口函数、事务、其他函数与触发器等）。
  
- **函数语言**
   表1函数语言 
  | 语言     | 特点                         | 适用                |
  |:---|:---|:---|
  | sql   | 单条SQL查询即可完成，无流程控制，简单高效。 | 查询、计算、关联封装。        |
  | plpgsql | 支持变量、分支、循环、异常处理、事务控制。   | 多步骤逻辑、条件判断、批量处理。 |
     
  二者均为PostgreSQL内置语言。SQL直接以标准SQL查询作为函数体，不含控制流，适合直接封装查询；plpgsql即PL/pgSQL，是PostgreSQL的过程化语言，在SQL之上扩展变量、分支、循环与异常处理，可承载多步骤与条件逻辑，详见[PostgreSQL过程语言](https://www.postgresql.org/docs/current/xplang.html)。简单封装优先用SQL，需流程控制再用plpgsql。
  
- **返回类型**
  表2常用返回类型 
  | 返回类型                    | 说明             | 典型场景             |
  |:---|:---|:---|
  | void                  | 无返回值。        | 仅执行写入或更新，如批量维护。 |
  | 标量（bigint、numeric、text等） | 返回单个值。         | 计数、最值、计算结果。     |
  | SETOF                  | 返回某表的若干行。    | 返回符合条件的数据集合。    |
  | TABLE(字段 类型, ...)      | 返回自定义结构的多行。 | 多表关联、计算列的集合结果。  |
  | trigger                  | 专供触发器调用。       | 自动维护字段、审计、校验。 |
     
  返回集合（SETOF/TABLE）的函数，其结果可经RPC查询，并支持与表相同的链式过滤、分页。
  
- **安全模式**
  函数按"以谁的权限执行"分两种安全模式，直接决定是否受RLS约束。
  表3安全模式 
  | 模式                   | 执行权限        | 是否受RLS约束 | 适用                 |
  |:---|:---|:---|:---|
  | SECURITY INVOKER（默认） | 调用者          | 受约束    | 只处理调用者本就有权访问的数据。   |
  | SECURITY DEFINER    | 函数属主（通常绕过RLS） | 绕过RLS | 需以高权限完成特定操作，且要收口权限。 |
     
  SECURITY DEFINER以属主权限执行并可绕过RLS，虽然便捷但有一定风险。PostgreSQL默认向PUBLIC授予执行权限，位于public模式的SECURITY DEFINER函数可被anon、authenticated角色调用，等同于一个公开的高权限入口。SECURITY DEFINER仅在确实需要绕过RLS时才使用，并务必遵守以下限制：
  - REVOKE ... FROM PUBLIC收回默认执行权。
  
  - 仅GRANT EXECUTE给目标角色。
  
  - 函数体内以auth.uid()校验调用者。
  
  - 用SET search_path锁定模式，防止同名对象被劫持。
   
- **调用方式**
  支持RPC与触发器（Trigger）两种调用方式。
  表4调用方式 
  | 方式             | 触发时机       | 调用方                             |
  |:---|:---|:---|
  | RPC             | 应用主动调用。    | 前端/服务端经SDK的.rpc()，按需传参获取结果。      |
  | 触发器（Trigger） | 数据变更时自动执行。 | 绑定到表的INSERT/UPDATE/DELETE，无需应用介入。 |
     
  返回Trigger类型的函数专供触发器调用，不能经RPC使用；普通函数可经RPC调用，但不能直接绑定为触发器。
  
 
#### 函数和CRUD的使用场景
数据库函数并非替代Data API的CRUD，而是承接CRUD覆盖不到的复杂逻辑。[表5]介绍适用于函数和CRUD的场景。
 表5场景与选型 
| 场景         | 选型                  | 说明                          |
|:---|:---|:---|
| 单表读写        | 仍用CRUD              | Data API已自动生成，无需再封装函数。        |
| 多表关联、聚合统计 | 用函数                   | 封装复杂SQL，前端一次调用获取结果，免手写JOIN。 |
| 跨表事务（多表同增同改） | 用函数               | 在函数内一个事务完成，保证一致性。           |
| 业务校验、流程控制    | 用函数                | 分支、循环、异常处理，逻辑就近数据。          |
| 数据变更自动处理     | 用触发器函数             | 如自动刷新updated_at、写审计日志、级联校验。  |
| 对外仅开放特定操作   | 用SECURITY DEFINER函数 | 不授底层表权限，仅开放受控的函数入口。         |
| 计算列/派生字段    | 用函数                 | 把计算下沉到数据库，前端直接取结果。          |
   
核心价值：**复杂逻辑封装复用、事务一致性、自动触发、受控开放**。
#### 函数的创建方式
创建数据库函数有两种创建方式：在Dashboard的SQL Editor中编写CREATE FUNCTION语句执行，或在"Database \> Functions"页面用图形表单填写创建。二者创建的是同一类数据库对象，可搭配使用。
表6两种创建方式对比 
| 维度                | 方式一：SQL Editor                | 方式二：Database \> Functions   |
|:---|:---|:---|
| 创建方式                | 手动执行CREATE FUNCTION语句。      | 图形表单填写后保存。                  |
| 适合场景              | 任意复杂度，含多步骤PL/pgSQL、权限收口、触发器。 | 结构清晰的简单函数，快速创建。             |
| 语法完整度              | 支持PostgreSQL全部语法与选项。       | 覆盖常用字段，复杂选项受限。             |
| 修改迭代               | 改SQL重跑即可，可存Snippet复用。      | 表单可编辑，复杂修改仍建议回SQL Editor。    |
| 权限收口（REVOKE/GRANT） | 可直接执行。                       | 不提供图形入口，仍需SQL。               |
| 触发器函数与绑定           | 可定义trigger函数并建触发器。            | 建议用SQL Editor或Triggers界面。 |
| 上手难度              | 需熟悉SQL。                    | 可视化界面操作，易上手。               |
   
**选型建议**：简单函数可在"Database \> Functions"页面快速创建雏形；复杂逻辑、SECURITY DEFINER权限收口、触发器函数一律用SQL Editor。
- [方式一：用SQL Editor编写SQL创建（推荐使用、完整可控）]
  
  SQL Editor是创建与调试函数的主要场所，可执行任意SQL，支持全部语法与权限收口操作。
  本章示例基于[数据库API（Database Data API）](https://support.huaweicloud.com/usermanual-rds-pg/rds_supabase_23.html)中已建好的products与product_pricings两张表，先阐述语法，再给出四类典型示例，最后提供操作步骤。
  - **基本语法**
    ```
    CREATE OR REPLACE FUNCTION 函数名(参数名 类型, ...)
    RETURNS 返回类型
    LANGUAGE sql   -- 或 plpgsql
    AS $$
      -- 函数体
    $$;
    ```
    - CREATE OR REPLACE FUNCTION：不存在则创建，存在则替换，便于迭代修改。
    
    - $$：函数体的起止标记，中间可写多行SQL。
    
    - LANGUAGE：选sql或plpgsql，详见[表1]。
     
  
  - **典型示例**
    - **SQL函数：** **封装查询**
      示例场景：顾客按分类统计产品数量。单条SQL即可完成，用sql函数封装，前端无需手写SQL。
      ```
      CREATE OR REPLACE FUNCTION get_product_count_by_category(cat text)
      RETURNS bigint
      LANGUAGE sql
      AS $$
        SELECT count(*) FROM products WHERE category = cat;
      $$;
      ```
      封装了SQL查询，调用方式见[2]：传cat='计算'即返回计算类产品数量。
      
    
    - **PL/pgSQL函数：流程控制**
      示例场景：查询某产品当前最低价，无定价时返回0而非空。需条件处理，用plpgsql。
      ```
      CREATE OR REPLACE FUNCTION get_min_price(pid bigint)
      RETURNS numeric
      LANGUAGE plpgsql
      AS $$
      DECLARE
        min_price numeric;
      BEGIN
        SELECT min(price) INTO min_price
        FROM product_pricings
        WHERE product_id = pid;
        RETURN COALESCE(min_price, 0);   -- 无定价时返回0
      END;
      $$;
      ```
      - DECLARE声明变量；INTO把查询结果写入变量。
      
      - 参数名避免与列名同名（本例用pid而非product_id），否则WHERE product_id = product_id会变成列与自身比较，产生歧义。
      
      - COALESCE将NULL替换为0，避免空值传递到前端。
       
    
    - **返回集合：SETOF与TABLE**
      示例场景：一次取出产品及其定价（多表关联），前端无需手写JOIN。返回自定义结构，用TABLE(...)。
      ```
      CREATE OR REPLACE FUNCTION get_products_with_pricing()
      RETURNS TABLE (
        id bigint,
        name text,
        category text,
        tier text,
        price numeric
      )
      LANGUAGE sql
      AS $$
        SELECT p.id, p.name, p.category, pr.tier, pr.price
        FROM products p
        LEFT JOIN product_pricings pr ON pr.product_id = p.id;
      $$;
      ```
      返回集合的函数经RPC调用后，其结果支持与表相同的过滤、排序、分页，详见[3]，相当于为前端预置了一张"虚拟视图"。
      
    
    - **安全模式与权限收口（SECURITY DEFINER）**
      示例场景：上架新品时须同时写产品表与定价表，要求一个事务内完成，且不直接开放这两张表的写权限。用SECURITY DEFINER封装：函数以属主权限执行，绕过RLS与表级权限，调用方仅需函数的EXECUTE权限即可完成受控操作。
      ```
      CREATE OR REPLACE FUNCTION create_product_with_price(
        p_name text,
        p_category text,
        p_tier text,
        p_price numeric
      )
      RETURNS bigint
      LANGUAGE plpgsql
      SECURITY DEFINER
      SET search_path = public
      AS $$
      DECLARE
        new_id bigint;
      BEGIN
        INSERT INTO products (name, category) VALUES (p_name, p_category)
          RETURNING id INTO new_id;
        INSERT INTO product_pricings (product_id, tier, price) VALUES (new_id, p_tier, p_price);
        RETURN new_id;
      END;
      $$;
      -- 收口权限：移除PUBLIC默认执行权，仅认证用户可调用
      REVOKE ALL ON FUNCTION create_product_with_price(text, text, text, numeric) FROM PUBLIC;
      GRANT EXECUTE ON FUNCTION create_product_with_price(text, text, text, numeric) TO authenticated;
      ```
      注意事项：
      - SECURITY DEFINER+SET search_path = public：锁定模式搜索路径，防止同名对象在不同模式下被劫持。
      
      - 两步INSERT在同一函数体内，处于同一事务，要么全部成功、要么整体回滚，保证产品与定价一致。
      
      - REVOKE ... FROM PUBLIC是安全收口的关键：默认PUBLIC可执行，否则anon也能调用这一高权限入口。
      
      - 仅向authenticated开放EXECUTE，调用方无需拥有products、product_pricings的INSERT权限即可完成上架，以最小暴露实现受控写入。
      
      
      创建或修改函数后，立即刷新schema缓存使其对RPC可见（首次可能有数秒延迟）：
      ```
      NOTIFY pgrst, 'reload schema';
      ```
      不再需要的函数用DROP FUNCTION get_min_price(bigint);删除（须带参数类型以区分同名重载函数）。
      
     
  
  - **SQL Editor操作步骤**
    1. 登录RDS Supabase Dashboard控制台界面。
    
    2. 在左侧导航栏，选择"SQL Editor"。
    
    3. 在编辑区粘贴CREATE FUNCTION语句，具体示例详见[典型示例]。
    
    4. 单击"Run"执行语句，提示成功即函数已创建。
    
    5. 在新查询中执行以下SQL，确认返回结果符合预期。
       ```
       select get_product_count_by_category('计算');
       ```
       
    
    6. 可保存为Snippet（代码片段）便于复用。
    
    7. 创建函数后若RPC尚未识别，执行[3]中步骤来更新缓存。
     
   
- [方式二：用"Database \> Functions"图形界面创建]
  
  Supabase控制台"Database \> Functions"提供图形表单，无需手写CREATE FUNCTION语句：函数名、参数、返回类型、语言、安全模式等以表单填写，函数体仍在表单内SQL框中编写。适合结构清晰的简单函数快速建。
  1. 登录RDS Supabase Dashboard控制台界面。
  
  2. 左侧导航选择"Database\> Functions"。
     图1Database Functions管理页面   
     ![](https://support.huaweicloud.com/usermanual-rds-pg/zh-cn_image_0000002769825192.png "点击放大") 
  
  3. 单击"Create a new function"，进入创建表单页面，填写函数名、所属schema（默认public）、参数（名称+类型，可增删）、返回类型（如void/标量/SETOF/TABLE）、语言（sql/plpgsql）、是否SECURITY DEFINER、函数体（在表单内SQL框中编写）。
     图2Database Functions创建页面   
     ![](https://support.huaweicloud.com/usermanual-rds-pg/zh-cn_image_0000002769985144.png "点击放大") 
  
  4. 确认填写信息后，即可创建函数。
  
  
  **适用范围** ：GUI适合快速创建结构简单的函数与上手验证。以下场景仍建议用[SQL Editor]：
  - 多步骤逻辑场景（含分支、循环、异常处理）。
  
  - SECURITY DEFINER的权限收口场景（REVOKE/GRANT），GUI不提供此类图形入口。
  
  - 返回Trigger类型的触发器函数及其绑定场景，使用SQL建立、在Triggers界面可视化管理。
  
  
  GUI与SQL Editor创建的是同一类数据库对象。GUI建立雏形后，复杂的迭代修改可在SQL Editor用CREATE OR REPLACE继续优化。
#### 函数调用（RPC方式）
定义在public等已暴露模式下的函数经PostgREST自动暴露为RPC接口（RPC即远程过程调用，此处指经HTTP接口调用数据库函数），前端可通过Supabase SDK来进行rpc()调用。以下示例代码以JavaScript语言进行说明。
调用权限遵循与CRUD相同的规则：以Publishable Key未登录发起时以anon身份执行（受RLS约束），登录后以authenticated执行；SECURITY DEFINER函数则按属主权限执行。
1. **无参调用**
   示例场景：获取全部产品及其定价清单。
   ```
   const { data, error } = await supabase.rpc('get_products_with_pricing')
   if (error) { console.error(error); return }
   // data示例：[{ id: 1, name: '云服务器 ECS', category: '计算', tier: '包月', price: 99.00 }, ...]
   ```
   无参函数调用时第二参数可省略。返回值结构与函数RETURNS TABLE定义一一对应。
   
2. **带参调用**
   示例场景：查询id为1的产品的最低价。
   ```
   // 按命名参数传参（推荐，参数名与定义一致）
   const { data, error } = await supabase.rpc('get_min_price', { pid: 1 })
   if (error) { console.error(error); return }
   // data为函数返回的标量，如0或99.00
   // 统计某分类产品数量
   const { data: count } = await supabase.rpc('get_product_count_by_category', { cat: '计算' })
   ```
   .rpc()第二参数为对象，键名对应函数的参数名；PostgREST据此按命名参数绑定，无需关注位置。
   
3. **结果过滤与分页**
   返回集合的函数结果支持与表相同的链式过滤、排序、分页，避免将全部数据传输至前端再筛选。
   示例场景：从产品定价清单中只取计算类、按名称排序的前10条。
   ```
   // 对get_products_with_pricing()结果链式过滤、排序、分页
   const { data, error } = await supabase
     .rpc('get_products_with_pricing')
     .eq('category', '计算')
     .order('name')
     .limit(10)
   ```
   .eq()、.order()、.limit()等链式方法作用于函数返回的集合，用法与表查询一致，详见[数据库API（Database Data API）](https://support.huaweicloud.com/usermanual-rds-pg/rds_supabase_23.html)中SDK基本操作。
   
 
#### 触发器函数（数据变更自动执行）
触发器函数返回Trigger类型，在数据变更时由数据库自动执行，无需应用调用。它常用于把"数据变更、自动处理"的需求交给数据库自动完成。在[数据库API（Database Data API）](https://support.huaweicloud.com/usermanual-rds-pg/rds_supabase_23.html)中提到updated_at仅在插入时取默认值、修改时不会自动刷新，要实现自动刷新需借助触发器，这正是其典型用法之一。
1. **字段自动维护**
   示例场景：产品信息被修改时，自动把updated_at刷新为当前时间，避免应用每次更新都手动传值。
   先定义返回trigger的函数。
   ```
   CREATE OR REPLACE FUNCTION set_updated_at()
   RETURNS trigger
   LANGUAGE plpgsql
   AS $$
   BEGIN
     NEW.updated_at = now();   -- 将新行updated_at置为当前时间
     RETURN NEW;               -- 返回新行，使其继续写入
   END;
   $$;
   ```
   再将其绑定到目标表的UPDATE。
   ```
   CREATE TRIGGER trg_products_updated_at
     BEFORE UPDATE ON products      -- 更新前执行，改写updated_at
     FOR EACH ROW                  -- 每行触发
     EXECUTE FUNCTION set_updated_at();
   ```
   此后对products执行任何UPDATE，updated_at都会自动刷新，无需应用层干预。products表已有updated_at列，可直接绑定；product_pricings表无该列，如需同样自动维护，先为其增加列再绑定同一触发器。
   ```
   -- 为product_pricings增加updated_at列（原表仅有created_at）
   ALTER TABLE product_pricings ADD COLUMN updated_at TIMESTAMPTZ DEFAULT now();
   CREATE TRIGGER trg_pricings_updated_at
     BEFORE UPDATE ON product_pricings
     FOR EACH ROW
     EXECUTE FUNCTION set_updated_at();
   ```
   注意：触发器函数不能经RPC调用；BEFORE返回NEW使修改生效、返回NULL则取消该行操作。触发器也可在Dashboard的Triggers界面可视化管理，详见[数据库触发器（Trigger）](https://support.huaweicloud.com/usermanual-rds-pg/rds_supabase_25.html)。
   
2. **变更审计**
   示例场景：记录产品价格变更前后的值，便于追溯。先建审计表，再以触发器在更新时写入。
   ```
   CREATE TABLE product_price_audit (
     id          BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
     product_id  BIGINT NOT NULL,
     old_price   NUMERIC(10,2),
     new_price   NUMERIC(10,2),
     changed_at  TIMESTAMPTZ DEFAULT now()
   );
   CREATE OR REPLACE FUNCTION log_price_change()
   RETURNS trigger
   LANGUAGE plpgsql
   AS $$
   BEGIN
     IF NEW.price IS DISTINCT FROM OLD.price THEN   -- 仅价格变化才记录
       INSERT INTO product_price_audit (product_id, old_price, new_price)
       VALUES (NEW.product_id, OLD.price, NEW.price);
     END IF;
     RETURN NEW;
   END;
   $$;
   CREATE TRIGGER trg_pricing_audit
     AFTER UPDATE ON product_pricings   -- 更新后记录，确保新值已落库
     FOR EACH ROW
     EXECUTE FUNCTION log_price_change();
   ```
   注意：AFTER确保数据已写入再审计；IS DISTINCT FROM可安全比较含NULL的值；审计表可单独配RLS，仅运维可见。
   
 
#### 管理与维护
函数与触发器创建后的查看、修改、删除、缓存刷新等日常管理，主要通过SQL Editor与Database两个入口完成。
表7Dashboard函数相关入口 
| 入口                      | 主要用途                  |
|:---|:---|
| SQL Editor              | 编写、执行、测试、迭代函数与触发器SQL。 |
| Database \> Functions | 图形创建函数、查看函数定义。        |
| Database \> Triggers    | 可视化创建、启用、禁用、删除触发器。     |
   
1. **查看函数** **。**
   进入"Database \> Functions"，列出当前schema下的函数，可查看其名称、参数、返回类型与定义。界面以查看为主，同时支持可视化创建，详见[方式二：用"Database \> Functions"图形界面创建]；函数体的修改与迭代建议在SQL Editor完成，详见[方式一：用SQL Editor编写SQL创建（推荐使用、完整可控）]。
   
2. **修改与删除函数** **。**
   **修改函数** ：函数用**CREATE OR REPLACE FUNCTION** 覆写，既可在SQL Editor修改SQL重跑，也可在"Database \> Functions"表单编辑。修改后须刷新schema缓存，详见[3]。
   **删除** **函数** ：不再需要的函数通过**DROP FUNCTION**删除，须带参数类型以区分同名重载函数。
   ```
   DROP FUNCTION get_min_price(bigint);
   ```
   
3. **刷新schema缓存** **。**
   创建或修改函数后，须刷新PostgREST的schema缓存，函数才对RPC可见（首次可能有数秒延迟）。
   ```
   NOTIFY pgrst, 'reload schema';
   ```
   
4. **管理触发器** **。**
   进入"Database \> Triggers"页面，可视化创建触发器：选择目标表、触发时机（BEFORE/AFTER）、事件（INSERT/UPDATE/DELETE）、绑定触发器函数。也可在该页面启用、禁用或删除已有触发器。
   注意：SECURITY DEFINER函数的权限收口（REVOKE/GRANT）须以SQL执行，详见[方式一：用SQL Editor编写SQL创建（推荐使用、完整可控）]，界面不提供此类操作的图形入口。
   
 
#### 综合案例
示例场景：运营需要一个"产品概览"接口，按分类汇总产品数量、最低价格及最高价格，供后台看板展示。由于涉及多表关联聚合，CRUD难以一次性表达，使用函数封装最为合适。
定义函数：按分类聚合，关联定价表取价格区间。
```
CREATE OR REPLACE FUNCTION get_category_summary()
RETURNS TABLE (
  category      text,
  product_count bigint,
  min_price     numeric,
  max_price     numeric
)
LANGUAGE sql
AS $$
  SELECT p.category,
         count(DISTINCT p.id),
         min(pr.price),
         max(pr.price)
  FROM products p
  LEFT JOIN product_pricings pr ON pr.product_id = p.id
  GROUP BY p.category
  ORDER BY p.category;
$$;
```
刷新缓存并由SDK调用（以JavaScript为例）：
```
NOTIFY pgrst, 'reload schema';
```
```
const { data, error } = await supabase.rpc('get_category_summary')
if (error) { console.error(error); return }
// data示例：
// [
//   { category: '计算', product_count: 2, min_price: 99.00, max_price: 588.00 },
//   { category: '存储', product_count: 1, min_price: 45.00, max_price: 45.00 }
// ]
```
如需只看某一类，可在结果上链式过滤，也可改造函数加分类参数：
```
// 复用3中的链式过滤：只看"计算"类
const { data } = await supabase.rpc('get_category_summary').eq('category', '计算')
```
本案例将"多表关联+聚合+排序"一次封装进数据库，前端仅需一次RPC即可获得聚合结果，无需自建后端接口，也无需将明细传输至前端再统计。
 #### 参考文档
以下为Supabase开源相关文档，仅供参考。
- [Supabase数据库函数](https://supabase.com/docs/guides/database/functions)
- [Supabase JavaScript SDK --- rpc](https://supabase.com/docs/reference/javascript/rpc)
- [PostgreSQL --- CREATE FUNCTION](https://www.postgresql.org/docs/current/sql-createfunction.html)
- [PostgreSQL --- 触发器](https://www.postgresql.org/docs/current/trigger-definition.html)