Understanding stored procedure return types

[Collin Peters]

Hi all,

I am trying to figure out return types in stored procedures.  I am
using PostgreSQL and have a simple function called saveUser.  Here is
the important parts of the stored procedure:

CREATE OR REPLACE FUNCTION save_member_basic(in_user_id integer,
in_enterprise_id integer, in_username text, in_password text,
in_firstname text, in_lastname text)
 RETURNS integer AS
$BODY$
DECLARE

<snip>...

        return _user_id;

END
$BODY$
 LANGUAGE 'plpgsql' VOLATILE;

So it is a stored procedure that has 6 arguments, and a single integer
return value.  I have been able to successfully call the function with
this sqlmap:

        <typeAlias alias="UserVO" type="ca.mcrt.intouch.objects.UserVO" />

        <resultMap id="userResult" class="UserVO" >
                <result property="userID" column="user_id"/>
                <result property="enterpriseID" column="enterprise_id"/>
                <result property="firstName" column="firstname"/>
                <result property="lastName" column="lastname"/>
                <result property="username" column="username"/>
                <result property="password" column="password"/>
        </resultMap

        <parameterMap id="params-createUser" class="UserVO" >
                <parameter property="userID" jdbcType="integer" mode="IN"/>
                <parameter property="enterpriseID" jdbcType="integer" 
mode="IN"/>
                <parameter property="username" jdbcType="text" mode="IN"/>
                <parameter property="password" jdbcType="text" mode="IN"/>
                <parameter property="firstName" jdbcType="text" mode="IN"/>
                <parameter property="lastName" jdbcType="text" mode="IN"/>
        </parameterMap>

        <procedure id="createUser" parameterMap="params-createUser" 
resultClass="int" >
                { call save_member_basic(?, ?, ?, ?, ?, ?) }
        </procedure

So this successfully calls the stored procedure, but seems to ignore
the 'resultClass="int"' attribute.  So reading up on things I see it
should look like:
                { ? = call save_member_basic(?, ?, ?, ?, ?, ?) }
But this seems to mean I need to have an extra value in my
parameterMap, which would then mean I won't be able to send in my
UserVO class as the parameter.  Unless I add a return value variable
to it or something.  This seems to be a backwards way of doing things.

How come I can't use a resultClass with the procedure tag?  Something
to do with being locked into how JDBC does it?  This concept of an
INOUT parameter is a bit foreign to me, I have never created a stored
procedure where the parameters matched the return value.  I can see
the value in that, but it doesn't apply to this situation.

Collin