自定义函数

PostgreSQL 允许用户通过创建自定义函数来扩展数据库的功能。自定义函数可以是用 SQL、PL/pgSQL 或其他编程语言编写的。

创建简单的 SQL 函数

SQL 函数通过 CREATE FUNCTION 语句创建，支持 SQL 语句的封装和重复使用。

基本语法

CREATE FUNCTION function_name(parameters)
RETURNS return_type AS
$$
  function_body
$$
LANGUAGE sql;

示例：计算员工工资总和

CREATE FUNCTION total_salary()
RETURNS numeric AS
$$
  SELECT SUM(salary) FROM employees;
$$
LANGUAGE sql;

调用 SQL 函数

SELECT total_salary();

PL/pgSQL 自定义函数

PL/pgSQL 是 PostgreSQL 的过程化编程语言，支持更复杂的逻辑和控制结构。

基本语法

CREATE FUNCTION function_name(parameters)
RETURNS return_type AS
$$
  DECLARE
    -- 变量声明
  BEGIN
    -- 函数体
  END;
$$
LANGUAGE plpgsql;

示例：计算员工平均工资

CREATE FUNCTION avg_salary(department_id INT)
RETURNS numeric AS
$$
DECLARE
  avg_salary numeric;
BEGIN
  SELECT AVG(salary) INTO avg_salary
  FROM employees
  WHERE department_id = department_id;
  
  RETURN avg_salary;
END;
$$
LANGUAGE plpgsql;

调用 PL/pgSQL 函数

SELECT avg_salary(1);

函数的参数

PostgreSQL 支持输入参数、返回值和可选的默认值。函数参数可以是标量类型、复合类型或数组。

示例：使用默认参数

CREATE FUNCTION greet(name TEXT DEFAULT 'Guest')
RETURNS TEXT AS
$$
BEGIN
  RETURN 'Hello, ' || name || '!';
END;
$$
LANGUAGE plpgsql;

调用该函数时，未提供参数则使用默认值：

SELECT greet(); -- 输出 'Hello, Guest!'
SELECT greet('Alice'); -- 输出 'Hello, Alice!'

返回多个值

函数可以返回多个值，这可以通过使用 SETOF 或复合类型来实现。

示例：返回一个记录集

CREATE FUNCTION get_employees(department_id INT)
RETURNS SETOF employees AS
$$
BEGIN
  RETURN QUERY
  SELECT * FROM employees WHERE department_id = department_id;
END;
$$
LANGUAGE plpgsql;

调用返回多个值的函数

SELECT * FROM get_employees(1);

函数的调试与日志记录

可以使用 RAISE NOTICE 语句在 PL/pgSQL 函数中输出调试信息。

示例：调试信息输出

CREATE FUNCTION debug_example(value INT)
RETURNS VOID AS
$$
BEGIN
  RAISE NOTICE 'Input value: %', value;
END;
$$
LANGUAGE plpgsql;

调用函数时，会输出调试信息：

SELECT debug_example(100);

函数的权限管理

默认情况下，只有创建者或超级用户可以执行函数。可以通过 GRANT 语句授予其他用户执行权限。

示例：授予权限

GRANT EXECUTE ON FUNCTION total_salary() TO user_name;

小结

自定义函数在 PostgreSQL 中提供了强大的扩展能力，能够帮助开发者在数据库内部执行复杂的逻辑和计算。通过 SQL 和 PL/pgSQL 编写函数，可以简化查询、提高效率，并且通过灵活的参数和返回值机制，能够满足多样化的需求。