存货总线数量明细总账设计（plpgsql）

CREATE OR REPLACE FUNCTION public.x9_zxslmx(
  ssksny character,
  ssjsny character,
  sscllb character varying)
    RETURNS TABLE(chxh character varying, chdh character varying, chmc character varying, xh1 character varying, xh2 character varying, xh3 character varying, gg1 character varying, jldw character varying, xqsl numeric, jhsl numeric, srsl numeric, fcsl numeric, jcsl numeric, "链接功能" character varying) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000
AS $BODY$
-- description: 总账查询：存货总线数量明细总账
DECLARE
  ssljzd varchar(200);
BEGIN
  ssljzd:='存货总线数量明细账';
  RETURN query
  WITH mxzz (chxh,chdh,chmc,xh1,xh2,xh3,gg1,jldw,xqsl,jhsl,srsl,fcsl,jcsl) AS
  (SELECT xm.chxh, xm.chdh, xm.chmc, xm.xh1, xm.xh2, xm.xh3, xm.gg1, xm.jldw,
    sum(zz.xqsl) AS xqsl, sum(zz.jhsl) AS jhsl, sum(zz.srsl) AS srsl,
    sum(zz.fcsl) AS fcsl,
    sum(CASE zz.ny WHEN $2 THEN zz.jcsl ELSE 0 END) AS jcsl
  FROM utzzch AS zz INNER JOIN mlchxm AS xm ON zz.chxh = xm.chxh
  WHERE (xm.mllb = $3 OR $3 = '') AND
      (zz.ny BETWEEN $1 AND $2)
  GROUP BY xm.chxh, xm.chdh, xm.chmc, xm.xh1, xm.xh2, xm.xh3, xm.gg1, xm.jldw)
  SELECT q.chxh,q.chdh,q.chmc,q.xh1,q.xh2,q.xh3,q.gg1,q.jldw,q.xqsl,q.jhsl,q.srsl,
      q.fcsl,q.jcsl,ssljzd AS 链接功能 FROM mxzz q 
  ORDER BY q.chdh,q.chxh;
END;
$BODY$;