Обсуждение: How to fetch the RefCursor in via ODBC??

Hi All,
  I wrote a function to return a RefCursor of a temp table as below:

    CREATE FUNCTION reffunc(refcursor, varchar(10), varchar(10))
        RETURNS refcursor AS '
        BEGIN
          EXECUTE ''create local temp table tablexxx
                    (repno character(15), date date)'';
          insert into tablexxx (repno, date) VALUES ( $2, now() );
          insert into tablexxx (repno, date) VALUES ( $3, now() );
          OPEN $1 for EXECUTE '' SELECT * FROM tablexxx '';
          RETURN $1;
          EXECUTE '''';
        END;
    ' LANGUAGE 'plpgsql';

It work fine on psql, at least it work as I expected. I call this
function by following command on psql:

        BEGIN;
          SELECT reffunc('funccursor', 'AAA', 'BBB' );
          FETCH ALL FROM funccursor;
        COMMIT;

However, when I try to call this function via ODBC ( namely in the
Borland DBExplorer ). It got none information for me! I am wondoring
is ODBC supporting RefCursor, since the FAQ of GBorg said the ODBC
only support simple data type. Therefore, does ODBC support
refcursor??

PS: My psqlODBC version is 07.03.0100
    My psql version is  7.3.1
Harry Yau
MIS HK

Harry Yau <harry@aurasound.com.hk> writes:
>   I wrote a function to return a RefCursor of a temp table as below:

>     CREATE FUNCTION reffunc(refcursor, varchar(10), varchar(10))
>         RETURNS refcursor AS '
>         BEGIN
>           EXECUTE ''create local temp table tablexxx
>                     (repno character(15), date date)'';
>           insert into tablexxx (repno, date) VALUES ( $2, now() );
>           insert into tablexxx (repno, date) VALUES ( $3, now() );
>           OPEN $1 for EXECUTE '' SELECT * FROM tablexxx '';
>           RETURN $1;
>           EXECUTE '''';
>         END;
>     ' LANGUAGE 'plpgsql';

I think you probably need the INSERTS to be done via EXECUTE as well,
at least if you want this to work more than once per session.

Not sure about your ODBC issue, but pgsql-odbc would be the place to
ask about that.

            regards, tom lane