存储过程、触发器与扩展

8.1 PL/pgSQL 函数基础

PL/pgSQL 是 PostgreSQL 的过程语言扩展，允许编写包含条件判断、循环、异常处理的复杂逻辑，直接在数据库端执行。

SQL-- 基础函数：计算订单总金额
CREATE OR REPLACE FUNCTION get_order_total(p_order_id BIGINT)
RETURNS NUMERIC AS $$
DECLARE
  v_total NUMERIC;
BEGIN
  SELECT SUM(quantity * unit_price)
  INTO v_total
  FROM order_items
  WHERE order_id = p_order_id;

  RETURN COALESCE(v_total, 0);
END;
$$ LANGUAGE plpgsql STABLE;  -- STABLE: 相同输入相同输出，不修改数据

-- 调用函数
SELECT get_order_total(1001);

-- 返回表的函数（RETURNS TABLE / RETURNS SETOF）
CREATE OR REPLACE FUNCTION get_user_orders(p_user_id BIGINT, p_limit INTEGER DEFAULT 10)
RETURNS TABLE(
  order_id    BIGINT,
  status      TEXT,
  total       NUMERIC,
  item_count  BIGINT,
  created_at  TIMESTAMPTZ
) AS $$
BEGIN
  RETURN QUERY
  SELECT
    o.id,
    o.status,
    o.total,
    COUNT(oi.id),
    o.created_at
  FROM orders o
  LEFT JOIN order_items oi ON o.id = oi.order_id
  WHERE o.user_id = p_user_id
  GROUP BY o.id, o.status, o.total, o.created_at
  ORDER BY o.created_at DESC
  LIMIT p_limit;
END;
$$ LANGUAGE plpgsql STABLE;

-- 使用：像表一样查询
SELECT * FROM get_user_orders(42, 5);

8.2 PL/pgSQL 控制流与异常处理

SQL-- 带异常处理的转账函数
CREATE OR REPLACE FUNCTION transfer_funds(
  p_from_id  BIGINT,
  p_to_id    BIGINT,
  p_amount   NUMERIC
) RETURNS VOID AS $$
DECLARE
  v_from_balance NUMERIC;
BEGIN
  -- 参数校验
  IF p_amount <= 0 THEN
    RAISE EXCEPTION '转账金额必须大于 0，收到：%', p_amount
      USING ERRCODE = '22003';  -- numeric_value_out_of_range
  END IF;

  -- 锁定源账户并检查余额
  SELECT balance INTO STRICT v_from_balance
  FROM accounts
  WHERE id = p_from_id
  FOR UPDATE;

  IF v_from_balance < p_amount THEN
    RAISE EXCEPTION '余额不足：当前 %，需要 %', v_from_balance, p_amount
      USING ERRCODE = 'P0001';  -- raise_exception
  END IF;

  -- 执行转账
  UPDATE accounts SET balance = balance - p_amount WHERE id = p_from_id;
  UPDATE accounts SET balance = balance + p_amount WHERE id = p_to_id;

  RAISE NOTICE '转账成功：从账户 % 转入账户 % 金额 %', p_from_id, p_to_id, p_amount;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE EXCEPTION '账户 % 不存在', p_from_id;
  WHEN OTHERS THEN
    RAISE EXCEPTION '转账失败：% %', SQLERRM, SQLSTATE;
END;
$$ LANGUAGE plpgsql;

-- 调用
SELECT transfer_funds(1, 2, 500.00);

8.3 存储过程（Procedure）

PostgreSQL 11+ 引入了独立的 CREATE PROCEDURE（与函数的区别：过程可以提交/回滚事务，函数不能）。

SQL-- 存储过程可以在内部 COMMIT/ROLLBACK
CREATE OR REPLACE PROCEDURE batch_update_prices(p_category_id INTEGER, p_discount NUMERIC)
LANGUAGE plpgsql AS $$
DECLARE
  v_count INTEGER := 0;
  v_row products%ROWTYPE;
BEGIN
  FOR v_row IN
    SELECT * FROM products WHERE category_id = p_category_id
  LOOP
    UPDATE products
    SET price = price * (1 - p_discount / 100), updated_at = NOW()
    WHERE id = v_row.id;

    v_count := v_count + 1;

    -- 每 100 条提交一次（减少锁持有时间）
    IF v_count % 100 = 0 THEN
      COMMIT;
    END IF;
  END LOOP;

  COMMIT;
  RAISE NOTICE '已更新 % 条商品价格', v_count;
END;
$$;

-- 调用过程（用 CALL，不是 SELECT）
CALL batch_update_prices(1, 10);  -- 分类1的商品打9折

8.4 触发器

触发器在表的 INSERT/UPDATE/DELETE 操作时自动执行函数。PostgreSQL 的触发器函数必须返回 TRIGGER 类型。

SQL-- 最常用的触发器：自动更新 updated_at
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();  -- NEW 是即将写入的新行
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 为 orders 表绑定触发器
CREATE TRIGGER orders_updated_at
  BEFORE UPDATE ON orders   -- BEFORE UPDATE：在实际更新之前执行
  FOR EACH ROW               -- ROW-level：每修改一行触发一次
  EXECUTE FUNCTION update_updated_at();

-- 同样为 products 绑定（一个函数可以被多个触发器使用）
CREATE TRIGGER products_updated_at
  BEFORE UPDATE ON products
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at();

SQL-- AFTER 触发器：审计日志
CREATE TABLE audit_log (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  table_name TEXT NOT NULL,
  operation  TEXT NOT NULL,
  old_data   JSONB,
  new_data   JSONB,
  changed_by TEXT NOT NULL DEFAULT current_user,
  changed_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION audit_changes()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_log (table_name, operation, old_data, new_data)
  VALUES (
    TG_TABLE_NAME,    -- 内置变量：触发器所在表名
    TG_OP,            -- 内置变量：操作类型 INSERT/UPDATE/DELETE
    CASE WHEN TG_OP = 'INSERT' THEN NULL ELSE row_to_json(OLD)::JSONB END,
    CASE WHEN TG_OP = 'DELETE' THEN NULL ELSE row_to_json(NEW)::JSONB END
  );
  RETURN NULL;  -- AFTER 触发器返回值被忽略，返回 NULL 即可
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER orders_audit
  AFTER INSERT OR UPDATE OR DELETE ON orders
  FOR EACH ROW
  EXECUTE FUNCTION audit_changes();

8.5 扩展系统

PostgreSQL 的扩展系统（CREATE EXTENSION）是其最大优势之一，可以无缝添加新的数据类型、函数、操作符、索引方法。

SQL-- 查看已安装的扩展
SELECT name, default_version, installed_version, comment
FROM pg_available_extensions
WHERE installed_version IS NOT NULL
ORDER BY name;

-- 查看可安装（未安装）的扩展
SELECT name, default_version, comment
FROM pg_available_extensions
WHERE installed_version IS NULL
ORDER BY name;

常用扩展速览

SQL-- 1. uuid-ossp：UUID 生成函数（PG 13+ 用内置 gen_random_uuid 即可）
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
SELECT uuid_generate_v4();    -- v4: 随机 UUID
SELECT uuid_generate_v1();    -- v1: 基于时间+MAC 地址

-- 2. pgcrypto：加密函数
CREATE EXTENSION IF NOT EXISTS pgcrypto;
SELECT crypt('mypassword', gen_salt('bf'));   -- bcrypt 哈希
SELECT crypt('input', stored_hash) = stored_hash; -- 验证密码

-- 3. pg_trgm：三元组相似度搜索（模糊搜索）
CREATE EXTENSION IF NOT EXISTS pg_trgm;
SELECT similarity('PostgreSQL', 'PostgresQL');    -- 相似度 0~1

-- 为相似度搜索建 GiST 索引
CREATE INDEX idx_products_name_trgm ON products
  USING GIN (name gin_trgm_ops);

-- 模糊搜索（即使拼写有错误也能找到）
SELECT name, similarity(name, 'Makcbook') AS sim
FROM products
WHERE name % 'Makcbook'   -- % 操作符：相似度 > 0.3（阈值可配置）
ORDER BY sim DESC;

-- 4. PostGIS：地理空间扩展
CREATE EXTENSION IF NOT EXISTS postgis;
ALTER TABLE stores ADD COLUMN location GEOMETRY(Point, 4326);

-- 插入地理坐标（经度, 纬度）
UPDATE stores
SET location = ST_SetSRID(ST_MakePoint(116.4074, 39.9042), 4326)
WHERE name = '北京旗舰店';

-- 查找 5 公里内的门店
SELECT name,
  ST_Distance(location::geography, ST_MakePoint(116.40, 39.90)::geography) / 1000 AS km
FROM stores
WHERE ST_DWithin(location::geography, ST_MakePoint(116.40, 39.90)::geography, 5000)
ORDER BY km;

-- 5. pg_stat_statements（性能监控，需 shared_preload_libraries）
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 6. timescaledb：时序数据库（需单独安装）
-- 将普通表转换为超表（Hypertable），自动按时间分区
CREATE EXTENSION IF NOT EXISTS timescaledb;

CREATE TABLE metrics (
  time        TIMESTAMPTZ NOT NULL,
  device_id   TEXT NOT NULL,
  temperature NUMERIC,
  humidity    NUMERIC
);

-- 转换为超表，按时间自动分区（每 7 天一个 chunk）
SELECT create_hypertable('metrics', 'time', chunk_time_interval => INTERVAL '7 days');

-- 时序聚合查询
SELECT
  time_bucket('1 hour', time) AS bucket,
  device_id,
  AVG(temperature) AS avg_temp
FROM metrics
GROUP BY bucket, device_id
ORDER BY bucket DESC;