postgresql创建加密函数

加密函数

创建原理

-- 管理员登录创建，登录命令：D:\myinstall\PostgreSQL\10\bin>psql -U postgres -d tjbzdb
create extension pgcrypto;
--encrypt 加密函数 参数1:加密数据 参数2:签名密钥 参数3:加密方式
--encode 编码函数
--加密
select encrypt('lisi', 'test1','aes')
--编码
select encode(encrypt('lisi', 'test1','aes'), 'hex')
--解码
select decode('64b02855a6a9bbcbd6f96b2d0f6be8e3','hex')

--解密
select convert_from(decrypt(decode('64b02855a6a9bbcbd6f96b2d0f6be8e3','hex'),'test1','aes'),'SQL_ASCII')
select convert_from(decrypt(encrypt('lisi', 'test1','aes'),'test1','aes'),'SQL_ASCII')

资料

函数：
解密：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')

加密函数

create or replace function jia_mi (dataStr varchar,miYao varchar)
returns varchar as $$

	declare
	dataEncrypt varchar(255); --加密后数据
begin 	
	  select encode(encrypt(cast(dataStr as  bytea), cast(miYao as bytea),'aes'), 'hex')  into dataEncrypt;
		return dataEncrypt;
end;
$$ language plpgsql; 

解密函数

create or replace function jie_mi (dataStr varchar,miYao varchar)
returns varchar as $$

	declare
	dataDecrypt varchar(255); --加密后数据
begin 	
	 select convert_from(decrypt(cast(decode(dataStr,'hex') as bytea),cast(miYao as bytea),'aes'),'SQL_ASCII') into dataDecrypt;
		
		return dataDecrypt;
end;
$$ language plpgsql;