函数:
解密:convert_from(decrypt(cast(字段 as bytea),cast(秘钥 as bytea),'aes'),'SQL_ASCII')
加密:encode(encrypt(cast(#{字段,jdbcType=VARCHAR} as bytea),cast('秘钥' as bytea),'aes'),'escape')
示例:
解密:convert_from(decrypt(cast( info.sell_cert_no as bytea),cast('psbctjxykyx'as bytea),'aes'),'SQL_ASCII')as"sell_cert_no"
加密:encode(encrypt(cast(#{info.cardNum,jdbcType=VARCHAR} as bytea),cast('psbctjxykyx' as bytea),'aes'),'escape')
加密函数
createorreplacefunction jia_mi (dataStr varchar,miYao varchar)returnsvarcharas $$
declare
dataEncrypt varchar(255);--加密后数据beginselect encode(encrypt(cast(dataStr as bytea), cast(miYao as bytea),'aes'),'hex')into dataEncrypt;return dataEncrypt;end;
$$ language plpgsql;
解密函数
createorreplacefunction jie_mi (dataStr varchar,miYao varchar)returnsvarcharas $$
declare
dataDecrypt varchar(255);--加密后数据beginselect convert_from(decrypt(cast(decode(dataStr,'hex')as bytea),cast(miYao as bytea),'aes'),'SQL_ASCII')into dataDecrypt;return dataDecrypt;end;
$$ language plpgsql;