How to return a callableStatement result?

Question

I'm using PostgreSQL and I have a function that returns a record:

CREATE OR REPLACE FUNCTION fn_lisMatricula()
  RETURNS record AS
$BODY$
SELECT mat.codigo as codmatr, mat.codigoalumno as codal, mat.codigoempresa as codemp ,mat.codigopresentacion as codpre,
    mat.codigosecretaria as codsec, mat.fecha as fechamat, mat.estado as estadomat,
    mat.vigencia as vigmat, p.apellidos as apeAl,
    p.nombres as nomAl,razonsocial ,pre.codigocurso as codcur,cur.nombre as curso
    FROM matricula mat join alumno al on mat.codigoalumno = al.codigoalumno
    join persona p on  al.codigoalumno = p.codigo
    join persona pe on mat.codigoalumno = pe.codigo
    left join empresa emp on mat.codigoempresa = emp.codigo
    join presentacion pre on mat.codigopresentacion = pre.codigo
    join curso cur on cur.codigo = pre.codigocurso
    order by p.apellidos
$BODY$
  LANGUAGE sql VOLATILE 

I call it with this, because I must declare the type of the out values, that works fine in Postgres:

select * from fn_lisMatricula() as (codmatr integer,codal integer,codemp integer,codpre integer,codsec integer,fechamat date,
estadomat char,vigmat boolean,apeal varchar,nomal varchar,razonsocial varchar,codcur integer,curso varchar)

But to call this in a callableStatement in java I use:

proc = this.cn.prepareCall("{call fn_lisMatricula()}")

But I get a postgreSQL.exception:

the list of definition of columns is required to functions that returns «record»

So I temporarily solved this in Java using a preparedStatement:

PreparedStatement ps;
String SQL = "select * from fn_lisMatricula() as (codmatr integer,codal integer,codemp integer,codpre integer,codsec integer,fechamat date,\n"
                    + "estadomat char,vigmat boolean,apeal varchar,nomal varchar,razonsocial varchar,codcur integer,curso varchar)";

ps = this.cn.prepareStatement(SQL);

I want to use a callableStatement. How can I do this?

Answer 1

I solve it, with this ... i read some from create table clause ... thanks =)

CREATE OR REPLACE FUNCTION fn_lisMatricula()
  RETURNS TABLE (codmatr integer,codal integer,codemp integer,codpre integer,codsec integer,fechamat date,
estadomat char,vigmat boolean,apeal varchar,nomal varchar,razonsocial varchar,codcur integer,curso varchar) AS
$BODY$
BEGIN
RETURN QUERY
SELECT mat.codigo as codmatr, mat.codigoalumno as codal, mat.codigoempresa as codemp ,mat.codigopresentacion as codpre,
    mat.codigosecretaria as codsec, mat.fecha as fechamat, mat.estado as estadomat,
    mat.vigencia as vigmat, p.apellidos as apeAl,
    p.nombres as nomAl,emp.razonsocial ,pre.codigocurso as codcur,cur.nombre as curso
    FROM matricula mat join alumno al on mat.codigoalumno = al.codigoalumno
    join persona p on  al.codigoalumno = p.codigo
    join persona pe on mat.codigoalumno = pe.codigo
    left join empresa emp on mat.codigoempresa = emp.codigo
    join presentacion pre on mat.codigopresentacion = pre.codigo
    join curso cur on cur.codigo = pre.codigocurso
    order by p.apellidos;
END
$BODY$ 
LANGUAGE plpgsql;

select * from fn_lisMatricula()