Thread: Getting a single row from a ResultSet

Hello,

Is there an easier way to get a single row from a Query than this ?

 - PostgreSQL side

CREATE TYPE public.test_type AS (id int8, name text, password text);

CREATE OR REPLACE FUNCTION public.get_member_by_id(int8)
RETURNS test_type AS '
      DECLARE
        F_ID ALIAS FOR $1;
        F_rec test_type;
BEGIN
  select into F_rec memberid,lastname, password from  tb_member where
memberid= F_ID;
    if NOT FOUND then
      RAISE EXCEPTION \'Inexistent ID --> %\', F_ID;
    end if;
  return F_rec;
END ' LANGUAGE 'plpgsql' VOLATILE;


   - java side

Connection connection = ConnectionPool.getConnection();
CallableStatement statement = connection.prepareCall("{ call
get_member_by_id(?)}");
statement.setInt(1, new Integer(0));
ResultSet resultSet = statement.executeQuery();

----------


I did a search on the mailing list but didn t get any satisfying
alternatives.

* We just need one row, and i tried to declare a type like

   CREATE TYPE public.test_type2 AS (tb_member%ROWTYPE);

but couldn t bypass the syntax check...

* On the java side, having a ResultSet structure sounds like an
overkill. We re just getting one record, and don t need all the
associated metainformation.


Note that in this snipplet I explicitly call each column, but a "select
*" would be preferable. (generating procedures via XSL from tablelist)


Thanks in advance,

   Arne Stölck

   vert'bleu