Question 90185 in PostgreSQL Question posted in
The official documentation can be found here.

Iterate each row and perform update accordingly – Postgresql

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: domainusername

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?

Tags:

2

Answers


  1. 0

    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 '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
    source_username strpos substr
    domainusername 7 username
    janet 0 janet
    mydomainjoe 9 joe

    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.

    Login or Signup to reply.
  2. 0

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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search