WHERE-condition using information from array in a loop

Question

I would like to clean address data from the US. I want to impute state codes from a column (address) whenever the state code appears in the first two substrings (substring(address, 1, 2)). I built a foreach loop, looping through an array of US states. However, the condition where = substring(address, 1, 2) = field does not work.fieldis used in the loop: foreach field in array arr loop.

The whole code looks as follows:

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()
;

The loop works well in concat(address_, ' ', field) (tested and used in another procedure as well), but not in the where condition. Does anyone have an idea why and what could be done?

Hambone · Accepted Answer · 2017-12-01 20:10:23Z

1

It looks to me like the extra space in your array elements:

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

Are preventing this from ever being true:

substring(address, 1, 2)= field

substring (x, 1, 2) will always be two characters and will never match a three character field, which the array elements are.

So first off, can you strip those extra spaces out and try?

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

Secondly -- can you load the states into a table instead and use that instead of an array? Depending on what you do, it will likely come in handy more than once.

Third, if inventor and pat_no are the primary key for your table, then I think a single update statement might work better than a function/loop:

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')

If those aren't the primary key, then there may be unintended consequences with your update statement, but I don't know that for sure.

It's food for thought either way.

answered Dec 1, 2017 at 20:10

Hambone

16.5k8 gold badges54 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Florian Seliger Over a year ago

excellent answer. Thank you. Yes, indeed, I did not pay attention to the extra space, sorry, but I now use the 2nd approach. Any advantage of using "left" instead of "substring"?

Hambone Over a year ago

The only possible advantage of left over substring is less code and maybe better transparency to the reader that you are taking the left two characters. substring is pretty mainstream, so anyone familiar with SQL or computing in general should know what that means, and one might argue that it's more standard than is left. By the way, when I said left (address, 1, 2) that should be left (address, 2). I'm glad this was helpful. Yes, whenever you can do a single SQL transaction over a loop, you want to. Loops are widely used when not needed, so don't feel bad.

klin · Accepted Answer · 2017-12-01 23:03:25Z

1

You do not need loops nor plpgsql code for this update as the simple SQL statement does the job:

update de_inventor
set address_ = concat(address_, ' ', substring(address, 1, 2))
where ctry_code_inv = 'US' 
and substring(address, 1, 2) in ('AL', 'AK', 'WY'); -- list of all state codes here

In cases like this one an SQL approach is more efficient and simpler than loops in functions.

answered Dec 1, 2017 at 23:03

klin

123k15 gold badges240 silver badges262 bronze badges

1 Comment

Florian Seliger Over a year ago

excellent advice. Yes, I missed the most obvious thing to do because I was totally focused on using loops.

Collectives™ on Stack Overflow

WHERE-condition using information from array in a loop

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related