transform oracle query to postgresql-CodePudding

community I am trying to pass a query from oracle to POSTGRESQL and it has not worked for me, how can I do it? the code is the following:

Oracle:

create function updateinsert
create cursor xx
select id 
  from "CCRs"  
  where status = 'POR REGULARIZAR' 
    and id in (221424,188790);
begin
open cursor xx
loop

update ccrs 
set status  ='ANULADO', 
    isActive = false,
    cancelationDate = now(),
    updatedAt= now()
where id = xx.id;

INSERT INTO public.CCRHistories
(status, createdAt, updatedAt, ccrId, userId)
VALUES('ANULADO', now(), now(),xx.id , 2438);

end loop;
end;

CodePudding user response：

I think that would be everything:

DO $$
declare
        registro record;

cur_id cursor for select id
from
    "CCRs"
where
    status = 'POR REGULARIZAR' and id in (204);
 begin
    open cur_id;

fetch cur_id
into
    registro;

while (found) loop

    update
    "CCRs"
set
    status = 'ANULADO',
    isActive = false,
    cancelationDate = now(),
    updatedAt = now()
where
    id = registro.id;

insert
    into
    CCRHistories
(status,
    createdAt,
    updatedAt,
    ccrId,
    userId)
values('ANULADO',
now(),
now(),
registro.id,
2438);

fetch cur_id
into
    registro;
end loop;
end $$ LANGUAGE 'plpgsql';

CodePudding user response：

No need for a LOOP or PL/pgSQL. This can be done with a single statement that uses a data modifying common table expression to copy the updated rows into the history table:

with updated as (
  update "CCRs" 
  set status = 'ANULADO', 
      isActive = false,
      cancelationDate = now(),
      updatedAt= now()
  where id = in (select id 
                 from "CCRs"  
                 where status = 'POR REGULARIZAR' 
                 and id in (221424,188790))
  returning id, status
)
INSERT INTO public.CCRHistories (status, createdAt, updatedAt, ccrId, userId)
select status, now(), now(), upd.id, 2438
from updated upd;

If "CCRs".id is unique, you can get rid of the subquery

with updated as (
  update "CCRs" 
  set status = 'ANULADO', 
      isActive = false,
      cancelationDate = now(),
      updatedAt= now()
  where id in (221424,188790)
    and status = 'POR REGULARIZAR' 
  returning id, status
)
INSERT INTO public.CCRHistories (status, createdAt, updatedAt, ccrId, userId)
select status, now(), now(), upd.id, 2438
from updated upd;