I have PostgreSQL SQL that should look for a backslash in a column called source_username and if it finds the backslash, it should replace the current value of the source_username column with the same value without the characters before the backslash.

For example:

before source_username: domain\username

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

This SQL works fine when I have only one row in the table.

If I have multiple rows, I need to specify the row that I want to work with by adding where id = <id>

I wish to iterate all the relevant rows (all the rows that source_username contains backslash) and on each row to perform the SQL above.

I tried to do this with 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();

However, I get an error because I don't think it likes the 'with' statement.

Any idea how can I do it?

CodePudding user response：

It's far simpler than that. You need to use the SUBSTR and STRPOS functions. Take a look at the results of this query.

https://dbfiddle.uk/9-yPKn6E

with os_user (source_username) as (
  select 'domain\username'
  union select 'mydomain\joe'
  union select 'janet'
)
select u.source_username
, strpos(u.source_username, '\')
, substr(u.source_username, strpos(u.source_username, '\')   1)
from os_user u

What you need is:

UPDATE itpserver.managed_incidents
SET source_username  =                                             substr(source_username, strpos(source_username, '\')   1)
  , description      = replace(description      , source_username, substr(source_username, strpos(source_username, '\')   1))
  , additional_info  = replace(additional_info  , source_username, substr(source_username, strpos(source_username, '\')   1))
  , typical_behavior = replace(typical_behavior , source_username, substr(source_username, strpos(source_username, '\')   1))
  , raw_description  = replace(raw_description  , source_username, substr(source_username, strpos(source_username, '\')   1));

This is based on lengthy experience with SQL Server and some quick document searches for Postgresql. The UPDATE statement may not work as I expect.

CodePudding user response：

SQL by design/default works on complete data sets. It thus eliminates LOOPS entirely from the language - they are not needed. (Well not quite there are recursive queries). Your task is accomplished in a single update statement with a simple regular expression. See documentation String Functions:

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

Demo here.

Main Take away: Drop procedural logic terminology (for, loop, if then, ...) from your SQL vocabulary. (you choose alternatives with case.)