Calling a stored procedure with a custom return type

From: "Brad Larson" <bklarson+postgres(at)gmail(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Calling a stored procedure with a custom return type
Date: 2007-10-25 22:32:38
Message-ID: efa71ccf0710251532u48c15214ta1d1d7493acf3b10@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Oliver Jowett 2007-10-25 22:37:20 Re: Potential inconsistency in handling of timestamps
Previous Message Kris Jurka 2007-10-25 22:30:51 Re: Potential inconsistency in handling of timestamps