Обсуждение: passing a list to a function and returning a recordset

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

Realised my error, should have been using open for execute.


> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Marie G. Tuite
> Sent: Monday, June 03, 2002 4:43 PM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] passing a list to a function and returning a recordset
> 
> 
> 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'
> ;
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>