Converting an Oracle Stored Procedure to PostgreSQL

Question

I'm facing an Oracle to PostgreSQL migration: I managed to convert tables and views however I'm a bit stuck with Stored Procedures.

I've started with this one, which looks like this in Oracle:

CREATE OR REPLACE procedure
  update_role_func_def(function_id varchar2)
as
  cursor role_list_cursor is
   select r.id from amm_role r
    for update of r.id
   ;
begin
  for role_record in role_list_cursor
   loop
     insert into AMM_ROLE_FUNC_DEF (RID, FID, GRANT_FUNCT) values (role_record.id,    function_id, 'N');
  end loop;

  commit;
end update_role_func_def;

looking at the docs I managed to create this equivalent:

CREATE FUNCTION 
  update_role_func_def(function_id varchar)
  returns void 
as
$$
DECLARE
  cursor role_list_cursor is
    select r.id from amm_role r  for update of r.id  ;
begin
  for role_record in role_list_cursor
  loop
    insert into AMM_ROLE_FUNC_DEF (RID, FID, GRANT_FUNCT) values (role_record.id, function_id, 'N');
  end loop;

  commit;
end ;
$$

However entering this procedure in the PgAdmin results in a "Syntax Error, unexpected CREATE, expecting ';'

I feel a bit lost: is there any Postgres developer that can give me a clue if the procedure is syntactically correct ?

You can't use commit inside a function in Postgres. And you don't need the cursor at all (neither in Oracle nor in Postgres), this can be done with a single INSERT ... SELECT statement. And why do you use for update if you are not updating amm_role at all? — user330315
– user330315, Commented Mar 28, 2013 at 11:47
Do it with sql only (no declare, begin, end) as suggested by @a_horse unless there is more in the function that needs plpgsql. Declare the used language language sql or language plpgsql — Clodoaldo Neto
– Clodoaldo Neto, Commented Mar 28, 2013 at 11:52
Found this tool (online and offline) while searching for the same thing : sqlines.com — Benj
– Benj, Commented Mar 5, 2020 at 10:44

user330315 · Accepted Answer · 2013-03-28 11:53:49Z

4

Unless there is more to this than you are telling us, the following should work:

create or replace function update_role_func_def(function_id varchar)
  returns void 
as
$$
  insert into AMM_ROLE_FUNC_DEF (RID, FID, GRANT_FUNCT) 
  select r.id, function_id, 'N'
  from amm_role r;
$$
language sql;

Here is an SQLFiddle example: http://sqlfiddle.com/#!12/aed49/1

answered Mar 28, 2013 at 11:53

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

Tyler Foster Over a year ago

Thank you very much for your help. I really needed an initial bootstrap :-)

Collectives™ on Stack Overflow

Converting an Oracle Stored Procedure to PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related