Received: from localhost.localdomain (postgresql.org [64.49.215.8])
	by localhost (Postfix) with ESMTP id 54944475CF3
	for <pgsql-sql@postgresql.org>; Fri,  7 Jun 2002 16:48:46 -0400 (EDT)
Received: from edisonaffiliates.com (w205.z208176060.phx-az.dsl.cnc.net
	[208.176.60.205])
	by postgresql.org (Postfix) with SMTP id 25AF9475B64
	for <pgsql-sql@postgresql.org>; Fri,  7 Jun 2002 16:30:12 -0400 (EDT)
Received: from redhen ([208.176.60.194]) by edisonaffiliates.com ;
	Fri, 07 Jun 2002 15:29:06 -0500
Reply-To: <marie.tuite@edisonaffiliates.com>
From: "Marie G. Tuite" <marie.tuite@edisonaffiliates.com>
To: <marie.tuite@edisonaffiliates.com>, <pgsql-sql@postgresql.org>
Subject: Re: passing a list to a function and returning a recordset
Date: Fri, 7 Jun 2002 15:32:49 -0500
Message-ID: <IGELKLINGDMODABPOOFEGEDHCDAA.marie.tuite@edisonaffiliates.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0)
In-Reply-To: <IGELKLINGDMODABPOOFEGEAGCDAA.marie.tuite@edisonaffiliates.com>
X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Importance: Normal
X-Archive-Number: 200206/85
X-Sequence-Number: 8050

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
>