# 自定义函数

URL: https://caijiao.org/postgresql/advanced/functions/custom
Source: docs/postgresql/advanced/functions/custom.md
Description: 介绍如何在 PostgreSQL 中创建和使用自定义函数，帮助开发者扩展数据库功能以满足特定需求。

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

## 创建简单的 SQL 函数

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

### 基本语法

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

### 示例：计算员工工资总和

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

### 调用 SQL 函数

```sql
SELECT total_salary();
```

## PL/pgSQL 自定义函数

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

### 基本语法

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

### 示例：计算员工平均工资

```sql
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 函数

```sql
SELECT avg_salary(1);
```

## 函数的参数

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

### 示例：使用默认参数

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

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

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

## 返回多个值

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

### 示例：返回一个记录集

```sql
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;
```

### 调用返回多个值的函数

```sql
SELECT * FROM get_employees(1);
```

## 函数的调试与日志记录

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

### 示例：调试信息输出

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

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

```sql
SELECT debug_example(100);
```

## 函数的权限管理

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

### 示例：授予权限

```sql
GRANT EXECUTE ON FUNCTION total_salary() TO user_name;
```

## 小结

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