Calling a stored procedure with a custom return type - Mailing list pgsql-jdbc

From Brad Larson
Subject Calling a stored procedure with a custom return type
Date
Msg-id efa71ccf0710251532u48c15214ta1d1d7493acf3b10@mail.gmail.com
Whole thread Raw
Responses Re: Calling a stored procedure with a custom return type  (Kris Jurka <books@ejurka.com>)
List pgsql-jdbc
I'm sorry if this is already documented elsewhere; I've googled around
and searched the jdbc source without any luck.  I have a stored
procedure (PL/pgSQL) which uses a custom return type of the form:

CREATE TYPE login_return_type AS (
        "user"            BIGINT,
        "session_key"    VARCHAR(255),
        "admin"            BOOLEAN,
        "null_password"    BOOLEAN
);

CREATE FUNCTION login(
    username varchar(50),
    password varchar(250),
    address varchar(20),
    agent varchar(255)
    ) RETURNS login_return_type AS $$
.....
$$ LANGUAGE plpgsql;

How can I call this from JDBC?  I can call it with a

prepareStatement("select login( ?, ?, ?, ? )");

but then the results are all returned as 1 string, of the form "(int,
string, t, t)", which of course isn't ideal.  I also tried

prepareCall("{ ? = call login(?, ?, ?, ? ) }");

which gives an error that the number of out parameters specified
doesn't match the query.  This gives me hope, because when I step
through the code, the metadata reports 4 columns of return data.

How should I format my JDBC request for a stored procedure of this
form?  Can I get the data back in individual columns, or am I stuck
parsing the big string?

Thanks!!!
-Brad

pgsql-jdbc by date:

Previous
From: Kris Jurka
Date:
Subject: Re: Potential inconsistency in handling of timestamps
Next
From: Oliver Jowett
Date:
Subject: Re: Potential inconsistency in handling of timestamps