Database Functions 與 Triggers 實戰 | Supabase 完整教學

在 Supabase 底層的 Postgres 裡，資料庫不只是被動存放資料的倉庫，它還能「自己會思考、會反應」。用 Database Functions 把商業邏輯封裝在資料庫層，用 Triggers 在資料變更的瞬間自動執行動作——這篇帶你用 plpgsql 寫出可執行的函式與觸發器，包含 security definer 的資安要點、自動更新 updated_at、註冊時自動建 profile 等經典模式。

前言

一句話定義本篇主題：這篇要教你在 Supabase 的 Postgres 裡寫 Database Functions（伺服器端函式）與 Triggers（資料變更時自動執行的觸發器），涵蓋 create function 的寫法、回傳型別、security definer vs security invoker 的資安差異，以及幾個生產環境天天在用的經典模式。

上一篇《Postgres Extensions》我們逛完了 Postgres 的「工具箱」，知道 pgvector、pg_cron、postgis 等擴充套件各自解決什麼問題，把資料庫的能力邊界拉大了一圈。但擴充套件是「別人寫好的能力」，這一篇要教你的則是「寫你自己的能力」——把你的商業邏輯直接種進資料庫，讓它在對的時機自動運轉。

用一個現實類比：Function 像是你交給一位資深員工的「標準作業程序（SOP）」，而 Trigger 像是辦公室裝的「自動感應器」。 你把一段複雜流程（例如「計算某使用者的會員等級」）寫成 SOP（Function），之後任何人只要喊一聲函式名字，它就照著跑、回傳結果，你不必每次重述細節。而 Trigger 則是裝在門口的感應器：只要有人進門（資料被 insert）、有東西被移動（update）、有物品被拿走（delete），感應器就自動觸發預設動作——開燈、記錄時間、發通知。你不必盯著門口，感應器 24 小時替你值班。這兩者結合，就能讓資料庫從「被動倉庫」升級成「主動的邏輯中樞」。

本篇你會學到：

• Function 是什麼：create function 的語法、plpgsql 與 sql 兩種語言、回傳型別（純量、setof、trigger）
• 資安核心：security definer vs security invoker、為什麼 definer 一定要設 search_path
• Trigger 是什麼：觸發時機（before／after）× 事件（insert／update／delete）的組合、new 與 old 兩個特殊變數
• 經典實作：updated_at 自動更新、handle_new_user 註冊時建 profile、審計日誌，以及避免無限迴圈等常見坑

核心概念

Database Function 是什麼

**Database Function（資料庫函式）**是一段儲存在資料庫內、可被重複呼叫的具名程式碼。你把一組 SQL 或程序邏輯封裝成函式，之後只要呼叫函式名稱、傳入參數，資料庫就會執行並回傳結果。它跑在資料庫內部，離資料最近，因此處理大量資料的彙總、多表 join、交易操作時，效能與一致性都優於把資料撈到應用層再處理。

在 Supabase／Postgres 裡，函式主要用兩種語言撰寫：

• language sql：適合單純的查詢或運算，本體就是一段 SQL。寫法簡潔，Postgres 也更容易對它做優化（inline）。
• language plpgsql：Postgres 的程序式語言（Procedural Language/PostgreSQL），支援變數宣告、if／loop 流程控制、例外處理。當邏輯較複雜、或你要寫觸發器函式時，用 plpgsql。

一個函式的骨架長這樣：

create or replace function 函式名(參數 型別)
returns 回傳型別          -- 純量型別、setof 表、或 trigger
language plpgsql          -- 或 sql
security invoker          -- 或 definer（見下節）
set search_path = ''      -- 資安設定（definer 必加）
as $$
begin
  -- 函式主體
  return ...;
end;
$$;

回傳型別很有彈性：可以回傳純量（integer、text、boolean）、回傳一整張表的多列（returns setof 表名 或 returns table(...)），也可以回傳特殊的 trigger 型別——這種函式專門給觸發器使用，稍後會詳談。

security definer vs security invoker：最重要的資安觀念

這是整篇最關鍵、也最容易踩雷的地方。每個函式都有一個「執行身份」的設定，決定它以誰的權限執行：

用類比理解：security invoker 像是「用你自己的門禁卡進辦公室」——你能開的門就是你能開的門，函式看得到的資料就是你看得到的資料，一切受你的 RLS 政策約束。security definer 則像是「函式手上握有管理員的萬用卡」——它以建立者（通常是 postgres 高權限角色）的身份執行，能繞過呼叫者的 RLS 限制，做一些一般使用者本來做不到的事。

那為什麼需要 definer？最經典的例子就是 handle_new_user：一般使用者不該有直接寫入 profiles 表的權限（否則他能亂改別人的資料），但你又希望「使用者一註冊，就自動幫他在 profiles 建一筆對應資料」。這個動作需要繞過限制、以受控方式寫入，正是 security definer 的用武之地。

但 definer 有一條鐵律：

使用 security definer 時，必須加上 set search_path = ''（或明確指定 schema 清單）。

原因是 search_path 決定 Postgres 尋找資料表、函式的路徑。如果你不鎖定它，攻擊者有機會在自己有權限的 schema 裡建立一個同名的惡意函式或資料表，讓你的高權限 definer 函式「找錯物件」而執行到惡意程式碼——這就是 schema 注入。設 set search_path = '' 後，函式內所有物件都必須寫完整路徑（如 public.profiles、auth.users），杜絕這種攻擊。這是 Supabase 官方與 Postgres 社群一致的安全基準，務必遵守。

Trigger 是什麼：時機 × 事件的矩陣

**Trigger（觸發器）**是「當某張表發生某種資料變更時，自動執行一個函式」的機制。它由兩個元件組成：

1. Trigger Function（觸發器函式）：一個 returns trigger 的 plpgsql 函式，裡面寫「要做什麼」。
2. Trigger（觸發器物件）：用 create trigger 綁定，指定「什麼時機、對哪張表、哪個事件」呼叫上面那個函式。

觸發時機與事件可以自由組合，形成一個矩陣：

在觸發器函式裡，你可以用兩個特殊變數存取變更前後的資料：

• new：即將寫入（insert／update）的那一列。delete 時為 null。
• old：變更前（update／delete）的那一列。insert 時為 null。

before 和 after 的關鍵差異：before 觸發器在資料「還沒定案」前執行，因此你可以修改 new 的欄位值，Postgres 會拿修改後的 new 去寫入——這正是自動填 updated_at 能成立的原因。而 after 觸發器在資料「已寫入」後執行，new／old 都是唯讀的，適合做審計、通知、更新別張表等「後續動作」。記住這個原則能幫你避開大部分觸發器的坑。

實作範例

打開 Supabase 的 SQL Editor，跟著逐段執行。我們先做一個純函式，再依序完成三個生產環境天天在用的觸發器模式。

範例一：一個可從前端呼叫的 Function

先感受一下最單純的函式。假設有一張 posts 表，我們寫一個函式，傳入使用者 id 回傳他所有貼文：

-- setof 回傳「多列 posts」；security invoker 讓它受呼叫者的 RLS 約束
create or replace function public.get_user_posts(user_uuid uuid)
returns setof public.posts
language sql
security invoker
set search_path = ''
as $$
  select * from public.posts
  where user_id = user_uuid
  order by created_at desc;
$$;

因為它定義在 public schema，Supabase 會自動把它暴露成一個 RPC 端點，前端可以這樣呼叫：

// 前端用 supabase.rpc() 呼叫，函式名與參數對應
const { data, error } = await supabase.rpc('get_user_posts', {
  user_uuid: userId,
})
// data 就是該使用者的貼文陣列

這就是把邏輯封裝進資料庫的好處：前端一次呼叫，資料庫替你處理查詢與排序。RPC 的完整用法（參數傳遞、錯誤處理、權限）會在第 015 篇深入，這裡先建立印象。

範例二：updated_at 自動更新（before update）

幾乎每張表都有 updated_at 欄位，用來記錄「這列最後被改的時間」。與其每次 update 都手動帶入 now()（容易漏、也容易被前端造假），不如用 before update 觸發器自動填。

第一步，寫一個共用的觸發器函式：

-- 回傳 trigger 型別；在寫入前把 new.updated_at 改成現在時間
create or replace function public.handle_updated_at()
returns trigger
language plpgsql
security invoker
set search_path = ''
as $$
begin
  new.updated_at = now();  -- 直接改寫即將寫入的那一列
  return new;              -- 回傳修改後的 new，Postgres 拿它去寫入
end;
$$;

第二步，把這個函式綁到你的表上（可重複用在很多張表）：

-- 每次 update posts 之前，先跑 handle_updated_at
create trigger set_posts_updated_at
before update on public.posts
for each row
execute function public.handle_updated_at();

之後你只要 update posts set title = '新標題' where id = 1;，updated_at 就會自動變成當下時間，完全不用在 SQL 裡提到它。

為什麼這裡不會無限迴圈？ 因為我們是在 before 觸發器裡「修改 new」，而不是「對表下另一條 update」。整個過程只有你原本那一次寫入，改的是它即將寫進去的內容，不會再觸發第二次 update。這是關鍵，稍後在常見錯誤會再對比。

範例三：handle_new_user 註冊時自動建 profile（after insert + security definer）

這是 Supabase 專案最經典的模式。使用者透過 Supabase Auth 註冊時，會在 auth.users 表新增一列。我們希望同時在自己的 public.profiles 表建一筆對應資料（存暱稱、頭像等應用層資訊）。

先確保有一張 profiles 表：

-- profiles 主鍵直接對應 auth.users 的 id
create table if not exists public.profiles (
  id uuid primary key references auth.users(id) on delete cascade,
  username text,
  avatar_url text,
  created_at timestamptz default now()
);

接著寫觸發器函式。注意這裡必須用 security definer——因為觸發器是在 auth schema 的 users 表上觸發，而一般使用者沒有寫入 public.profiles 的權限，需要以高權限身份受控地插入：

-- security definer：以建立者身份執行，才能寫入 profiles
-- set search_path = ''：definer 的鐵律，所有物件寫完整路徑防 schema 注入
create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer
set search_path = ''
as $$
begin
  insert into public.profiles (id, username)
  values (
    new.id,
    new.raw_user_meta_data ->> 'username'  -- 從註冊時帶的 metadata 取暱稱
  );
  return new;
end;
$$;

最後把它綁到 auth.users 表上，用 after insert（使用者確實建立後才建 profile）：

-- 每當 auth.users 新增一列，就自動建對應 profile
create trigger on_auth_user_created
after insert on auth.users
for each row
execute function public.handle_new_user();

從此以後，任何人透過 Supabase Auth 註冊，profiles 就自動同步生出一筆對應資料——前端完全不用額外呼叫 API 去建 profile。這正是「讓資料庫主動反應」的威力。

範例四：審計日誌（after update）

第四個常見需求是審計（audit）：把某張重要表的每次變更都記錄下來，方便日後追查「誰在什麼時候把什麼改成什麼」。這用 after 觸發器最合適，因為變更已定案，我們只做「事後記錄」。

先建審計表：

create table if not exists public.posts_audit (
  id bigint generated always as identity primary key,
  post_id bigint,
  old_data jsonb,     -- 變更前的整列（用 jsonb 存最省事）
  new_data jsonb,     -- 變更後的整列
  changed_by uuid,    -- 由誰觸發
  changed_at timestamptz default now()
);

審計觸發器函式，把 old 與 new 轉成 jsonb 存進去：

create or replace function public.audit_posts_changes()
returns trigger
language plpgsql
security definer
set search_path = ''
as $$
begin
  insert into public.posts_audit (post_id, old_data, new_data, changed_by)
  values (
    new.id,
    to_jsonb(old),        -- 變更前快照
    to_jsonb(new),        -- 變更後快照
    auth.uid()            -- 目前登入者
  );
  return new;
end;
$$;

-- after update：資料改完後才記錄
create trigger audit_posts_update
after update on public.posts
for each row
execute function public.audit_posts_changes();

現在每次有人更新 posts，posts_audit 就自動多一筆完整的變更快照。用 to_jsonb() 把整列存成 jsonb 是很實用的技巧——不管表結構怎麼變，審計邏輯都不用改。

常見錯誤與最佳實踐

坑一：security definer 忘了設 search_path。 這是最嚴重的資安漏洞。少了 set search_path = ''，攻擊者可能在自己有權限的 schema 建同名物件，讓你的高權限 definer 函式執行到惡意程式碼（schema 注入）。正確做法：所有 security definer 函式一律加 set search_path = ''，並在函式內把每個物件寫成完整路徑（public.profiles、auth.users）。這是不可妥協的基準線。

坑二：該用 invoker 卻濫用 definer。 security definer 會繞過 RLS，等於在你的安全牆上開洞。有人為了「省麻煩」把函式全設成 definer，結果不小心讓使用者能透過函式讀到別人的資料。正確做法：能用 security invoker 就用 invoker（它是預設，且受 RLS 保護）；只有在確實需要以高權限受控執行某動作時（如 handle_new_user）才用 definer，並把函式邏輯限縮到最小、只做該做的那一件事。

坑三：觸發器無限迴圈。 在 after update 觸發器裡又對同一張表下 update（例如去改 updated_at），那條 update 會再次觸發同一個觸發器，一路遞迴到堆疊爆炸。正確做法：凡是「要改寫本列欄位」的需求（updated_at、正規化欄位值），一律用 before 觸發器直接改 new 再 return new，絕不在觸發器裡對本表下額外的 update。after 只留給「不回頭改本列」的後續動作（審計、通知、寫別表）。

坑四：搞錯 before／after 與 new／old 的可用性。 在 after 觸發器裡改 new 是無效的（資料已定案）；在 insert 觸發器裡讀 old 會拿到 null（沒有舊值）；在 delete 觸發器裡讀 new 也是 null。正確做法：改寫資料用 before；insert 只有 new、delete 只有 old、update 兩者都有——依事件選對變數。

坑五：觸發器藏太多副作用，難以追蹤。 觸發器是「隱形」執行的，一次 insert 背後可能連鎖觸發好幾個函式、寫好幾張表。埋太多邏輯會讓「為什麼多出一筆資料 / 為什麼變慢」變得極難除錯。正確做法：一個觸發器只做一件明確的事、取有意義的名字（set_posts_updated_at、on_auth_user_created），並在團隊文件或 migration 註解裡記錄它的用途；複雜或可延遲的副作用（如發外部 webhook）考慮改用非同步機制，別全塞進同步觸發器。

最佳實踐總結：

• security definer 一律配 set search_path = ''，物件寫完整路徑，杜絕 schema 注入
• 預設用 security invoker，只在必要時（如建 profile）才用 definer，且邏輯最小化
• 改寫本列用 before、後續動作用 after，永遠不在觸發器裡對本表下額外 update
• 依事件選對 new／old：insert 用 new、delete 用 old、update 兩者皆可
• 觸發器單一職責、命名清楚、留註解，避免副作用失控難追蹤
• 複雜查詢邏輯封裝成 Function，前端用 rpc() 呼叫（第 015 篇深入），減少往返、集中管理

小結

這篇是 Supabase 系列教學的第 007 篇。承接上一篇《Postgres Extensions》——我們逛完了 Postgres 的擴充工具箱，這篇則教你寫「自己的能力」，讓資料庫從被動倉庫升級成主動的邏輯中樞：

• Function 是什麼：create function 封裝伺服器端邏輯，sql／plpgsql 兩種語言，回傳純量、setof 或 trigger；public 的函式可用 rpc() 從前端呼叫
• 資安核心：security invoker（預設、受 RLS 約束）vs security definer（高權限、繞過 RLS）；definer 必配 set search_path = '' 防 schema 注入
• Trigger 是什麼：時機（before／after）× 事件（insert／update／delete）的矩陣，搭配 new／old 特殊變數
• 經典模式：before update 自動填 updated_at、after insert 的 handle_new_user 建 profile、after update 審計日誌
• 常見坑：definer 沒設 search_path、濫用 definer、after 觸發器造成無限迴圈、副作用失控難追

你現在能讓資料庫在資料變更的瞬間自動運轉，也能把商業邏輯封裝成可呼叫的函式。但當應用規模長大、連線數暴增——尤其在 Serverless 環境每個請求都想開一條新連線——資料庫的連線會成為新的瓶頸。

下一篇《連線管理與 Pooler》，我們會教你 Supabase 的連線池器 Supavisor：Direct Connection、Session Mode、Transaction Mode 三種連線模式各自的埠與適用場景，為什麼 Serverless 一定要走 Transaction Mode，以及 Transaction Mode 不支援 Prepared Statements 該怎麼處理。讓資料庫動起來之後，接著讓它在高流量下也穩得住。下一篇見。