PostgreSQL教程

函数概述

什么是函数

函数是PostgreSQL中预定义或用户自定义的可重用代码块，用于执行特定任务并返回结果。函数可以：

• 执行计算：对输入参数进行计算并返回结果
• 数据处理：处理和转换数据
• 业务逻辑：实现复杂的业务规则
• 代码重用：避免重复编写相同逻辑

函数类型

PostgreSQL支持多种类型的函数：

• 内置函数：系统预定义的函数，如数学函数、字符串函数等
• 自定义函数：用户创建的函数，使用PL/pgSQL或其他过程语言
• 聚合函数：对一组值进行计算并返回单个结果，如SUM、COUNT等
• 窗口函数：在结果集的行窗口上执行计算

内置函数分类

数学函数

用于执行数学计算：

SELECT 
    ABS(-10) AS absolute_value,
    SQRT(16) AS square_root,
    POWER(2, 3) AS power_result,
    ROUND(3.14159, 2) AS rounded_value,
    CEIL(3.14) AS ceiling_value,
    FLOOR(3.14) AS floor_value;

字符串函数

用于处理和操作字符串：

SELECT 
    LENGTH('Hello World') AS string_length,
    UPPER('hello') AS uppercase,
    LOWER('WORLD') AS lowercase,
    SUBSTRING('Hello World', 1, 5) AS substring_result,
    CONCAT('Hello', ' ', 'World') AS concatenated;

日期时间函数

用于处理日期和时间：

SELECT 
    CURRENT_DATE AS today,
    CURRENT_TIME AS current_time,
    NOW() AS current_timestamp,
    DATE_PART('year', CURRENT_DATE) AS current_year,
    AGE('1990-01-01'::DATE) AS age;

条件函数

根据条件返回不同结果：

SELECT 
    COALESCE(NULL, 'Default Value') AS coalesce_result,
    NULLIF(10, 10) AS nullif_result,
    GREATEST(10, 20, 30) AS greatest_value,
    LEAST(10, 20, 30) AS least_value;

自定义函数

创建简单函数

-- 创建返回两个数之和的函数
CREATE OR REPLACE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER AS $$
BEGIN
    RETURN a + b;
END;
$$ LANGUAGE plpgsql;

-- 调用函数
SELECT add_numbers(5, 3); -- 返回 8

创建带默认参数的函数

-- 创建带默认参数的函数
CREATE OR REPLACE FUNCTION calculate_discount(
    price NUMERIC(10,2), 
    discount_percent NUMERIC(5,2) DEFAULT 10.00
)
RETURNS NUMERIC(10,2) AS $$
BEGIN
    RETURN price * (1 - discount_percent / 100);
END;
$$ LANGUAGE plpgsql;

-- 调用函数
SELECT calculate_discount(100.00); -- 使用默认折扣10%，返回 90.00
SELECT calculate_discount(100.00, 20.00); -- 指定折扣20%，返回 80.00

创建返回表的函数

-- 创建返回表的函数
CREATE OR REPLACE FUNCTION get_active_users()
RETURNS TABLE(
    user_id INTEGER,
    username VARCHAR(50),
    email VARCHAR(255)
) AS $$
BEGIN
    RETURN QUERY
    SELECT id, username, email
    FROM users
    WHERE is_active = true;
END;
$$ LANGUAGE plpgsql;

-- 调用函数
SELECT * FROM get_active_users();

函数管理

查看函数

-- 查看所有函数
SELECT 
    proname AS function_name,
    pg_get_function_arguments(p.oid) AS arguments,
    pg_get_function_result(p.oid) AS return_type
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE n.nspname = 'public';

-- 查看特定函数的定义
SELECT pg_get_functiondef('add_numbers'::regproc);

修改函数

-- 使用CREATE OR REPLACE修改函数
CREATE OR REPLACE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER AS $$
BEGIN
    -- 添加日志记录
    RAISE NOTICE 'Adding % and %', a, b;
    RETURN a + b;
END;
$$ LANGUAGE plpgsql;

删除函数

-- 删除函数
DROP FUNCTION IF EXISTS add_numbers(INTEGER, INTEGER);

-- 删除重载函数
DROP FUNCTION IF EXISTS add_numbers(INTEGER, INTEGER, INTEGER);

函数最佳实践

命名规范

• 描述性命名：函数名应清楚描述其功能
• 一致性：在项目中保持命名风格一致
• 避免关键字：不要使用SQL关键字作为函数名

性能考虑

• 避免复杂逻辑：函数中避免过于复杂的业务逻辑
• 合理使用：在大数据集上谨慎使用函数
• 索引友好：确保函数使用不会阻止索引的使用

安全性

• 输入验证：对所有输入参数进行验证
• 权限控制：为函数设置适当的执行权限
• SQL注入防护：使用参数化查询

-- 创建计算订单总金额的函数
CREATE OR REPLACE FUNCTION calculate_order_total(p_order_id INTEGER)
RETURNS NUMERIC(10,2) AS $$
DECLARE
    total NUMERIC(10,2) := 0;
BEGIN
    SELECT SUM(quantity * price) INTO total
    FROM order_items
    WHERE order_id = p_order_id;
    
    RETURN COALESCE(total, 0);
END;
$$ LANGUAGE plpgsql;

-- 创建获取用户订单统计的函数
CREATE OR REPLACE FUNCTION get_user_order_stats(p_user_id INTEGER)
RETURNS TABLE(
    total_orders BIGINT,
    total_spent NUMERIC(12,2),
    avg_order_value NUMERIC(10,2),
    last_order_date DATE
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        COUNT(*) AS total_orders,
        COALESCE(SUM(total_amount), 0) AS total_spent,
        COALESCE(AVG(total_amount), 0) AS avg_order_value,
        MAX(order_date) AS last_order_date
    FROM orders
    WHERE user_id = p_user_id;
END;
$$ LANGUAGE plpgsql;

-- 使用函数
SELECT * FROM get_user_order_stats(1);