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
– Horus, Commented Apr 1, 2013 at 0:34
Sorry, I cannot make sense of your question. Maybe you are looking for something like this?? — Erwin Brandstetter
– Erwin Brandstetter, Commented Apr 1, 2013 at 1: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
– MitoCode, Commented Apr 1, 2013 at 4:50

Craig Ringer · Accepted Answer · 2013-04-01 23:23:57Z

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 1, 2013 at 23:23

answered Apr 1, 2013 at 7:29

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MitoCode Over a year ago

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 =)

Craig Ringer Over a year ago

@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: stackoverflow.com/search and here: google.com

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related