求大神把PostgreSQL 一个存储过程转变为自定义函数

Question

存储过程如下：
create procedure calcGPAbySno (
@vsno char(10),
@avg_gpa decimal(10,2) out
) as
declare @gpa decimal(10,1);
declare @vcredit int;
declare @vgrade int;
declare @sum_credit int;
declare @sum_gpa decimal(10,1);
declare cur_gpa cursor for select Ccredit,grade from course c,sc where c.cno=sc.cno and sno=@vsno and year<>'2014';
begin
set @sum_gpa=0.0;
set @sum_credit=0;
open cur_gpa;
fetch next from cur_gpa into @vcredit,@vgrade;
while @@FETCH_STATUS=0
begin
if @vgrade>=95 set @gpa=5.0;
else if @vgrade<60 set @gpa=0;
else set @gpa=5.0-(95-@vgrade)*0.1;

    set @sum_credit=@sum_credit+@vcredit;
    set @sum_gpa=@sum_gpa+@gpa*@vcredit;
    fetch next from cur_gpa into @vcredit,@vgrade;
  end;
close cur_gpa;
deallocate cur_gpa;
if @sum_credit=0 set @avg_gpa=0
else set @avg_gpa=@sum_gpa/@sum_credit;
update student set gpa=@avg_gpa where sno=@vsno;

end;
这是为了实现输入为学生学号，输出为该学生的GPA的功能，并且考虑大一刚入学、正在学课程、不及格课程等情况。

Answer 1

 create function  getCalcGPAbySnoById(@student_cno char(10))
returns decimal(10,2) as
begin
    declare @avg_gpa decimal(10,2);
    declare @gpa decimal(10,1);
    declare @vcredit int;
    declare @vgrade int;
    declare @sum_credit int;
    declare @sum_gpa decimal(10,1);
    --根据传递的参数（学生的cno）用select 查询此学生的选课信息 并 创建游标
    declare cur_gpa cursor for select Ccredit,grade from course c,sc where c.cno=sc.cno and sno=@vsno and year<>'2014' and sc.cno=@student_cno;

    set @sum_gpa=0.0;--存放此学生所有选课 课程的gpa之和
    set @sum_credit=0;--存放此学生所有选课 的总成绩
    open cur_gpa;--打开游标
    fetch next from cur_gpa into @vcredit,@vgrade;--将游标所指的值赋值给 变量 @vcredit,@vgrade
    while @@FETCH_STATUS=0
    begin
        if @vgrade>=95 set @gpa=5.0;
         else if @vgrade<60 set @gpa=0;
        else set @gpa=5.0-(95-@vgrade)*0.1;
        set @sum_credit=@sum_credit+@vcredit;
        set @sum_gpa=@sum_gpa+@gpa*@vcredit;
        fetch next from cur_gpa into @vcredit,@vgrade;
    end
    close cur_gpa;
    deallocate cur_gpa;
    if @sum_credit=0 set @avg_gpa=0;
    else set @avg_gpa=@sum_gpa/@sum_credit;
    update student set gpa=@avg_gpa where sno=@vsno;
    return @avg_gpa;  
end