postgresql 修改字符串，使用数组。

将LoadLocation.blockid 的第五位修改为D

OVERLAY(LoadLocation.blockid PLACING 'D' FROM 5 FOR 1)::VARCHAR AS blockid,

在函数中将筛选的数据放进数组

DECLARE
	WorkSiteList_Month VARCHAR[];  --定义数组

SELECT
	STRING_TO_ARRAY(STRING_AGG(BlockIDList.block_id||'',','), ',') INTO WorkSiteList_Month
FROM
(
	SELECT report.pf_Modular_GetBlockidByMonth(CurrentMonth, CurrentMonth) AS block_id
) AS BlockIDList;

函数中 将数组中的某个字符统一修改

DECLARE 
	WorkSite_List VARCHAR[];
SELECT STRING_TO_ARRAY(REPLACE(ARRAY_TO_STRING(worksite,','), 'D', 'L'), ',') INTO WorkSite_List;

这里将数组worksite 中的D 替换为 L

手动测试数组

WHERE OVERLAY(CompEval.mining_block PLACING 'L' FROM 5 FOR 1)::VARCHAR = ANY(ARRAY['1B14L011','1B14L012','1B14L022','1B14L076','1B14L077','1B15L064','1B15L068','2B29L021','2B29L025','2B29L026','2B29L027','2B29L028','2B29L030','2B29L032','2B29L039','2B29L045'])

like 多匹配

CompEval.category like ANY (ARRAY['LG%','MG%','HG%','VHG%'])