Thread: refcursor

Hi!

I'm playing with refcursor, but I'm not successful. I defined follwing
function:

CREATE OR REPLACE FUNCTION stat() RETURNS refcursor AS '
DECLARE
    ref refcursor;
BEGIN
    OPEN ref FOR
        SELECT * FROM tagebuch;
    RETURN ref;
END;
' LANGUAGE 'plpgsql';

Trying to see some results gives me:

sport=# select stat();
ERROR:  fmgr_info: function 16556: cache lookup failed
sport=# select version();
                            version
---------------------------------------------------------------
 PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.95.4
(1 row)
sport=# select * from tagebuch;
...

This works fine. I receive the same error when I try to call stat() from Python with pyPgSQL-DBI adaptor. What am I
doingwrong (PostgreSQL runs on a Debian/Woody box)? 

Regards,
Oliver

--
VECERNIK Datenerfassungssysteme
A-2560 Hernstein, Hofkogelgasse 17
Tel.: +43 2633 47530, Fax: DW 50
http://members.aon.at/vecernik

U could try this:

CREATE OR REPLACE FUNCTION stat() RETURNS refcursor AS '
DECLARE
  ref refcursor;

 ---------
 change 4:
 ---------

CREATE OR REPLACE FUNCTION stat(refcursor) RETURNS refcursor AS '
DECLARE
  cursor ALIAS FOR $1;
  ref refcursor;

sport=# BEGIN;
sport=#   select stat(cursor);
sport=#   fetch all in cursor;
sport=# END;

It works fine with me!!!


On Sun, 05 Jan 2003 11:11:57 +0100
Oliver Vecernik <vecernik@aon.at> wrote:

> Hi!
>
> I'm playing with refcursor, but I'm not successful. I defined follwing
> function:
>
> CREATE OR REPLACE FUNCTION stat() RETURNS refcursor AS '
> DECLARE
>     ref refcursor;
> BEGIN
>     OPEN ref FOR
>         SELECT * FROM tagebuch;
>     RETURN ref;
> END;
> ' LANGUAGE 'plpgsql';
>
> Trying to see some results gives me:
>
> sport=# select stat();
> ERROR:  fmgr_info: function 16556: cache lookup failed
> sport=# select version();
>                             version
> ---------------------------------------------------------------
>  PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.95.4
> (1 row)
> sport=# select * from tagebuch;
> ...
>
> This works fine. I receive the same error when I try to call stat() from Python with pyPgSQL-DBI adaptor. What am I
doingwrong (PostgreSQL runs on a Debian/Woody box)? 
>
> Regards,
> Oliver
>
> --
> VECERNIK Datenerfassungssysteme
> A-2560 Hernstein, Hofkogelgasse 17
> Tel.: +43 2633 47530, Fax: DW 50
> http://members.aon.at/vecernik
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>


--
Renê Salomão
Ibiz Tecnologia -- www.ibiz.com.br

>> sport=# select stat();
>> ERROR:  fmgr_info: function 16556: cache lookup failed

Does *any* plpgsql function work?  I think you probably broke plpgsql by
deleting its handler function, or something like that.  Try dropping and
recreating the plpgsql language (then you'll need to recreate your
function, too).

7.3 contains interlocks to prevent this sort of mistake, btw.

            regards, tom lane