arrays - WHERE条件在循环中使用数组中的信息

Question

我想清除来自美国的地址数据。每当状态代码出现在前两个子字符串 (substring(address, 1, 2)) 中时，我想从列(地址)中估算状态代码。我构建了一个 foreach 循环，遍历美国各州的数组。然而，条件where = substring(address, 1, 2) = field不起作用。field用于循环:foreach field in array arr loop 。

整个代码如下:

create or replace function imp() RETURNS VOID AS $$
declare field varchar;
declare arr varchar[] := array['AL ', 'AK ', 'AZ ', 'AR ', 'CA ', 'CO ', 'CT ', 'DE ', 'DC ', 'FL ', 'GA ', 'HI ', 'ID ', 'IL ', 'IN ', 'IA ', 'KS ', 'KY ', 'LA ', 'ME ', 'MD ', 'MA ', 'MI ', 'MN ', 'MS ', 'MO ', 'MT ', 'NE ', 'NV ', 'NH ', 'NJ ', 'NM ', 'NY ', 'NC ', 'ND ', 'OH ', 'OK ', 'OR ', 'PA ', 'RI ', 'SC ', 'SD ', 'TN ', 'TX ', 'UT ', 'VT ', 'VA ', 'WA ',  'WV ',  'WI ', 'WY '];
begin
foreach field in array arr LOOP
update DE_inventor t1 set address_=t2.address_ from (
select
concat(address_, ' ', field)

as address_, pat_no, inventor
from DE_inventor
where ctry_code_inv = 'US' and substring(address, 1, 2)= field
) as t2
where t1.pat_no = t2.pat_no and t1.inventor = t2.inventor;
END LOOP;
RETURN;
END;
$$ language 'plpgsql'; 
select imp()
;

循环在 concat(address_, ' ', field) 中运行良好(也在另一个过程中测试和使用过)，但在 where 条件下运行不佳。有谁知道为什么以及可以做什么？

Answer 1

在我看来，你的数组元素中有多余的空间:

declare arr varchar[] := array['AL ', 'AK ', 'AZ ',
                                  ^      ^      ^ those

正在阻止这一切成为现实:

substring(address, 1, 2)= field

substring (x, 1, 2) 将始终是两个字符，永远不会匹配数组元素所在的三个字符字段。

那么首先，你能去掉那些多余的空间并尝试吗？

create or replace function imp() RETURNS VOID AS $$
declare
  field varchar;
  arr varchar[] := array['AL', 'AK', 'AZ', ... 'WY'];
begin

其次——您能否将状态加载到表中并使用它而不是数组？根据您的工作，它可能会不止一次派上用场。

第三，如果 inventor 和 pat_no 是您表的主键，那么我认为单个更新语句可能比函数/循环更好:

update de_inventor
set address_ = address_ || ' ' || left (address, 1, 2)
where
  ctry_code_inv = 'US' and
  left (address, 2) in ('AL', 'AK', 'AZ', 'AR' .. 'WY')

如果这些不是主键，那么您的更新语句可能会产生意想不到的后果，但我不确定。

无论哪种方式都值得深思。