使用table

利用table

CREATE OR REPLACE FUNCTION "public"."f_get_appraisalindex" ( "o_indexid" VARCHAR )
-- 定义table
RETURNS TABLE (
"o_indexname" VARCHAR,
"o_standardrule" VARCHAR,
"o_standardscore" VARCHAR,
"o_losescore" NUMERIC,
"o_orgstructname" VARCHAR,
"o_appraisaldate" VARCHAR,
"o_periodictime" VARCHAR,
"o_periodictimelosescore" NUMERIC,
"o_evaluatereason" VARCHAR
) AS $BODY$
BEGIN
RETURN query
-- 字段一一映射
SELECT
tn_indexname,
tn_standardrule,
tn_standardscore,
tn_losescore,
tn_orgstructname,
tn_appraisaldate,
tn_periodictime,
tn_periodictimelosescore,
tn_evaluatereason
FROM
tn_appraisalindex
WHERE
tn_periodictimelosescore > 0
ORDER BY
tn_indexid;

END;

$BODY$ LANGUAGE plpgsql VOLATILE COST 100 ROWS 1000

扩展

如何定义有入参和出参的情况：可以不需要明确指出return的方式