CODEWITHSUNDEEP
functionpostgresqlstored-procedures
MitoCode
MitoCode

Reputation: 327

how to return a table from function postgresql with multiples tables in the query?

i have a question, i have a query, that has a multiple tables and i want to create a function that return me that query as a table 

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.apellidos||' '||p.nombres as nombrecompleto,
    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 nombrecompleto

thats is my query and this is my function

CREATE OR REPLACE FUNCTION fn_lisMatricula() RETURNS SETOF Matricula AS
$BODY$
DECLARE
    r Matricula%rowtype;
r Persona%rowtype;

BEGIN
    FOR r IN 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.apellidos||' '||p.nombres as nombrecompleto,
    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 nombrecompleto
    LOOP       
        RETURN NEXT r;
    END LOOP;
    RETURN;
END
$BODY$
LANGUAGE 'plpgsql';

select * from fn_lisMatricula()

the problem is the result of the function only has the structure of my "matricula" table but i want the structure of all my query result.

Upvotes: 0

Views: 1078

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324375

Use RETURNS TABLE and specify the result column types and names. See the PostgreSQL documentation for PL/PgSQL and CREATE FUNCTION.

Alternately, you can:

  • CREATE TYPE a custom composite type for the purpose and return a SETOF it;
  • Use OUT parameters and RETURNS SETOF RECORD,
  • Use RETURNS SETOF RECORD and specify the result record structure when invoking the function

RETURNS TABLE(...) is by far the simplest option of these.

Upvotes: 2

Related Questions