Reply-To: <marie.tuite@edisonaffiliates.com>
From: "Marie G. Tuite" <marie.tuite@edisonaffiliates.com>
To: <pgsql-sql@postgresql.org>
Subject: passing a list to a function and returning a recordset
Date: Mon, 3 Jun 2002 16:14:03 -0500
Message-ID: <IGELKLINGDMODABPOOFEAEAECDAA.marie.tuite@edisonaffiliates.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Importance: Normal

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'
;