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

返回所有表字段（PostgreSql 函数）

  • 772

如何使 postgresql 存储过程或函数返回表的所有列而无需指定它们？我想实现 Sql Server 查询 Sp 的行为。使用我显示的代码，它给了我错误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

1 Answers

  1. Best Answer

    而不是返回SETOF record，你应该返回SETOF <tu tabla>。此代码应该可以工作：

    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