transform oracle query to postgresql

Question

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;

user330315 · Accepted Answer · 2022-08-12 05:12:31Z

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;

Daniel Hidalgo · Accepted Answer · 2022-08-12 02:20:29Z

0

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

answered Aug 12, 2022 at 2:20

Daniel Hidalgo

32 bronze badges

1 Comment

Community Over a year ago

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

transform oracle query to postgresql

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related