语法：
CREATE [OR REPLACE] FUNCTION function_name (arguments)
RETURNS return_datatype AS $variable_name$
DECLARE
declaration;
[...]
BEGIN
< function_body >
[...]
RETURN { variable_name | value }
END; LANGUAGE plpgsql;
参数说明
function_name：指定函数的名称。[OR REPLACE]：是可选的，它允许您修改/替换现有函数。RETURN：它指定要从函数返回的数据类型。它可以是基础，复合或域类型，或者也可以引用表列的类型。function_body：function_body包含可执行部分。plpgsql：它指定实现该函数的语言的名称
#时间函数
CREATE OR REPLACE FUNCTION "public"."addtime"(difftype varchar, incrementvalue int4, inputdate timestamptz) RETURNS "pg_catalog"."timestamp" AS $BODY$ DECLARE YEAR_CONST Char(15) := 'year'; MONTH_CONST Char(15) := 'month'; WEEK_CONST Char(15) := 'week'; DAY_CONST Char(15) := 'day'; HOUR_CONST Char(15) := 'hour'; MIN_CONST Char(15) :='mi'; SECOND_CONST Char(15) :='ss'; dateTemp timestamp; intervals interval; BEGIN IF lower($1) = lower(YEAR_CONST) THEN select cast(cast(incrementvalue as character varying) || ' year' as interval) into intervals; ELSEIF lower($1) = lower(MONTH_CONST) THEN select cast(cast(incrementvalue as character varying) || ' months' as interval) into intervals; ELSEIF lower($1) = lower(WEEK_CONST) THEN select cast(cast(incrementvalue as character varying) || ' week' as interval) into intervals; ELSEIF lower($1) = lower(DAY_CONST) THEN select cast(cast(incrementvalue as character varying) || ' day' as interval) into intervals; ELSEIF lower($1) = lower(HOUR_CONST) THEN select cast(cast(incrementvalue as character varying) || ' hour' as interval) into intervals; ELSEIF lower($1) = lower(MIN_CONST) THEN select cast(cast(incrementvalue as character varying) || ' minute' as interval) into intervals; ELSEIF lower($1) = lower(SECOND_CONST) THEN select cast(cast(incrementvalue as character varying) || ' second' as interval) into intervals; END IF; dateTemp:= inputdate + intervals; RETURN dateTemp; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100 ;
CREATE OR REPLACE FUNCTION "public"."end_purchase_price"(refcursor) RETURNS "pg_catalog"."refcursor" AS $BODY$ begin open $1 for SELECT p1.name_template,p2.material,p2.cust_spec,p0.price_unit::double precision,p3.name FROM purchase_origin_line p0 LEFT JOIN product_product p1 ON p0.product_id=p1.id LEFT JOIN product_template p2 ON p1.product_tmpl_id=p2.id LEFT JOIN product_uom p3 ON p2.uom_id=p3.id WHERE p2.active ='t' AND p0.id IN ( SELECT MAX(id) FROM purchase_origin_line where state<>'cancel' AND product_id >0 GROUP BY product_id ); return $1; end; $BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100 ;
CREATE OR REPLACE FUNCTION "public"."prod_line_zd"( refcursor, gsid int4, sd varchar, ed varchar, gxstateid int4, workname varchar, lotname varchar, ename varchar, pname varchar, pgg varchar, gxname varchar) RETURNS "pg_catalog"."refcursor" AS $BODY$ BEGIN if gxstateid=3 then open $1 for EXECUTE' select t2.sequence,to_char(timeadd(''hour'',8,t1.date),''yyyy-mm-dd''),t3.name_template,t4.material,t4.cust_spec,t0.name workname,t5.name lotname,t2.lqty_ok,t2.lkgweight,t2.lqty,t2.lqty_no,t7.login,t8.name,t6.sequence,t6.name,t6.produce_description,case t2.lstate when 1 then ''领'' else ''交'' end,t1.name,t9.name_template,t9.cust_spec,t2.lremark from mrp_production t0 left join mrp_production_report t1 on t0.id=t1.workname left join mrp_production_report_line t2 on t1.id=t2.line_id and t0.id=t2.lworkname left join product_product t3 on t0.product_id=t3.id left join product_template t4 on t3.product_tmpl_id=t4.id left join mrp_production_report_lot t5 on t5.id=t2.lot_id left join mrp_routing_workcenter t6 on t6.id=t2.lprocedure left join res_users t7 on t7.id=t2.lemployee left join res_partner t8 on t8.id=t7.partner_id left join (select t0.id,t2.name_template,t3.material,t3.cust_spec from mrp_bom t0 left join mrp_bom_line t1 on t0.id=t1.bom_id left join product_product t2 on t1.product_id=t2.id left join product_template t3 on t3.id=t2.product_tmpl_id)t9 on t9.id=t0.bom_id where t0.company_id='|| gsid ||' and timeadd(''hour'',8,t1.date)>='''|| sd ||''' and timeadd(''hour'',8,t1.date)<='''|| ed ||''' and exists(select 1 from mrp_production_report where workname=t0.id) and t0.state<>''cancel'' and t0.name like ''%'|| workname ||'%'' and t5.name like ''%'|| lotname ||'%'' and t8.name like ''%'|| ename ||'%'' and t3.name_template like ''%'|| pname ||'%'' and t4.cust_spec like ''%'|| pgg ||'%''and t6.name like ''%'|| gxname ||'%'' order by t1.date,t1.name,t2.sequence;'; ELSE open $1 for EXECUTE' select t2.sequence,to_char(timeadd(''hour'',8,t1.date),''yyyy-mm-dd''),t3.name_template,t4.material,t4.cust_spec,t0.name workname,t5.name lotname,t2.lqty_ok,t2.lkgweight,t2.lqty,t2.lqty_no,t7.login,t8.name,t6.sequence,t6.name,t6.produce_description,case t2.lstate when 1 then ''领'' else ''交'' end,t1.name,t9.name_template,t9.cust_spec,t2.lremark from mrp_production t0 left join mrp_production_report t1 on t0.id=t1.workname left join mrp_production_report_line t2 on t1.id=t2.line_id and t0.id=t2.lworkname left join product_product t3 on t0.product_id=t3.id left join product_template t4 on t3.product_tmpl_id=t4.id left join mrp_production_report_lot t5 on t5.id=t2.lot_id left join mrp_routing_workcenter t6 on t6.id=t2.lprocedure left join res_users t7 on t7.id=t2.lemployee left join res_partner t8 on t8.id=t7.partner_id left join (select t0.id,t2.name_template,t3.material,t3.cust_spec from mrp_bom t0 left join mrp_bom_line t1 on t0.id=t1.bom_id left join product_product t2 on t1.product_id=t2.id left join product_template t3 on t3.id=t2.product_tmpl_id)t9 on t9.id=t0.bom_id where t0.company_id='|| gsid ||' and timeadd(''hour'',8,t1.date)>='''|| sd ||''' and timeadd(''hour'',8,t1.date)<='''|| ed ||''' and t2.lstate='|| gxstateid ||' and exists(select 1 from mrp_production_report where workname=t0.id) and t0.state<>''cancel'' and t0.name like ''%'|| workname ||'%'' and t5.name like ''%'|| lotname ||'%'' and t8.name like ''%'|| ename ||'%'' and t3.name_template like ''%'|| pname ||'%'' and t4.cust_spec like ''%'|| pgg ||'%''and t6.name like ''%'|| gxname ||'%'' order by t1.date,t1.name,t2.sequence;'; end if; return $1; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100 ;