PostgreSQL 函数封装

背景

封装函数主要是为了隐藏函数体的内容(业务逻辑),这个在Oracle里面是通过package来实现的.

在PostgreSQL里面, 原生是不带这个功能的, 但是EnterpriseDB的AS版本支持这一操作,

通过edbwrap可以实现代码的封装.

另一方面，PostgreSQL可以找一下社区版本的函数封装插件。

例如 :

未封装的函数通过pg_proc.prosrc可以查看它的源码.

edb=# SELECT prosrc FROM pg_proc WHERE proname = 'list_emp';  
                            prosrc                              
--------------------------------------------------------------  
                                                                
     v_empno         NUMBER(4);                                 
     v_ename         VARCHAR2(10);                              
     CURSOR emp_cur IS                                          
         SELECT empno, ename FROM emp ORDER BY empno;           
 BEGIN                                                          
     OPEN emp_cur;                                              
     DBMS_OUTPUT.PUT_LINE('EMPNO    ENAME');                    
     DBMS_OUTPUT.PUT_LINE('-----    -------');                  
     LOOP                                                       
         FETCH emp_cur INTO v_empno, v_ename;                   
         EXIT WHEN emp_cur%NOTFOUND;                            
         DBMS_OUTPUT.PUT_LINE(v_empno || '     ' || v_ename);   
     END LOOP;                                                  
     CLOSE emp_cur;                                             
 END                                                            
(1 row)                                                         
  
edb=# quit  

使用edbwrap封装这个创建函数的文件

[bash] edbwrap -i listemp.sql                                          
EDB*Wrap Utility: Release 8.4.3.2  
  
Copyright (c) 2004-2010 EnterpriseDB Corporation.  All Rights Reserved.  
  
Using encoding UTF8 for input  
Processing listemp.sql to listemp.plb  
  
Examining the contents of the output file (listemp.plb) file reveals that the code is obfuscated:  

封装后就变成这样了.

[bash] cat listemp.plb   
$__EDBwrapped__$                       
UTF8                                   
d+6DL30RVaGjYMIzkuoSzAQgtBw7MhYFuAFkBsfYfhdJ0rjwBv+bHr1FCyH6j9SgH  
movU+bYI+jR+hR2jbzq3sovHKEyZIp9y3/GckbQgualRhIlGpyWfE0dltDUpkYRLN  
/OUXmk0/P4H6EI98sAHevGDhOWI+58DjJ44qhZ+l5NNEVxbWDztpb/s5sdx4660qQ  
Ozx3/gh8VkqS2JbcxYMpjmrwVr6fAXfb68Ml9mW2Hl7fNtxcb5kjSzXvfWR2XYzJf  
KFNrEhbL1DTVlSEC5wE6lGlwhYvXOf22m1R2IFns0MtF9fwcnBWAs1YqjR00j6+fc  
er/f/efAFh4=  
$__EDBwrapped__$  

使用封装后的代码创建函数

[bash] edb-psql edb  
Welcome to edb-psql 8.4.3.2, the EnterpriseDB interactive terminal.  
  
Type:  \copyright for distribution terms  
       \h for help with SQL commands  
       \? for help with edb-psql commands  
       \g or terminate with semicolon to execute query  
       \q to quit  
  
edb=# \i listemp.plb  
CREATE PROCEDURE  

那么再查看pg_proc.prosrc就变成封装后的代码了

edb=# SELECT prosrc FROM pg_proc WHERE proname = 'list_emp';  
                                    prosrc  
----------------------------------------------------------------  
 $__EDBwrapped__$  
 UTF8  
 dw4B9Tz69J3WOsy0GgYJQa+G2sLZ3IOyxS8pDyuOTFuiYe/EXiEatwwG3h3tdJk  
 ea+AIp35dS/4idbN8wpegM3s994dQ3R97NgNHfvTQnO2vtd4wQtsQ/Zc4v4Lhfj  
 nlV+A4UpHI5oQEnXeAch2LcRD87hkU0uo1ESeQV8IrXaj9BsZr+ueROnwhGs/Ec  
 pva/tRV4m9RusFn0wyr38u4Z8w4dfnPW184Y3o6It4b3aH07WxTkWrMLmOZW1jJ  
 Nu6u4o+ezO64G9QKPazgehslv4JB9NQnuocActfDSPMY7R7anmgw  
 $__EDBwrapped__$  
(1 row)  

参考

Postgres Plus Advanced Server Oracle Compatibility Developer’s Guide

http://www.enterprisedb.com/docs/en/9.1/oracompat/Postgres_Plus_Advanced_Server_Oracle_Compatibility_Guide-194.htm#P19537_909450

文章来源：https://billtian.github.io/digoal.blog/2012/06/06/01.html