函数

函数是存储在数据库中的可重用代码块，可以接收参数并返回值。PostgreSQL支持多种语言编写函数，包括SQL、PL/pgSQL、Python、Perl等。本章将详细介绍PostgreSQL函数的创建和使用。

函数基础

创建函数的基本语法

sql

-- 创建函数的基本语法
CREATE [OR REPLACE] FUNCTION 函数名(参数列表)
RETURNS 返回类型
AS $$
    函数体
$$ LANGUAGE 语言名;

-- 参数格式：参数名 参数类型
-- 参数模式：IN（默认）、OUT、INOUT、VARIADIC

简单SQL函数

sql

-- 创建一个简单的SQL函数
CREATE OR REPLACE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER
AS $$
    SELECT a + b;
$$ LANGUAGE SQL;

-- 调用函数
SELECT add_numbers(10, 20);

-- 在表达式中使用
SELECT add_numbers(salary, 1000) AS new_salary
FROM employees;

-- 使用默认参数值
CREATE OR REPLACE FUNCTION greet(name TEXT, greeting TEXT DEFAULT '你好')
RETURNS TEXT
AS $$
    SELECT greeting || ', ' || name || '!';
$$ LANGUAGE SQL;

SELECT greet('张三');           -- 使用默认问候语
SELECT greet('李四', '欢迎');   -- 指定问候语

PL/pgSQL函数

PL/pgSQL是PostgreSQL的过程语言，功能更强大，支持变量、条件、循环等控制结构。

基本结构

sql

-- PL/pgSQL函数的基本结构
CREATE OR REPLACE FUNCTION 函数名(参数列表)
RETURNS 返回类型
AS $$
DECLARE
    -- 变量声明
    变量名 变量类型;
BEGIN
    -- 函数体
    RETURN 返回值;
END;
$$ LANGUAGE plpgsql;

-- 示例：计算阶乘
CREATE OR REPLACE FUNCTION factorial(n INTEGER)
RETURNS BIGINT
AS $$
DECLARE
    result BIGINT := 1;  -- 声明并初始化变量
    i INTEGER;
BEGIN
    -- 检查输入
    IF n < 0 THEN
        RAISE EXCEPTION '阶乘只能计算非负整数';
    END IF;
    
    -- 计算阶乘
    FOR i IN 1..n LOOP
        result := result * i;
    END LOOP;
    
    RETURN result;
END;
$$ LANGUAGE plpgsql;

SELECT factorial(5);  -- 返回120
SELECT factorial(10); -- 返回3628800

变量声明

sql

CREATE OR REPLACE FUNCTION variable_demo()
RETURNS TEXT
AS $$
DECLARE
    -- 基本类型声明
    v_integer INTEGER;
    v_text TEXT;
    v_numeric NUMERIC(10, 2);
    
    -- 带默认值
    v_count INTEGER := 0;
    v_name TEXT DEFAULT '未知';
    
    -- 使用表字段类型
    v_salary employees.salary%TYPE;
    
    -- 使用行类型
    v_employee employees%ROWTYPE;
    
    -- 记录类型
    v_record RECORD;
    
    -- 常量
    v_pi CONSTANT NUMERIC := 3.14159;
BEGIN
    -- 赋值
    v_integer := 100;
    v_text := 'Hello';
    
    -- 从查询赋值
    SELECT salary INTO v_salary 
    FROM employees 
    WHERE emp_id = 1;
    
    -- 返回结果
    RETURN '变量演示完成';
END;
$$ LANGUAGE plpgsql;

参数模式

sql

-- IN参数：输入参数（默认）
-- OUT参数：输出参数
-- INOUT参数：既是输入也是输出

-- 使用OUT参数返回多个值
CREATE OR REPLACE FUNCTION get_employee_info(
    emp_id INTEGER,
    OUT emp_name TEXT,
    OUT salary NUMERIC,
    OUT dept_name TEXT
)
AS $$
BEGIN
    SELECT e.emp_name, e.salary, d.dept_name
    INTO emp_name, salary, dept_name
    FROM employees e
    LEFT JOIN departments d ON e.dept_id = d.dept_id
    WHERE e.emp_id = emp_id;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM get_employee_info(1);

-- 使用INOUT参数
CREATE OR REPLACE FUNCTION double_value(INOUT value INTEGER)
AS $$
BEGIN
    value := value * 2;
END;
$$ LANGUAGE plpgsql;

SELECT double_value(10);  -- 返回20

-- 使用VARIADIC参数（可变参数）
CREATE OR REPLACE FUNCTION sum_numbers(VARIADIC nums NUMERIC[])
RETURNS NUMERIC
AS $$
DECLARE
    total NUMERIC := 0;
    num NUMERIC;
BEGIN
    FOREACH num IN ARRAY nums LOOP
        total := total + num;
    END LOOP;
    RETURN total;
END;
$$ LANGUAGE plpgsql;

SELECT sum_numbers(1, 2, 3, 4, 5);      -- 返回15
SELECT sum_numbers(10, 20, 30);         -- 返回60
SELECT sum_numbers(VARIADIC ARRAY[1,2,3,4,5]);  -- 使用数组调用

条件语句

sql

CREATE OR REPLACE FUNCTION get_salary_level(salary NUMERIC)
RETURNS TEXT
AS $$
BEGIN
    -- IF-THEN-ELSE
    IF salary >= 20000 THEN
        RETURN '高薪';
    ELSIF salary >= 15000 THEN
        RETURN '中薪';
    ELSIF salary >= 10000 THEN
        RETURN '低薪';
    ELSE
        RETURN '待定';
    END IF;
END;
$$ LANGUAGE plpgsql;

-- CASE语句
CREATE OR REPLACE FUNCTION get_dept_name(dept_id INTEGER)
RETURNS TEXT
AS $$
BEGIN
    CASE dept_id
        WHEN 1 THEN RETURN '技术部';
        WHEN 2 THEN RETURN '销售部';
        WHEN 3 THEN RETURN '人事部';
        ELSE RETURN '未知部门';
    END CASE;
END;
$$ LANGUAGE plpgsql;

-- 搜索CASE
CREATE OR REPLACE FUNCTION classify_employee(
    salary NUMERIC,
    years_of_service INTEGER
)
RETURNS TEXT
AS $$
BEGIN
    CASE
        WHEN salary >= 20000 AND years_of_service >= 5 THEN
            RETURN '资深员工';
        WHEN salary >= 15000 OR years_of_service >= 3 THEN
            RETURN '中级员工';
        ELSE
            RETURN '初级员工';
    END CASE;
END;
$$ LANGUAGE plpgsql;

循环语句

sql

-- LOOP循环
CREATE OR REPLACE FUNCTION sum_to_n(n INTEGER)
RETURNS INTEGER
AS $$
DECLARE
    i INTEGER := 1;
    total INTEGER := 0;
BEGIN
    LOOP
        total := total + i;
        i := i + 1;
        
        -- 退出条件
        EXIT WHEN i > n;
    END LOOP;
    
    RETURN total;
END;
$$ LANGUAGE plpgsql;

-- WHILE循环
CREATE OR REPLACE FUNCTION while_demo(limit_val INTEGER)
RETURNS INTEGER
AS $$
DECLARE
    i INTEGER := 0;
    total INTEGER := 0;
BEGIN
    WHILE i <= limit_val LOOP
        total := total + i;
        i := i + 1;
    END LOOP;
    
    RETURN total;
END;
$$ LANGUAGE plpgsql;

-- FOR循环（整数范围）
CREATE OR REPLACE FUNCTION for_demo(start_val INTEGER, end_val INTEGER)
RETURNS INTEGER
AS $$
DECLARE
    total INTEGER := 0;
BEGIN
    FOR i IN start_val..end_val LOOP
        total := total + i;
    END LOOP;
    
    RETURN total;
END;
$$ LANGUAGE plpgsql;

-- FOR循环（反向）
CREATE OR REPLACE FUNCTION for_reverse_demo(n INTEGER)
RETURNS INTEGER
AS $$
DECLARE
    total INTEGER := 0;
BEGIN
    FOR i IN REVERSE n..1 LOOP
        total := total + i;
    END LOOP;
    
    RETURN total;
END;
$$ LANGUAGE plpgsql;

-- FOR循环（查询结果）
CREATE OR REPLACE FUNCTION raise_salary_by_dept(dept_id_param INTEGER, raise_percent NUMERIC)
RETURNS INTEGER
AS $$
DECLARE
    emp RECORD;
    count INTEGER := 0;
BEGIN
    FOR emp IN 
        SELECT emp_id, emp_name, salary 
        FROM employees 
        WHERE dept_id = dept_id_param
    LOOP
        UPDATE employees 
        SET salary = salary * (1 + raise_percent / 100)
        WHERE emp_id = emp.emp_id;
        
        count := count + 1;
        
        RAISE NOTICE '已为 % 加薪，原薪资: %', emp.emp_name, emp.salary;
    END LOOP;
    
    RETURN count;
END;
$$ LANGUAGE plpgsql;

-- FOREACH循环（数组）
CREATE OR REPLACE FUNCTION sum_array(arr INTEGER[])
RETURNS INTEGER
AS $$
DECLARE
    total INTEGER := 0;
    element INTEGER;
BEGIN
    FOREACH element IN ARRAY arr LOOP
        total := total + element;
    END LOOP;
    
    RETURN total;
END;
$$ LANGUAGE plpgsql;

SELECT sum_array(ARRAY[1, 2, 3, 4, 5]);

-- CONTINUE跳过当前迭代
CREATE OR REPLACE FUNCTION sum_even_numbers(n INTEGER)
RETURNS INTEGER
AS $$
DECLARE
    total INTEGER := 0;
BEGIN
    FOR i IN 1..n LOOP
        -- 跳过奇数
        IF i % 2 != 0 THEN
            CONTINUE;
        END IF;
        
        total := total + i;
    END LOOP;
    
    RETURN total;
END;
$$ LANGUAGE plpgsql;

异常处理

sql

CREATE OR REPLACE FUNCTION safe_divide(a NUMERIC, b NUMERIC)
RETURNS NUMERIC
AS $$
DECLARE
    result NUMERIC;
BEGIN
    result := a / b;
    RETURN result;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE '除数不能为零，返回NULL';
        RETURN NULL;
    WHEN OTHERS THEN
        RAISE NOTICE '发生错误: %', SQLERRM;
        RETURN NULL;
END;
$$ LANGUAGE plpgsql;

SELECT safe_divide(10, 2);   -- 返回5
SELECT safe_divide(10, 0);   -- 返回NULL并显示通知

-- 自定义异常
CREATE OR REPLACE FUNCTION check_age(age INTEGER)
RETURNS TEXT
AS $$
BEGIN
    IF age < 0 THEN
        RAISE EXCEPTION '年龄不能为负数';
    ELSIF age > 150 THEN
        RAISE EXCEPTION '年龄不能超过150岁';
    END IF;
    
    RETURN '年龄有效';
END;
$$ LANGUAGE plpgsql;

-- 使用SQLSTATE
CREATE OR REPLACE FUNCTION raise_custom_error()
RETURNS VOID
AS $$
BEGIN
    RAISE EXCEPTION '自定义错误' USING ERRCODE = 'P0001';
END;
$$ LANGUAGE plpgsql;

返回集合的函数

RETURNS SETOF

sql

-- 返回多行数据
CREATE OR REPLACE FUNCTION get_employees_by_dept(dept_id_param INTEGER)
RETURNS SETOF employees
AS $$
BEGIN
    RETURN QUERY
    SELECT * FROM employees WHERE dept_id = dept_id_param;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM get_employees_by_dept(1);

-- 返回自定义类型
CREATE OR REPLACE FUNCTION get_high_salary_employees(min_salary NUMERIC)
RETURNS TABLE (
    emp_name TEXT,
    salary NUMERIC,
    dept_name TEXT
)
AS $$
BEGIN
    RETURN QUERY
    SELECT e.emp_name, e.salary, d.dept_name
    FROM employees e
    JOIN departments d ON e.dept_id = d.dept_id
    WHERE e.salary >= min_salary
    ORDER BY e.salary DESC;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM get_high_salary_employees(15000);

-- 使用RETURN NEXT逐行返回
CREATE OR REPLACE FUNCTION generate_series_custom(
    start_val INTEGER,
    end_val INTEGER,
    step_val INTEGER DEFAULT 1
)
RETURNS SETOF INTEGER
AS $$
DECLARE
    i INTEGER;
BEGIN
    i := start_val;
    
    WHILE i <= end_val LOOP
        RETURN NEXT i;  -- 添加到结果集
        i := i + step_val;
    END LOOP;
    
    RETURN;  -- 结束函数
END;
$$ LANGUAGE plpgsql;

SELECT * FROM generate_series_custom(1, 10, 2);

函数重载

PostgreSQL支持函数重载，即同名函数可以有不同的参数列表：

sql

-- 同名函数，不同参数类型
CREATE OR REPLACE FUNCTION format_value(val INTEGER)
RETURNS TEXT
AS $$
BEGIN
    RETURN '整数: ' || val::TEXT;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION format_value(val TEXT)
RETURNS TEXT
AS $$
BEGIN
    RETURN '文本: ' || val;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION format_value(val NUMERIC)
RETURNS TEXT
AS $$
BEGIN
    RETURN '数值: ' || ROUND(val, 2)::TEXT;
END;
$$ LANGUAGE plpgsql;

-- 根据参数类型自动选择函数
SELECT format_value(100);        -- 整数: 100
SELECT format_value('hello');    -- 文本: hello
SELECT format_value(123.456);    -- 数值: 123.46

聚合函数

可以创建自定义聚合函数：

sql

-- 创建聚合函数需要定义状态转换函数和最终函数

-- 状态转换函数：累积每个值
CREATE OR REPLACE FUNCTION sum_squares_state(state NUMERIC, value NUMERIC)
RETURNS NUMERIC
AS $$
BEGIN
    RETURN state + value * value;
END;
$$ LANGUAGE plpgsql;

-- 最终函数：计算结果
CREATE OR REPLACE FUNCTION sum_squares_final(state NUMERIC)
RETURNS NUMERIC
AS $$
BEGIN
    RETURN state;
END;
$$ LANGUAGE plpgsql;

-- 创建聚合函数
CREATE AGGREGATE sum_squares(NUMERIC) (
    SFUNC = sum_squares_state,
    STYPE = NUMERIC,
    INITCOND = '0',
    FINALFUNC = sum_squares_final
);

-- 使用自定义聚合函数
SELECT sum_squares(salary) FROM employees;

-- 创建统计中位数的聚合函数
CREATE OR REPLACE FUNCTION median_state(state NUMERIC[], value NUMERIC)
RETURNS NUMERIC[]
AS $$
BEGIN
    IF state IS NULL THEN
        RETURN ARRAY[value];
    ELSE
        RETURN state || value;
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION median_final(state NUMERIC[])
RETURNS NUMERIC
AS $$
DECLARE
    len INTEGER;
    mid INTEGER;
BEGIN
    IF state IS NULL OR array_length(state, 1) = 0 THEN
        RETURN NULL;
    END IF;
    
    len := array_length(state, 1);
    mid := len / 2 + 1;
    
    IF len % 2 = 1 THEN
        RETURN state[mid];
    ELSE
        RETURN (state[mid - 1] + state[mid]) / 2;
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE AGGREGATE median(NUMERIC) (
    SFUNC = median_state,
    STYPE = NUMERIC[],
    FINALFUNC = median_final
);

函数权限与安全

sql

-- 函数权限管理
GRANT EXECUTE ON FUNCTION add_numbers(INTEGER, INTEGER) TO PUBLIC;
REVOKE EXECUTE ON FUNCTION add_numbers(INTEGER, INTEGER) FROM some_user;

-- 函数安全定义者（以函数所有者权限执行）
CREATE OR REPLACE FUNCTION get_all_salaries()
RETURNS TABLE(emp_name TEXT, salary NUMERIC)
SECURITY DEFINER  -- 以函数所有者权限执行
AS $$
BEGIN
    RETURN QUERY
    SELECT emp_name::TEXT, salary FROM employees;
END;
$$ LANGUAGE plpgsql;

-- 函数安全调用者（以调用者权限执行，默认）
CREATE OR REPLACE FUNCTION get_my_salaries()
RETURNS TABLE(emp_name TEXT, salary NUMERIC)
SECURITY INVOKER  -- 以调用者权限执行
AS $$
BEGIN
    RETURN QUERY
    SELECT emp_name::TEXT, salary FROM employees;
END;
$$ LANGUAGE plpgsql;

函数管理

查看函数信息

sql

-- 查看所有函数
\df

-- 查看函数定义
\df+ function_name

-- 使用SQL查询函数信息
SELECT 
    proname AS 函数名,
    pg_get_function_arguments(oid) AS 参数,
    pg_get_function_result(oid) AS 返回类型,
    prolang AS 语言ID
FROM pg_proc
WHERE pronamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public')
ORDER BY proname;

-- 查看函数源代码
SELECT pg_get_functiondef(oid) 
FROM pg_proc 
WHERE proname = 'factorial';

修改和删除函数

sql

-- 修改函数（使用CREATE OR REPLACE）
CREATE OR REPLACE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER
AS $$
    SELECT a + b;
$$ LANGUAGE SQL;

-- 修改函数所有者
ALTER FUNCTION add_numbers(INTEGER, INTEGER) OWNER TO postgres;

-- 重命名函数
ALTER FUNCTION add_numbers(INTEGER, INTEGER) RENAME TO add_integers;

-- 删除函数（需要指定参数类型）
DROP FUNCTION add_numbers(INTEGER, INTEGER);

-- 如果存在则删除
DROP FUNCTION IF EXISTS add_numbers(INTEGER, INTEGER);

内置函数分类

PostgreSQL提供了大量内置函数：

sql

-- 字符串函数
SELECT LENGTH('Hello');                    -- 长度
SELECT UPPER('hello');                     -- 大写
SELECT LOWER('HELLO');                     -- 小写
SELECT SUBSTRING('Hello World', 1, 5);     -- 子串
SELECT CONCAT('Hello', ' ', 'World');      -- 连接
SELECT REPLACE('Hello', 'l', 'L');         -- 替换
SELECT TRIM('  Hello  ');                  -- 去空格
SELECT SPLIT_PART('a,b,c', ',', 2);        -- 分割取部分

-- 数学函数
SELECT ABS(-10);                           -- 绝对值
SELECT ROUND(3.14159, 2);                  -- 四舍五入
SELECT FLOOR(3.9);                         -- 向下取整
SELECT CEIL(3.1);                          -- 向上取整
SELECT POWER(2, 10);                       -- 幂运算
SELECT SQRT(16);                           -- 平方根
SELECT RANDOM();                           -- 随机数

-- 日期函数
SELECT CURRENT_DATE;                       -- 当前日期
SELECT CURRENT_TIME;                       -- 当前时间
SELECT CURRENT_TIMESTAMP;                  -- 当前时间戳
SELECT NOW();                              -- 当前时间
SELECT AGE('2024-01-01', '2023-01-01');    -- 时间差
SELECT DATE_PART('year', NOW());           -- 提取年份
SELECT DATE_TRUNC('month', NOW());         -- 截断到月初

-- 类型转换函数
SELECT CAST('123' AS INTEGER);             -- 类型转换
SELECT '123'::INTEGER;                     -- 类型转换简写
SELECT TO_CHAR(12345.67, 'FM99,999.99');   -- 数字转字符串
SELECT TO_DATE('2024-01-15', 'YYYY-MM-DD'); -- 字符串转日期

-- 条件函数
SELECT COALESCE(NULL, NULL, 'default');    -- 返回第一个非NULL值
SELECT NULLIF(10, 10);                     -- 相等返回NULL
SELECT GREATEST(1, 5, 3, 2);               -- 最大值
SELECT LEAST(1, 5, 3, 2);                  -- 最小值

-- 数组函数
SELECT ARRAY_LENGTH(ARRAY[1,2,3], 1);      -- 数组长度
SELECT ARRAY_APPEND(ARRAY[1,2], 3);        -- 追加元素
SELECT ARRAY_PREPEND(0, ARRAY[1,2]);       -- 前置元素
SELECT UNNEST(ARRAY[1,2,3]);               -- 展开数组

-- JSON函数
SELECT '{"a":1,"b":2}'::JSONB -> 'a';      -- 获取JSON字段
SELECT '{"a":[1,2,3]}'::JSONB -> 'a' -> 0; -- 获取嵌套值
SELECT JSONB_BUILD_OBJECT('name', '张三', 'age', 25); -- 构建JSON

小结

本章介绍了PostgreSQL函数的主要内容：

内容	说明
SQL函数	简单函数，使用SQL语言
PL/pgSQL函数	功能强大的过程语言函数
参数模式	IN、OUT、INOUT、VARIADIC
控制结构	条件、循环、异常处理
集合返回函数	RETURNS SETOF、RETURN QUERY
函数重载	同名函数不同参数
自定义聚合函数	创建自定义聚合操作

下一章我们将学习存储过程的使用。

函数 ​

函数基础 ​

创建函数的基本语法 ​

简单SQL函数 ​

PL/pgSQL函数 ​

基本结构 ​

变量声明 ​

参数模式 ​

条件语句 ​

循环语句 ​

异常处理 ​

返回集合的函数 ​

RETURNS SETOF ​

函数重载 ​

聚合函数 ​

函数权限与安全 ​

函数管理 ​

查看函数信息 ​

修改和删除函数 ​

内置函数分类 ​

小结 ​

函数