Return All fields in the table (Function PostgreSql)

Question

Return All fields in the table (Function PostgreSql)

Navigation

#1 by (1 votes)

0

How can I make a postgresql function or stored procedure return all the columns of the table without having to specify them? I want to achieve the behavior of the Sql Server query Sp. With the code that I show, I get the error ERROR: 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 postgresql stored-procedure

asked by Ariel Octavio D'Alfeo 01.04.2016 в 20:29

source

1 answer

Estimated time with traffic Error: Using $ this when not in object context

score 1 · Accepted Answer

Instead of returning SETOF record you 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