Postgresql:null值不能格式化为SQL标识符

Question

如何在尝试选择我的函数时解决此ERROR: null values cannot be formatted as an SQL identifier：

select ws_sls_core.ars_pricing_test()
ERROR:  null values cannot be formatted as an SQL identifier
CONTEXT:  SQL statement "select string_agg(distinct format('(props ->> %L) as %I', w_order_line_d.matl_grp2_desc, w_order_line_d.matl_grp2_desc), ', ')
from ws_sls_core.w_support_pricing_d 
left join ws_sls_core.w_order_line_d 
on w_support_pricing_d.svc_pricing_type = w_order_line_d.matl_grp2_cd"
PL/pgSQL function ars_pricing_test() line 6 at SQL statement

我已经检查过了，使用中的查询没有生成任何NULL

select * from ws_sls_core.w_support_pricing_d where svc_pricing_type is
null

我尝试了下面提到的没有JOIN的代码，它运行得很好，我需要一个额外的列，必须使用JOIN，我只看到这个错误。

这是我的完整代码

CREATE OR REPLACE FUNCTION ws_sls_core.ars_pricing_test(
)
RETURNS boolean
LANGUAGE 'plpgsql'
COST 100
VOLATILE 
AS $BODY$
declare
l_sql text;
l_columns text;
begin
select string_agg(distinct format('(props ->> %L) as %I', w_order_line_d.matl_grp2_desc, w_order_line_d.matl_grp2_desc), ', ')
into l_columns
from ws_sls_core.w_support_pricing_d 
left join ws_sls_core.w_order_line_d 
on w_support_pricing_d.svc_pricing_type = w_order_line_d.matl_grp2_cd;
--     and A.svc_pricing_type is not null;

l_sql := 
'create or replace view ars_pricing_test as 
select w_support_pricing_d.item_num, '||l_columns||' 
from (
select w_support_pricing_d.item_num, json_object_agg(w_order_line_d.matl_grp2_desc,w_support_pricing_d.mnth_maint_price) as props
from ws_sls_core.w_support_pricing_d 
left join ws_sls_core.w_order_line_d 
on w_support_pricing_d.svc_pricing_type = w_order_line_d.matl_grp2_cd
group by w_support_pricing_d.item_num 
) t';
execute l_sql;
return true;
end;
$BODY$;
ALTER FUNCTION ws_sls_core.ars_pricing_test()

Answer 1

我为一个非常相似的问题挣扎了一个小时。显然，即使您不传递null值，SQL Optimizer中也一定发生了一些需要null检查的事情：

尝试以下操作：

IF l_columns IS NOT NULL
THEN
l_sql := 
'create or replace view ars_pricing_test as 
select w_support_pricing_d.item_num, '||l_columns||' 
from (
select w_support_pricing_d.item_num, json_object_agg(w_order_line_d.matl_grp2_desc,w_support_pricing_d.mnth_maint_price) as props
from ws_sls_core.w_support_pricing_d 
left join ws_sls_core.w_order_line_d 
on w_support_pricing_d.svc_pricing_type = w_order_line_d.matl_grp2_cd
group by w_support_pricing_d.item_num 
) t';
execute l_sql;
return true;
END IF;

变量l_columns被用于格式化程序，因此在传递到格式化程序之前，它应该是一个null检查。