Create obfuscated procedures by using a wrapping procedure

A common way to create an obfuscated stored procedure is by creating a stored procedure “wrapper” that takes the procedure definition and the procedure body, and creates the obfuscated procedure, as in the following example:

TEST.TESTSCH(USR)=> CREATE OR REPLACE PROCEDURE wrap_proc(text, text)
RETURNS BOOL LANGUAGE NZPLSQL AS 
BEGIN_PROC
  DECLARE
    proc alias for $1;
    body alias for $2;
    enc text;
    sql text;
    BEGIN
      enc := wrap_nzplsql(body);
      sql := proc || '' || '' || quote_literal(enc) || '';
      RAISE NOTICE '%;', sql;
      EXECUTE IMMEDIATE sql;
      return true;
    END;
END_PROC;
CREATE PROCEDURE

Call the wrap_proc() procedure and specify the CREATE OR REPLACE main definition in the first input value; then specify the BEGIN PROC/END PROC contents in the second input value. You must surround the main definition (the first input value) with single quotation marks. Do not enclose the second input value (the procedure body) in single quotation marks because the wrap_nzplsql() built-in procedure takes the text as it would be specified for a CREATE OR REPLACE PROCEDURE command. An example follows:

TEST.TESTSCH(USR)=> CALL wrap_proc('CREATE OR REPLACE PROCEDURE customer() 
RETURNS INT4 LANGUAGE NZPLSQL AS', 
BEGIN_PROC
    BEGIN
        RAISE NOTICE 'The customer name is alpha';
    END;
END_PROC);
NOTICE:  CREATE OR REPLACE PROCEDURE customer() RETURNS INT4 LANGUAGE 
NZPLSQL 
AS'TlpQU1FMV1JBUDEwWWk5NUhrQzVhR0xyRFRxTWR3VE5sQT09JEdFQ1B5LzVkSU1KMTI
1a0dUK3NTWjlkb3ZGL3ZHalhpVExPVG5UajRkK3gxSkxvZVhKejdZQmJOVHN0aU1waFRlb
mhoaWtYdHJUTVkKUUNrWDY5Nko5Rms2NlBIYUxra21xeWNZYXdWclBCQT0=';
 wrap_proc
-----------
 t
(1 row)

When you call an obfuscated procedure, the system uses internal routines to “read” the obfuscated body text and run the procedure. The behavior and output of the obfuscated procedure is identical to a cleartext version of the procedure, for example:

TEST.TESTSCH(USR)=> CALL customer();
NOTICE:  The customer name is alpha
 customer
----------
(1 row)