postgreSQL基本教程-创建函数

整理偏见

已于 2024-03-20 17:51:52 修改

阅读量1.2k

点赞数 28

分类专栏：各种数据库文章标签： postgresql 数据库 sql

于 2024-03-20 17:46:34 首次发布

本文链接：https://blog.csdn.net/m0_46579823/article/details/136882659

版权

各种数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

创建标量函数

在PL/pgSQ中，标量函数是指返回单个标量值（例如整数、浮点数、字符串等）的函数。这意味着标量函数接受参数，并返回单个值作为结果。

在 PL/pgSQL 中声明标量函数时，需要指定返回类型，该返回类型可以是任何合法的 PostgreSQL 数据类型，例如 integer、text、numeric 等。标量函数可以用于执行诸如计算、转换或逻辑判断等操作，并返回单个结果值。

--创建函数的基本结构
CREATE OR REPLACE FUNCTION get_user_count() --这个函数是否存在如果不存在就创建，如果存在会提示你
  RETURNS INTEGER                           --自己规定返回值，注意这里是"returns"
  AS $$                                     --$$用于定义多行字符串,也可以写作“$body$”
DECLARE                                     --declare声明变量
    user_count INTEGER;
BEGIN                                       --开始
    SELECT COUNT(*) INTO user_count FROM public.users;
    RETURN user_count;                      --return返回值,注意和returns的类型要相同
END;
$$ LANGUAGE plpgsql;                        --$$与前面的对应,
                                            --LANGUAGE 指定函数体使用的编程语言为 PL/pgSQL

表函数
1. 什么是表函数？在 PostgreSQL 中，表函数可以返回一个表格变量，这意味着可以像查询表一样对其进行引用和操作。在下面的示例中，我们创建了一个名为 get_users_by_age 的表函数，它接受一个整数参数 age_threshold，并返回一个包含 name 和 age 列的表格。函数体中使用 RETURN QUERY 语句执行了一个查询，根据传入的年龄阈值来筛选出符合条件的用户数据。
2. ```
CREATE FUNCTION get_users_by_age(age_threshold INTEGER)
  RETURNS TABLE (name TEXT, age INTEGER)
  AS $$
  BEGIN
    RETURN QUERY SELECT name, age FROM users WHERE age > age_threshold;
  END;
  $$
  LANGUAGE plpgsql;
--可以这样调用：
SELECT * FROM get_users_by_age(30);
--也可直接按照函数调用：
SELECT get_users_by_age(30);
```
3. 表函数的分类：
  1. 内联：
```
CREATE OR REPLACE FUNCTION get_all_users()
  RETURNS TABLE (userid INTEGER, username CHARACTER VARYING(20), userpassword CHARACTER VARYING(50))
  AS
  $$
    SELECT userid, username, userpassword FROM public.users;
  $$
  LANGUAGE sql ;
select get_all_users();
--或者：
select * from get_all_users();
```
    - 内联函数通常使用 LANGUAGE sql 来定义，其函数体中包含了单个 SQL 查询语句。
    - 内联函数非常适合简单的查询逻辑，可以将查询直接嵌入到函数体中，提高执行效率。
    - 适用于简单的数据检索、过滤和计算等场景，其优势在于简洁并且执行效率高。
  2. 多语句函数（Multi-Statement Functions）：
    - 多语句函数通常使用 LANGUAGE plpgsql 或其他类似的语言来定义，其函数体可以包含多个 SQL 查询语句以及其他逻辑控制语句。
    - 多语句函数适用于需要复杂逻辑处理的场景，例如循环、条件判断、异常处理等。
    - 适用于需要进行更复杂数据转换、业务逻辑处理、数据操作等场景，其优势在于可以编写更复杂的程序逻辑。
    - 下面实现和内联一样的功能，可以对比理解。
```
CREATE OR REPLACE FUNCTION get_all_users()
  RETURNS TABLE (userid int, username character varying(20), userpassword character varying(50))
  AS
$$
BEGIN
    RETURN QUERY SELECT * FROM public.users;
    
END;
$$ LANGUAGE plpgsql;

select * from get_all_users();
```
4. 使用setof类型，要用return next
存储过程，调用别的函数，捕获处理异常
- 存储过程是一段预先编译好并存储在数据库中的可重复使用的程序代码。它们允许您在数据库中创建一组 SQL 语句和逻辑操作，然后通过简单地调用该存储过程来执行这些操作。存储过程通常用于封装常见的业务逻辑操作，以便重复使用.
- 在存储过程中调用其他函数和异常处理，这里尝试调用下面这个除法函数，想要实现的异常处理是，如果出现除数是0那么报错：
```
CREATE OR REPLACE FUNCTION test(a0 int)--这里我创建一个除法函数
returns int as
$$
declare a1 int;
begin a1=a0/(a0-6);
return a1;
end
$$
language plpgsql;
```
  下面是一个完整的包含查询、调用和捕获异常的存储过程：
```
CREATE OR REPLACE PROCEDURE retrieve_user_data(id int)
AS $$
DECLARE password text;
number int;
BEGIN
    -- 根据用户id查询用户表中的userpassword
    SELECT userpassword into password FROM hello 
	where userid=id;
	raise notice'这个id=%的用户密码是%',id,password;
    -- 调用其他函数（这里调用刚刚创建的test函数）
    select test(id) into number;
	raise notice'a/(a-6)=%',number;
EXCEPTION
    -- 捕获异常并处理,underfined_table表示欸有这个表；others表示其他异常
    WHEN undefined_table THEN
        RAISE NOTICE '捕获到 undefined_table 异常';
	WHEN division_by_zero THEN
        RAISE NOTICE '捕获到 division_by_zero 异常';
    WHEN others THEN
        RAISE EXCEPTION '发生了其他异常';
END;
$$ LANGUAGE plpgsql;
```
- 调用语句如下：我把输入值设为6，在查询语句中，发现查询的用户密码是123，在test中出现除数为0的情况，这里报了第二个错误的提示
- 如果我把查询语句里的表修改成一个不存在的表，那么结果如下，并且我们发现当代码执行到查询错误之后后面的调用test部分没有运行，可以知道检测出错误之后代码直接跳到了异常处理结束执行。

format()用法

在 PostgreSQL 中，可以使用 format() 函数来格式化字符串。该函数接受一个格式字符串和零个或多个参数，并返回格式化后的字符串。下面是一个示例，演示如何使用 format() 函数：

-- 示例1：简单的格式化字符串
SELECT format('Hello, %s!', 'World'); -- 输出：Hello, World!

-- 示例2：带有占位符的格式化字符串
SELECT format('Name: %s, Age: %s', 'John', 25); -- 输出：Name: John, Age: 25

-- 示例3：使用位置参数的格式化字符串
SELECT format('Name: %1$s, Age: %2$s', 'John', 25); -- 输出：Name: John, Age: 25

异常处理使用errcode 和message

create or replace procedure proce(choice_func int,tablenam text)
as 
$body$
declare notic text;
begin if choice_func>0 then 
execute format('create table if not exists %I (tableid serial,column1 varchar(10))',tablenam);
elseif choice_func<0 then
execute format('select * from %I',tablenam) into notic;
raise notice 'quary result %',notic;
else raise exception 'please dont enter 0';
end if;
end
$body$
language plpgsql;

call proce(-1,'newtable1');

create table mytab(firstname varchar(20),lastname varchar(20));
INSERT INTO mytab(firstname, lastname) VALUES('Tom', 'Jones'); 
create or replace function mma()
returns int as
$$
declare x int:=-1;
y int;
BEGIN 
 UPDATE mytab SET firstname = 'Joe' WHERE lastname = 'Jones'; 
 x := x + 1; 
 y := x / 0; 
EXCEPTION 
 WHEN division_by_zero THEN RAISE NOTICE 'перехватили ошибку division_by_zero'; 
 RETURN x; 
return x;
END; 
$$
language plpgsql;

select mma();
select * from mytab;

递归查询

create table depart(
	departid serial primary key not null,
	sub_departid int
);
insert into depart(sub_departid) values(7),(6),(1),(2),(4),(3),(5);
select * from depart;

with recursive t as 
(
	select departid,sub_departid from public."depart" where departid=1
	union 
	select d.departid,d.sub_departid from public."depart" d,t where d.sub_departid=t.departid
)select * from t;

关于游标的使用

create or replace function cur_try()
returns text as
$$
declare cur1 refcursor;
r record;

begin 
open cur1 for select * from files;
fetch cur1 into r;
raise notice 'cur1.filename:%',r.filename;
close cur1;
return r.fileid;
end
$$
language plpgsql;

select cur_try();

推荐下面这个关于游标使用的教程，很详细：Postgresql游标使用介绍（cursor）_postgresql 游标-CSDN博客

一些内置函数和磁盘函数，结构体类型数据使用：

create type person as(person_name text,age int);
create or replace function try_inner_function(person_info person)
returns text as
$$
declare str text:=person_info.person_name||' is '||person_info.age::text||' years old';
strlen int;
strbig bigint;
begin strlen=char_length(str);
raise notice 'strlen is %',strlen;
strbig=pg_column_size(str);
raise notice 'strbit is %',strbig;
return str;
end
$$
language plpgsql;

select try_inner_function(row('Anna',23));