Ubuntu PostgreSQL函数使用教程

一、准备工作：安装PostgreSQL及必要工具

在Ubuntu上使用PostgreSQL函数前，需先安装数据库及命令行工具psql（用于交互式操作）。

更新包列表：
```
sudo apt update
```

安装PostgreSQL及pgAdmin（可选图形工具）：

sudo apt install postgresql postgresql-contrib pgadmin4

启动PostgreSQL服务并设置开机自启：

sudo systemctl start postgresql
sudo systemctl enable postgresql

安装完成后，默认超级用户为postgres，可通过psql -U postgres登录数据库。

二、连接PostgreSQL数据库

使用psql命令行工具连接数据库（以默认postgres数据库为例）：

psql -U postgres -d postgres

输入密码后进入psql交互界面（提示符为postgres=#）。

三、创建自定义函数（以PL/pgSQL为例）

PostgreSQL支持多种函数语言（如PL/pgSQL、Python、Perl），其中PL/pgSQL是默认内置的过程语言，适合编写复杂业务逻辑。

1. 创建简单函数（无参数、返回文本）

CREATE OR REPLACE FUNCTION hello_world()
RETURNS text AS $$
BEGIN
    RETURN 'Hello, World!';
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE：若函数已存在则替换，避免报错；
RETURNS text：指定返回类型为文本；
AS $$...$$：用$$包裹函数体（也可用 $function_name$ ）；
LANGUAGE plpgsql：声明函数语言为PL/pgSQL。

2. 创建带参数的函数（两数相加）

CREATE OR REPLACE FUNCTION add_numbers(a integer, b integer)
RETURNS integer AS $$
BEGIN
    RETURN a + b;
END;
$$ LANGUAGE plpgsql;

参数格式为(参数名参数类型)，函数体内通过$1、$2引用（如RETURN $1 + $2）。

3. 创建带变量的函数（判断年龄）

CREATE OR REPLACE FUNCTION check_age(age integer)
RETURNS text AS $$
DECLARE
    result text;  -- 声明变量
BEGIN
    IF age < 18 THEN
        result := 'Minor';
    ELSE
        result := 'Adult';
    END IF;
    RETURN result;
END;
$$ LANGUAGE plpgsql;

DECLARE块用于定义局部变量；
使用IF...THEN...ELSE条件语句实现逻辑分支。

4. 创建返回结果集的函数（获取city表所有数据）

CREATE OR REPLACE FUNCTION get_city()
RETURNS SETOF city AS $$  -- SETOF表示返回多行
BEGIN
    RETURN QUERY SELECT * FROM city;  -- 返回查询结果
END;
$$ LANGUAGE plpgsql;

SETOF 表名：返回与表结构一致的多行数据；
RETURN QUERY：执行查询并将结果返回。

四、调用自定义函数

创建函数后，可通过SELECT语句调用：

1. 调用无参数函数

SELECT hello_world();

输出：

hello_world 
-------------
Hello, World!
(1 row)

2. 调用带参数函数

SELECT add_numbers(5, 10);

输出：

 add_numbers 
-------------
          15
(1 row)

3. 调用返回结果集的函数

-- 方式1：直接调用（显示为元组）
SELECT * FROM get_city();

-- 方式2：带条件过滤
SELECT * FROM get_city() WHERE cityId > 2;

输出（方式1）：

 cityid | cityname 
--------+----------
      1 | BeiJing
      2 | NewYork
      3 | Hong kong
      4 | ShaingHai
(4 rows)

输出（方式2）：

 cityid | cityname 
--------+----------
      3 | Hong kong
      4 | ShaingHai
(2 rows)

4. 在复杂查询中使用函数

SELECT id, title, hello_world() AS greeting FROM articles;

该查询会为每篇文章添加一列greeting，值为Hello, World!。

五、函数管理

1. 查看函数信息

通过pg_proc系统表查看函数详情（如名称、参数、返回类型）：

SELECT proname AS function_name, 
       proargtypes AS argument_types, 
       prorettype AS return_type
FROM pg_proc
WHERE proname = 'hello_world';

2. 删除函数

使用DROP FUNCTION命令删除函数（需指定参数列表，若函数无参数则留空）：

DROP FUNCTION IF EXISTS hello_world();  -- IF EXISTS避免函数不存在时报错

六、错误处理与调试

1. 异常处理（除零错误示例）

CREATE OR REPLACE FUNCTION divide_numbers(a integer, b integer)
RETURNS integer AS $$
BEGIN
    RETURN a / b;
EXCEPTION
    WHEN division_by_zero THEN  -- 捕获除零异常
        RETURN NULL;
END;
$$ LANGUAGE plpgsql;

调用divide_numbers(10, 0)将返回NULL而非报错。

2. 调试输出（RAISE语句）

使用RAISE NOTICE输出调试信息（如变量值）：

CREATE OR REPLACE FUNCTION debug_example(a integer)
RETURNS integer AS $$
BEGIN
    RAISE NOTICE 'Input value: %', a;  -- 输出输入值
    RETURN a * 2;
END;
$$ LANGUAGE plpgsql;

调用debug_example(5)时，控制台会显示：

NOTICE:  Input value: 5

七、扩展其他语言函数（以Python为例）

若需用Python编写函数，需先安装对应扩展：

sudo apt install postgresql-plpython3-14  # 根据PostgreSQL版本调整（如16则用16）

激活扩展：

CREATE EXTENSION plpython3;

创建Python函数（返回问候语）：

CREATE OR REPLACE FUNCTION hello_python(name text)
RETURNS text AS $$
    return f"Hello, {name}!"
$$ LANGUAGE plpython3;

调用：

SELECT hello_python('Alice');

输出：

hello_python 
--------------
Hello, Alice!
(1 row)

通过以上步骤，你可在Ubuntu上使用PostgreSQL创建、调用和管理函数，实现业务逻辑的封装与复用。

0 赞

0 踩