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

快来打我* 2023-10-04 17:15 15阅读 0赞

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;

![499af9a5bda8466196b5d933aa349e2e.png][]

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

-- 根据传入的成绩，返回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;

![e10e027336e641a895ca7d0eb8788854.png][]

[499af9a5bda8466196b5d933aa349e2e.png]: https://img-blog.csdnimg.cn/499af9a5bda8466196b5d933aa349e2e.png
[e10e027336e641a895ca7d0eb8788854.png]: https://img-blog.csdnimg.cn/e10e027336e641a895ca7d0eb8788854.png