如何解决这个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我已经尝试了下面提到的代码没有加入，它的工作正常，我需要一个额外的列，并使用加入，我只看到这个错误。
下面是我的完整代码
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()
1条答案
按热度按时间lztngnrs1#
我为一个非常相似的问题挣扎了一个小时。显然，即使你不传递null值，SQL Optimizer也一定发生了一些需要null检查的事情：
尝试以下操作：
变量l_columns用于formatter，因此在传递给formatter之前应该进行null检查。