1.原SQL
CREATE FUNCTION wmspci_app.pro_p1()
RETURNS record
LANGUAGE 'plpgsql'
AS $BODY$
declare
v_rec record;
_c varchar;
v_sysdate timestamp;
v_cnt numeric(6);
sfc record;
v_sap_fact_no sap_dispatchingm_bak.ffact_no%type;
v_ffact_no dispatchingm_bak.ffact_no%type;
begin
raise notice 'now is %', clock_timestamp();
END;
$BODY$;
2.錯誤
输出错误如下:
ERROR: invalid type name "sap_dispatchingm.ffact_no%type"
LINE 18: v_sap_fact_no sap_dispatchingm.ffact_no%type;
3.解决方法
指定table所在的schema name
CREATE FUNCTION wmspci_app.pro_p1()
RETURNS record
LANGUAGE 'plpgsql'
AS $BODY$
declare
v_rec record;
_c varchar;
v_sysdate timestamp;
v_cnt numeric(6);
sfc record;
v_sap_fact_no wmspci_app.sap_dispatchingm_bak.ffact_no%type;
v_ffact_no wmspci_app.dispatchingm_bak.ffact_no%type;
begin
raise notice 'now is %', clock_timestamp();
END;
$BODY$;```
结果如下:
```bash
CREATE FUNCTION
Query returned successfully in 40 msec.