postgresql 实现变量替换框架

替代变量

• psql自带的替代变量通过 \set 定义，通过 : 调用
• 匿名程序块和程序中不能识别替代变量

vardef表

drop table if exists vardef cascade;
create table vardef (
     varname varchar(63)  --变量名
    ,varvalue varchar(2000)  --变量值
    ,vardesc text  --变量说明
    ,createtime timestamp default current_timestamp  --变量创建时间
);
comment on table vardef is '变量定义表';
comment on column vardef.varname is '变量名';
comment on column vardef.varvalue is '变量值得定义';
comment on column vardef.vardesc is '变量描述';
comment on column vardef.createtime is '变量创建时间';

get_vardef 函数

create or replace function get_vardef(vardef_sql varchar) 
 returns varchar
 language plpgsql
as $function$ 
/*
 * 作者：v-yuzhenc
 * 功能：返回指定变量定义具体实时的值
 * vardef_sql：变量定义
 * */
declare 
    p_result varchar;
    p_vardef_sql varchar;
begin
    execute 'select '||vardef_sql into p_result;
       return p_result;
    exception when others then 
        return p_result;
end;
$function$
;

v_vardef视图

create or replace view v_vardef as
select 
     current_timestamp as currenttime
    ,varname
    ,get_vardef(varvalue) as varvalue
    ,vardesc
from vardef
;

comment on view v_vardef is '实时变量表';
comment on column v_vardef.currenttime is '当前时间';
comment on column v_vardef.varname is '变量名';
comment on column v_vardef.varvalue is '当前时间变量的值';
comment on column v_vardef.vardesc is '变量描述';

变量定义

insert into vardef (
     varname  --变量名
    ,varvalue  --变量值
    ,vardesc  --变量说明
) values (
     'today'
    ,$$to_char(current_date,'yyyymmdd')$$
    ,'今天'
);
insert into vardef (
     varname  --变量名
    ,varvalue  --变量值
    ,vardesc  --变量说明
) values (
     'tomorrow'
    ,$$to_char(current_date+1,'yyyymmdd')$$
    ,'明天'
);
insert into vardef (
     varname  --变量名
    ,varvalue  --变量值
    ,vardesc  --变量说明
) values (
     'yesterday'
    ,$$to_char(current_date-1,'yyyymmdd')$$
    ,'昨天'
);
insert into vardef (
     varname  --变量名
    ,varvalue  --变量值
    ,vardesc  --变量说明
) values (
     'thismonth'
    ,$$to_char(current_date,'yyyymm')$$
    ,'本月'
);
insert into vardef (
     varname  --变量名
    ,varvalue  --变量值
    ,vardesc  --变量说明
) values (
     'lastmonth'
    ,$$to_char(to_date(to_char(current_date,'yyyymm')||'01','yyyymmdd')-1,'yyyymm')$$
    ,'上月'
);
insert into vardef (
     varname  --变量名
    ,varvalue  --变量值
    ,vardesc  --变量说明
) values (
     'nextmonth'
    ,$$(to_char(current_date,'yyyymm')::numeric+1)::varchar$$
    ,'下个月'
);

每天生成变量文件

--每天00:05分生成一个变量文件
#! /bin/bash

# /home/postgres/create_dayvar.sh

psql <<EOF
--不显示角标
\pset footer
--不显示标题
\pset t
--输出今天的变量定义
\o ~/var/today.sql
select '\set '||varname||' '||varvalue from v_vardef; --查出的变量定义会存到上面指定的文件中
--备份今天的变量
\! cp ~/var/today.sql ~/var/`date '+%Y%m%d'`.sql
--退出
\q
EOF

chmod +x /home/postgres/create_dayvar.sh
crontab -e
5 0 * * * . /home/postgres/create_dayvar.sh >> /dev/null

测试

/home/postgres/create_dayvar.sh > /dev/null

psql <<EOF
\i ~/var/20220831.sql
select :today;
select :tomorrow;
select :yesterday;
select :thismonth;
select :lastmonth;
select :nextmonth;
\q
EOF