数据库_一些你可能不知道的操作

greenplum

创建一个加密函数  某个数值*20+19

CREATE OR REPLACE FUNCTION db_jcwzsj.intEncrypt(number int4)
RETURNS int AS $BODY$
SELECT $1*20+19
$BODY$
LANGUAGE sql;
 

使用   SELECT intencrypt(n_ztlx) from db_jcwzsj.t_zt  

数据采集工具的配置文件只能配置到数据库层面  但某个函数在某个模式下面(schema)

这时如果某sql报找不到该函数时  需要设置 set search_path=模式

如下

set SEARCH_PATH=db_zjksh;
select 
encode(ENCRYPT ( pg_catalog.decode( c_id, 'escape' ), 'xm' :: bytea, 'aes' :: TEXT ),'BASE64') as c_id,
encode(ENCRYPT ( pg_catalog.decode( c_mm, 'escape' ), 'xm' :: bytea, 'aes' :: TEXT ),'BASE64') as c_mm,
d_tjq,
encode(ENCRYPT ( pg_catalog.decode( c_ll, 'escape' ), 'xm' :: bytea, 'aes' :: TEXT ),'BASE64') as c_ll,
n_bb*20+19 as n_bb,
from ****_tmp;

转义查询 

在要进行转义的字符串整体前面 +E 在某字符需要转义前+/

SELECT
    *
FROM
    (
        SELECT
            encode(
                decrypt (
                    pg_catalog.decode (c_mm, 'BASE64'),
                    'xm' :: bytea,
                    'aes' :: TEXT
                ),
                'escape'
            ) AS xx
        FROM
            db_**.***_jm
    ) A
WHERE
    xx = E'\\347\\234\\201 \\351\\203\\250 \\347\\272\\247';  

新建UUID函数

查看GP 安装目录下是否有python文件夹 
cd $GPHOME/ext/ 后
ll -l
会出现
drwxr-xr-x 6 gpadmin gpadmin 56 7月  29 2017 python

如果没有python文件夹则不能使用该方式创建uuid函数

切换到gpadmin用户下
su  gpadmin

添加在对应库下添加python 支持  
createlang plpythonu -d 数据库
如 createlang plpythonu -d db_zjw

然后去对应数据库下新建查询 创建uuid函数 

create or replace function public.uuid_python() returns varchar(36)
AS $$  
        import uuid  
        return uuid.uuid1()  
$$ LANGUAGE plpythonu;  

然后就可以调用uuid_python函数了
select uuid_python()

新建加密函数转换字符串中特殊字符 \ 转换成 |+|

CREATE OR REPLACE FUNCTION public.varcharencrypt(strinig VARCHAR)
RETURNS VARCHAR AS $BODY$
SELECT encode(ENCRYPT (pg_catalog.decode (REPLACE ($1,'\','|+|'), 'escape' ), 'xm' :: bytea, 'aes' :: TEXT ),'BASE64') 
$BODY$
LANGUAGE sql;

新建解密函数 将字符串中特殊字符 |+| 转换成加密前的 \
CREATE OR REPLACE FUNCTION public.varchardecrypt(strinig VARCHAR)
RETURNS VARCHAR AS $BODY$
SELECT  REPLACE(encode(decrypt(pg_catalog.decode ($1, 'BASE64'),'xm' :: bytea,'aes' :: TEXT),'escape'),'|+|','\') 
$BODY$
LANGUAGE sql;

使用样例
 
加密 
SELECT 
public.varcharencrypt(x字段) as x字段
FROM xxx表

解密
SELECT
    public.varchardecrypt(x字段)
from xxx表

加密解密并用样例
SELECT
public.varchardecrypt(jm)
from (
SELECT 
public.varcharencrypt(c_tjdq) as jm
FROM
t_ajcfqk_nltjb
) x