当前位置：首页 > 虚拟主机 > 正文

pg数据库创建函数，如何定义参数和返回值类型？

admin
虚拟主机
2025-12-20
4

在PostgreSQL（简称PG）数据库中，创建函数是一种常见的数据库操作，通过函数可以将复杂的SQL逻辑封装起来，提高代码复用性和可维护性，PG数据库支持多种编程语言创建函数，如PL/pgSQL、PL/Python、PL/Perl等，其中PL/pgSQL是默认且最常用的过程语言，下面将详细介绍PG数据库中创建函数的语法、参数、返回值、语言选择、异常处理等核心内容,并结合实例说明具体应用。

创建函数的基本语法

创建函数的基本语法结构如下：

CREATE OR REPLACE FUNCTION function_name (parameter_name parameter_type [, ...]) RETURNS return_type LANGUAGE language_name AS $$ DECLARE 声明变量 variable_name variable_type; BEGIN 函数逻辑 RETURN expression; EXCEPTION 异常处理 WHEN exception_condition THEN RETURN error_value; END; $$;

CREATE OR REPLACE表示创建新函数或替换已存在的函数；function_name为函数名；parameter_name parameter_type为参数列表，支持输入参数（默认）、输出参数（OUT）或输入输出参数（INOUT）；RETURN子句指定返回值类型；LANGUAGE指定函数实现语言（如plpgsql）；AS $$ ... $$之间是函数体代码。

参数类型与使用

函数参数可分为三类：

输入参数（IN）：默认类型，用于向函数传递值,函数内部可读取但不能修改。
输出参数（OUT）：用于返回值，函数内部需赋值,调用时可获取。
输入输出参数（INOUT）：兼具输入和输出功能,函数可修改其值并返回。

示例：创建一个含输入和输出参数的函数,计算两数之和与差：

pg数据库创建函数，如何定义参数和返回值类型？第1张

调用方式：SELECT * FROM calculate(10, 5);，返回sum=15, diff=5。

返回值处理

函数可通过RETURN语句返回值，返回值类型需与RETURNS子句声明一致，若返回多个值，可使用OUT参数或返回复合类型（如记录、表）,返回表记录的函数：

CREATE OR REPLACE FUNCTION get_users() RETURNS TABLE(id INT, name TEXT) LANGUAGE plpgsql AS $$ BEGIN RETURN QUERY SELECT id, name FROM users WHERE status = 'active'; END; $$;

调用方式：SELECT * FROM get_users();,直接返回符合条件的用户表。

语言选择与扩展

PG支持多种过程语言，默认为plpgsql，需通过CREATE EXTENSION加载其他语言（如plpython3u），示例：使用Python创建函数：

CREATE EXTENSION IF NOT EXISTS plpython3u; CREATE OR REPLACE FUNCTION python_example(x INT) RETURNS INT LANGUAGE plpython3u AS $$ return x * 2 $$;

调用：SELECT python_example(5);，返回10。

异常处理

函数体可通过EXCEPTION块捕获和处理运行时错误，示例：处理除零异常：

CREATE OR REPLACE FUNCTION safe_divide(a INT, b INT) RETURNS NUMERIC LANGUAGE plpgsql AS $$ BEGIN RETURN a::NUMERIC / b; EXCEPTION WHEN division_by_zero THEN RETURN NULL; END; $$;

调用：SELECT safe_divide(10, 0);，返回NULL而非报错。

pg数据库创建函数，如何定义参数和返回值类型？第2张

实用示例：分页查询函数

以下是一个带分页参数的查询函数,支持排序和条件过滤：

CREATE OR REPLACE FUNCTION get_paginated_data( page INT DEFAULT 1, page_size INT DEFAULT 10, sort_column TEXT DEFAULT 'id', sort_order TEXT DEFAULT 'ASC' ) RETURNS TABLE(id INT, name TEXT) AS $$ DECLARE offset_val INT := (page 1) * page_size; valid_columns TEXT[] := ARRAY['id', 'name', 'created_at']; valid_orders TEXT[] := ARRAY['ASC', 'DESC']; BEGIN 参数校验 IF NOT (sort_column = ANY(valid_columns)) THEN RAISE EXCEPTION 'Invalid sort column'; END IF; IF NOT (sort_order = ANY(valid_orders)) THEN RAISE EXCEPTION 'Invalid sort order'; END IF; 动态SQL执行 RETURN QUERY EXECUTE format(' SELECT id, name FROM users ORDER BY %I %s LIMIT %s OFFSET %s', sort_column, sort_order, page_size, offset_val ); END; $$ LANGUAGE plpgsql;

调用：SELECT * FROM get_paginated_data(page => 2, page_size => 5, sort_column => 'name', sort_order => 'DESC');。