Thread

passing a list to a function and returning a recordset

Marie G. Tuite <marie.tuite@edisonaffiliates.com> — 2002-06-03T21:14:03Z

I have the following function.  The function is called by:

    fn_grd_students('(1,2,3,4)','(5,6,7,8)');

I want to pass two lists, do a select based on these lists and return a
recordset to the caller.

And it doesn't work...

Any help would be appreciated.

Thanks.


create or replace function fn_grd_students(text,text)
    returns refcursor as

'
declare
    -- in params
    student_list_in alias for $1;
    assign_list_in alias for $2;

    -- local var
    rc refcursor;
begin

    execute ''open rc for select b.user_id,
                b.grd_assignment_id,
                a.last_name,
                b.grd_grade_id,
                c.grade_desc,
                c.grade_value
         from
                user_common a,
                grd_student_grades b,
                grd_grades c
         where
                b.user_id = a.user_id
                and b.grd_assignment_id in '' || assign_list_in ||
                '' and b.grd_grade_id = c.grd_grade_id and '' ||
                '' a.user_id in '' || student_list_in

;

return rc;
end;
'
language 'plpgsql'
;