Home / Questions / 6761
Accepted
Ariel Octavio D'Alfeo
Asked: 2020-04-02 10:29:07 +0800 CST 2020-04-02 10:29:07 +0800 CST

Return All Table Fields (PostgreSql Function)

  • 772

How can I make a postgresql stored procedure or function return all the columns of the table without having to specify them? I want to achieve the behavior of Sql Server query Sp. With the code that I show, it gives me the errorERROR: la lista de definición de columnas es obligatoria para funciones que retornan «record»

    CREATE OR REPLACE FUNCTION sp_consulta_topes(IN text character varying,idObraSocial integer, idPlan Integer)
      RETURNS SETOF record AS
    $BODY$
    declare
     r RECORD;
    BEGIN
     FOR r IN
        Select * from vw_topes 
        where (upper(grupo_practicas) like '%'|| upper(regexp_replace ($1,' ','%', 'g' )) || '%'
         or 
         upper(practica_n) like '%'|| upper(regexp_replace ($1,' ','%','g' )) || '%' 
        or
        upper(practica) like '%'|| upper($1) || '%' )
        and (fecha_baja is null or fecha_baja>current_date)
        and id_obra_social=COALESCE(idObraSocial, id_obra_social) and id_plan=COALESCE(idPlan, id_plan)
     loop
       return next r;
     end loop;

    END;

    $BODY$
LANGUAGE plpgsql VOLATILE
sql

1 Answers

  1. Best Answer

    Instead of returning SETOF recordyou should return SETOF <tu tabla>. This code should work:

    CREATE OR REPLACE FUNCTION sp_consulta_topes(IN text character varying,idObraSocial integer, idPlan Integer)
RETURNS SETOF vw_topes AS
$BODY$
BEGIN
    RETURN query
        Select * from vw_topes 
        where (upper(grupo_practicas) like '%'|| upper(regexp_replace ($1,' ','%', 'g' )) || '%'
         or 
         upper(practica_n) like '%'|| upper(regexp_replace ($1,' ','%','g' )) || '%' 
        or
        upper(practica) like '%'|| upper($1) || '%' )
        and (fecha_baja is null or fecha_baja>current_date)
        and id_obra_social=COALESCE(idObraSocial, id_obra_social) and id_plan=COALESCE(idPlan, id_plan);

END;

$BODY$
LANGUAGE plpgsql VOLATILE
    • 1