一、引出问题
pg数据在做字段连接时,会出现丢字段现象,情况如下
数据准备:
create table table_test(
id numeric,
name varchar,
datetime timestamp(6)
);
insert into table_test values (1,null,null);
insert into table_test values (null,'test',null);
insert into table_test values (3,'',null);
insert into table_test values (4,'',now());
--数据情况
select * from table_test;
利用常用的连接方式,得到结果
select id||name||datetime from table_test;
select concat(id,name,datetime) from table_test;
select concat_ws('|', id,name,datetime) from table_test;
通过查询结果可知,有null值的数据会出现缺省,例如第三个查询三个字段以‘|’分隔符,1、2、3三个结果出现缺失分割符,但是4中的数据缺失很完整,结论证明用 '' 去代替 null值可解决该问题,因为可构建函数来解决该问题。
二、解决问题
将各种类型入参分别构建函数,结果如下:
CREATE OR REPLACE FUNCTION public.f_2null(indata character varying)
RETURNS character varying
LANGUAGE plpgsql
AS $function$
DECLARE
outdate varchar;
BEGIN
select case when indata is null then '' else indata end into outdate;
return outdate;
END;
$function$
;
CREATE OR REPLACE FUNCTION public.f_2null(indata date)
RETURNS character varying
LANGUAGE plpgsql
AS $function$
DECLARE
outdate varchar;
BEGIN
select case when indata is null then '' else indata::varchar end into outdate;
return outdate;
END;
$function$
;
CREATE OR REPLACE FUNCTION public.f_2null(indata timestamp without time zone)
RETURNS character varying
LANGUAGE plpgsql
AS $function$
DECLARE
outdate varchar;
BEGIN
select case when indata is null then '' else indata::varchar end into outdate;
return outdate;
END;
$function$
;
CREATE OR REPLACE FUNCTION public.f_2null(indata timestamp with time zone)
RETURNS character varying
LANGUAGE plpgsql
AS $function$
DECLARE
outdate varchar;
BEGIN
select case when indata is null then '' else indata::varchar end into outdate;
return outdate;
END;
$function$
;
CREATE OR REPLACE FUNCTION public.f_2null(indata numeric)
RETURNS character varying
LANGUAGE plpgsql
AS $function$
DECLARE
outdate varchar;
BEGIN
select case when indata is null then '' else indata::varchar end into outdate;
return outdate;
END;
$function$
;
三、验证结果
select f_2null(id)||f_2null(name)||f_2null(datetime) from table_test;
select concat(f_2null(id),f_2null(name),f_2null(datetime)) from table_test;
select concat_ws('|', f_2null(id),f_2null(name),f_2null(datetime)) from table_test;
结果所有字段均已显现,达成目标!