pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedpassing a list to a function and returning a recordset
3+ messages / 1 participants
[nested] [flat]
* passing a list to a function and returning a recordset
@ 2002-06-03 21:14 Marie G. Tuite <marie.tuite@edisonaffiliates.com>
0 siblings, 0 replies; 3+ messages in thread
From: Marie G. Tuite @ 2002-06-03 21:14 UTC (permalink / raw)
To: 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'
;
^ permalink raw reply [nested|flat] 3+ messages in thread
* passing a list to a function and returning a recordset
@ 2002-06-03 21:43 Marie G. Tuite <marie.tuite@edisonaffiliates.com>
2002-06-07 20:32 ` Re: passing a list to a function and returning a recordset Marie G. Tuite <marie.tuite@edisonaffiliates.com>
0 siblings, 1 reply; 3+ messages in thread
From: Marie G. Tuite @ 2002-06-03 21:43 UTC (permalink / raw)
To: 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'
;
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: passing a list to a function and returning a recordset
2002-06-03 21:43 passing a list to a function and returning a recordset Marie G. Tuite <marie.tuite@edisonaffiliates.com>
@ 2002-06-07 20:32 ` Marie G. Tuite <marie.tuite@edisonaffiliates.com>
0 siblings, 0 replies; 3+ messages in thread
From: Marie G. Tuite @ 2002-06-07 20:32 UTC (permalink / raw)
To: marie.tuite@edisonaffiliates.com; pgsql-sql
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
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-06-07 20:32 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-06-03 21:14 passing a list to a function and returning a recordset Marie G. Tuite <marie.tuite@edisonaffiliates.com>
2002-06-03 21:43 passing a list to a function and returning a recordset Marie G. Tuite <marie.tuite@edisonaffiliates.com>
2002-06-07 20:32 ` Marie G. Tuite <marie.tuite@edisonaffiliates.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox