PostgreSQL 函数与存储过程

全面学习PostgreSQL函数：内置函数、自定义PL/pgSQL函数、存储过程、触发器函数 · 难度：高级 · +20XP

PostgreSQL 函数概述

PostgreSQL拥有强大的函数系统，支持多种语言编写（SQL、PL/pgSQL、Python、JavaScript等）。函数可以封装业务逻辑，在数据库层面执行。

内置函数

-- 字符串函数
SELECT upper('hello'), lower('WORLD');       -- HELLO, world
SELECT length('数据库');                       -- 3（字符数）
SELECT regexp_replace('abc123', '\d+', 'X'); -- abcX

-- 时间函数
SELECT now(), current_date, current_time;
SELECT age('2024-03-01', '2020-01-01');       -- 4 years 2 mons
SELECT date_trunc('month', now());            -- 月初

-- 类型转换
SELECT 123::text;                              -- 转字符串
SELECT '2024-01-15'::date;                    -- 转日期
SELECT row_to_json(row) FROM table;

自定义函数（PL/pgSQL）

-- 基础函数：返回单个值
CREATE OR REPLACE FUNCTION full_name(first TEXT, last TEXT)
RETURNS TEXT AS $$
BEGIN
    RETURN first || ' ' || last;
END;
$$ LANGUAGE plpgsql;

-- 使用
SELECT full_name('张', '三');  -- 张 三

-- 带条件判断的函数
CREATE OR REPLACE FUNCTION get_grade(score NUMERIC)
RETURNS TEXT AS $$
BEGIN
    IF score >= 90 THEN RETURN 'A';
    ELSIF score >= 80 THEN RETURN 'B';
    ELSIF score >= 60 THEN RETURN 'C';
    ELSE RETURN 'D';
    END IF;
END;
$$ LANGUAGE plpgsql;

表函数（返回多行）

CREATE OR REPLACE FUNCTION top_products(limit_n INT)
RETURNS TABLE(name TEXT, sales INT) AS $$
BEGIN
    RETURN QUERY
    SELECT p.name, p.sales
    FROM products p
    ORDER BY p.sales DESC
    LIMIT limit_n;
END;
$$ LANGUAGE plpgsql;

-- 像查询表一样使用
SELECT * FROM top_products(5);

触发器函数

CREATE OR REPLACE FUNCTION update_modified_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.modified_at = now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER auto_update_timestamp
    BEFORE UPDATE ON products
    FOR EACH ROW EXECUTE FUNCTION update_modified_at();

💡 练习任务

创建一个函数：根据购买金额和会员等级计算折扣价，VIP会员9折、普通会员95折、非会员原价。