使用Pgsql创建自定义函数，生成uuid或等级

最新推荐文章于 2024-09-20 16:28:14 发布

1024小神

最新推荐文章于 2024-09-20 16:28:14 发布

阅读量2.3k

点赞数 1

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

本文链接：https://blog.csdn.net/weixin_44786530/article/details/121784924

版权

数据库专栏收录该内容

77 篇文章 16 订阅

订阅专栏

CREATE OR REPLACE FUNCTION uuid()
    RETURNS varchar AS
$uuid_str$
DECLARE
    uuid_str varchar;
BEGIN
    uuid_str := (select array_to_string(
                                array(select substring('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'
                                                       FROM (ceil(random() * 62))::int FOR 1)
                                      FROM generate_series(1, 32)), ''));
    RETURN uuid_str;
END;
$uuid_str$ LANGUAGE plpgsql;

select uuid() as weiyi;

创建带参数的函数，并且根据参数变化返回不同的值：

-- 根据传入的成绩，返回ABC三个等级，使用case
create or replace function getty(score int)
    returns varchar as
$ty$
declare
    ty varchar;
begin
    case
        when score between 0 and 60 then ty = 'C';
        when score between 60 and 80 then ty = 'B';
        when score between 80 and 100 then ty = 'A';
        else ty = 'A++';
        end case;
    return ty;
end;
$ty$ language plpgsql;


select getty(0);

drop function getty;
drop function myuuid();
drop function myuuid(int);

create or replace function myuuid(n int)
    returns varchar as
$uuid$
declare
    uuid varchar;
begin
--     if elseif 只能在方法中使用
    if n < 40 then
        uuid = 'D';
    elseif n between 40 and 60 then
        uuid = 'C';
    elseif n between 60 and 80 then
        uuid = 'B';
    else
        uuid = 'A';
    end if;
    return uuid;
end;
$uuid$ language plpgsql;

select myuuid(80) as uuid;