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

Question

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.

Have you tried creating a view with that, and then using the view intead? — Horus, Apr 01 '13 at 00:34
Sorry, I cannot make sense of your question. Maybe you are looking for [something like this?](http://stackoverflow.com/questions/11740256/refactor-a-pl-pgsql-function-to-return-the-output-of-various-select-queries/11751557#11751557)? — Erwin Brandstetter, Apr 01 '13 at 01:05
i want to return a table with the columns of my query, that query has more than 2 tables how can i do that ? — MitoCode, Apr 01 '13 at 04:50

Craig Ringer · Answer 1 · 2013-04-01T23:23:57.143

2

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.

edited Apr 01 '13 at 23:23

answered Apr 01 '13 at 07:29

Craig Ringer

307,061
76
688
778

yeah i read somethig about returns table but i dont know how its works , i type some code to return table but doesnt works, can you write me a example code to return table? an easy one please . Thanks =) – MitoCode Apr 01 '13 at 13:58
@J.A. No, I won't write an example. You can already find many examples here on Stack Overflow, in the PostgreSQL documentation, and elsewhere. Start here: http://stackoverflow.com/search and here: http://google.com/ – Craig Ringer Apr 01 '13 at 23:24

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

1 Answers1