迭代每一行并相应地执行更新

Question

我有PostgreSQL SQL，应该在列中查找反斜杠称为source_username，如果它找到反斜杠，它应该用相同的值替换source_username列的当前值，没有反斜杠之前的字符。

例如:

source_username: domainusername

source_username: username

with os_user as (
select source_username from itpserver.managed_incidents mi;
),
osUserWithoutDomain as (
select (
case when (select * from os_user) ilike '%\%' and (select position('-' in (select * from os_user))>= 1) and (select length((select * from os_user)) != (select position('-' in (select * from os_user))) + 1)
then (
select substring(
(select * from os_user),(select position('' in (select * from os_user)) + 1),(select length((select * from os_user)) - 1)
))
else ((select * from os_user))
end
)
)


UPDATE itpserver.managed_incidents SET source_username  = replace(source_username, (select * from os_user), (select * from osUserWithoutDomain)),
description  = replace(description , (select * from os_user), (select * from osUserWithoutDomain)),
additional_info  = replace(additional_info , (select * from os_user), (select * from osUserWithoutDomain)),
typical_behavior  = replace(typical_behavior , (select * from os_user), (select * from osUserWithoutDomain)),
raw_description  = replace(raw_description , (select * from os_user), (select * from osUserWithoutDomain));

当我在表中只有一行时，这个SQL工作得很好。

如果我有多行，我需要通过添加where id = <id>来指定我想要处理的行

我希望迭代所有相关的行(source_username包含反斜杠的所有行)，并在每一行上执行上面的SQL。

我试着用LOOP:

来做这个

create or replace function fetcher()
returns void as $$
declare 
emp record;
begin 
for emp in select * 
from itpserver.managed_incidents
order by id
limit 10
loop 

raise notice '%', emp.id;
<my sql> where id = emp.id
end loop;
end;
$$language plpgsql;

select fetcher();

然而，我得到一个错误，因为我不认为它喜欢'with'语句。

知道我该怎么做吗?

Answer 1

其实要简单得多。需要使用SUBSTR和STRPOS函数。看一下这个查询的结果。

https://dbfiddle.uk/9-yPKn6E

with os_user (source_username) as (
select 'domainusername'
union select 'mydomainjoe'
union select 'janet'
)
select u.source_username
, strpos(u.source_username, '')
, substr(u.source_username, strpos(u.source_username, '') + 1)
from os_user u

tbody> <<tr>珍妮特

source_username	strpos	substr
域用户名	7	用户名
	0	janet
mydomain 乔	9	乔

SQL在设计/默认情况下适用于完整的数据集。因此，它从语言中完全消除了循环——它们是不需要的。(并不是完全有递归查询)。您的任务可以通过一个简单正则表达式的更新语句来完成。参见文档String Functions:

update managed_incidents
set source_username = regexp_replace(source_username,'.*\(.*)','1');

演示。

主带走从SQL词汇表中删除过程逻辑术语(for、循环、if then、…)。(您可以选择使用大小写替代)