Skip site navigation (1) Skip section navigation (2)

Peripheral Links

Header And Logo

PostgreSQL
| The world's most advanced open source database.

Site Navigation

Search for
  Advanced Search

Calling a stored procedure with a custom return type



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



Home | Main Index | Thread Index

Privacy Policy | PostgreSQL Archives hosted by Command Prompt, Inc. | Designed by tinysofa
Copyright © 1996 – 2008 PostgreSQL Global Development Group