returns a table of several columns

I would like to make a function that returns a table of several columns in dependence on the number of columns entered per parameter

That is: if the variable num_columns is 10, the function must return 10 columns and if num_columns is 15 the function must return 15 columns this is my function:

CREATE OR REPLACE FUNCTION "estadisticas"."fn_tabla_mes_lab_x_secc"()
RETURNS setof record AS $BODY$

    DECLARE
    dia1 int;dia2 int;dia3 int;dia4 int;dia5 int;dia6 int;dia7 int;dia8  int;dia9 int;dia10 int;
    dia11 int;dia12 int;dia13 int;dia14 int;dia15 int;dia16 int;dia17 int;dia18 int;dia19 int;dia20 int;dia21 int;dia22 int;dia23 int;dia24 int;dia25 int;
    contador INT := 0;
    num_columnas INT:= 15;
    BEGIN
    dia1:= 1;dia2:= 32;dia3:= 234 ;dia4:= 534;dia5:= 33;dia6:= 123;dia7:=   11;dia8:= 62;dia9:= 32;dia10:= 21;                
    dia11:= 0;dia11:= 0;dia13:= 0 ;dia14:= 0;dia15:= 0;dia16:= 0;dia17:=0; dia18:= 0;dia19:=0;dia20:= 0; dia21:= 0;dia22 := 0; dia23:= 0;dia24:= 0;dia25:= 0;

    WHILE contador<>5 LOOP
    IF num_columnas = 10 THEN
    RETURN query SELECT dia1,dia2,dia3,dia4,dia5,dia6,dia7,dia8,dia9,dia10;
    ELSE
    RETURN query SElECT dia11,dia12,dia13,dia14,dia15,dia16,dia17,dia18,dia19,dia20,dia21,dia22,dia23,dia24,dia25;
    END IF;        
    contador:=contador+1;
    END LOOP;
    RETURN;
    END$BODY$
LANGUAGE plpgsql

Apparently I have a problem with the setof record data type since it returns the following error

Procedure execution failed

ERROR: se llamó una función que retorna un conjunto en un contexto que no puede aceptarlo CONTEXT: función PL/pgSQL fn_tabla_mes_lab_x_secc() en la línea 14 en RETURN QUERY

Time: 0,002s

could you help me please?

Answer

You can not use RETURNS SETOF record if you do not define the column names in a query executing that stored function. You can use alternatives such as

RETURNS refcursor
with temporary table and dual query.

Please visit the answer here Refactor a PL/pgSQL function to return the output of various SELECT queries

Advertisement

Answer