Thread: returning refcursors (into Visual Basic)

Hello.

I'm having some serious difficulties with making a function returning a
recordset in the form of a refcursor to Visual Basic through pgODBC (or
through pgAdminII).
I'm using postgreSQL 7.2 and pgODBC 7.1.9
I've tried with a function such as

CREATE FUNCTION "returnCourse"("bpchar") RETURNS "refcursor" AS
'declare
    _course ALIAS FOR $1;
    _refcursor refcursor;
begin
    OPEN _refcursor FOR SELECT * FROM course where code = _course;
    return _refcursor;
end;

    ' LANGUAGE 'plpgsql';

But the only thing I get is a single value recordset with the string
"<unnamed cursor 1>".
Does anybody know what I'm doing wrong?


/erik hjortsberg

http://hysteriskt.viktigt.nu
http://www.ostgota.lu.se
http://come.to/erik.h

Erik Hjortsberg wrote:
>
> Hello.
>
> I'm having some serious difficulties with making a function returning a
> recordset in the form of a refcursor to Visual Basic through pgODBC (or
> through pgAdminII).
> I'm using postgreSQL 7.2 and pgODBC 7.1.9
> I've tried with a function such as
>
> CREATE FUNCTION "returnCourse"("bpchar") RETURNS "refcursor" AS
> 'declare
>         _course ALIAS FOR $1;
>         _refcursor refcursor;
> begin
>         OPEN _refcursor FOR SELECT * FROM course where code = _course;
>         return _refcursor;
> end;
>
>         ' LANGUAGE 'plpgsql';
>
> But the only thing I get is a single value recordset with the string
> "<unnamed cursor 1>".
> Does anybody know what I'm doing wrong?

What your function returns is a reference to (name of)
a cursor and unfortunately there are few applications
which recognize "refcursor" and you have to call FETCH
commands using the cursor name to get the result.

regards,
Hiroshi Inoue