passing a list to a function and returning a recordset

From: "Marie G(dot) Tuite" <marie(dot)tuite(at)edisonaffiliates(dot)com>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: passing a list to a function and returning a recordset
Date: 2002-06-03 21:43:01
Message-ID: IGELKLINGDMODABPOOFEGEAGCDAA.marie.tuite@edisonaffiliates.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Tom Lane 2002-06-03 23:38:31 Re: alternate idioms for large "IN (...)" lists
Previous Message Marie G. Tuite 2002-06-03 21:14:03 passing a list to a function and returning a recordset