CREATE OR REPLACE FUNCTION "public"."addadmin" (p_passport varchar, p_password varchar, p_adminname varchar, p_adminnickname varchar) RETURNS integer AS
$body$
declare
insertresult integer;
begin
if exists(select 1 from public.t_admin where passport = $1) then
begin
insertresult:=-1;
end;
else
begin
insert into public.t_admin (passport,password,adminname,adminnickname) values($1,md5($2),$3,$4);
insertresult:=(select adminid from public.t_admin where passport = $1);
end;
end if;
return insertresult;
EXCEPTION
WHEN others THEN
return -1;
end;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION "public"."userreg" (p_passport varchar, p_nickname varchar, out "P_password" integer, out p_userid integer) RETURNS record AS
$body$
declare
insertresult integer;
tmpstr varchar(20);
begin
if exists(select 1 from public.t_user where passport = $1) then
begin
$4:=-1;
end;
else
begin
$3 := round((random()+1) * 9999999 );
tmpstr := cast ($3 as varchar);
tmpstr := md5(tmpstr);
insert into public.t_user (passport,password,nickname) values($1,tmpstr,$2);
$4:=(select userid from public.t_user where passport = $1);
end;
end if;
end;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
总算写对了一个PostgreSQL函数,保存下来
最新推荐文章于 2022-11-06 09:16:47 发布