Thread: Problem with Function

Hi, I'm new with pgSQL and I'm not sure why, but I
keep getting the same error. I want to call a function
that returns a cursor and I keep getting the following
exception:

org.postgresql.util.PSQLException: ERROR: cursor
"<unnamed portal 1>" does not exist

I would appreciate any help with this problem.

This is my JDBC code:

Connection objlConnection =
ConnectionPool.getConnection(saJNDI);
CallableStatement statement = null;
ResultSet rs = null;
try{
    statement = objlConnection.prepareCall("{? = call
ret_user(?)}");
    statement.registerOutParameter(1, Types.OTHER);
    statement.setObject(2, "munoze");
    statement.execute();
    rs = (ResultSet)statement.getObject(1);
    while(rs.next()){
        System.out.println(rs.getString(1));
        System.out.println(rs.getString(2));
        System.out.println(rs.getString(3));
    }
    rs.close();
    statement.close();
} catch (SQLExcepton e){
...


This is my function code:

CREATE OR REPLACE FUNCTION
traer_usuario(pnombreusuario "varchar")
  RETURNS refcursor AS
$BODY$
DECLARE
    ccursor refcursor;
BEGIN
    open ccursor for
    select nombreusuario,
        contrasena,
        idrol
    from usuario
    where nombreusuario = quote_literal($1);

    RETURN ccursor;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;



__________________________________________________
Correo Yahoo!
Espacio para todos tus mensajes, antivirus y antispam ¡gratis!
Regístrate ya - http://correo.espanol.yahoo.com/

On Thu, Mar 02, 2006 at 03:18:14PM -0600, Eduardo Muoz wrote:
> Hi, I'm new with pgSQL and I'm not sure why, but I
> keep getting the same error. I want to call a function
> that returns a cursor and I keep getting the following
> exception:
>
> org.postgresql.util.PSQLException: ERROR: cursor
> "<unnamed portal 1>" does not exist

Cursors are closed at the end of the transaction that created them.
Is your JDBC code calling the function and using the cursor in the
same transaction?  I'd guess that autocommit is enabled, so unless
you explicitly start a transaction then each command has its own
transaction.

>     where nombreusuario = quote_literal($1);

You shouldn't need quote_literal here since you're not building a
string for EXECUTE.  In fact, not only is it unecessary but it'll
probably cause the query not to match what you were expecting.

--
Michael Fuhr