CODEWITHSUNDEEP
postgresql
Tyler Foster
Tyler Foster

Reputation: 61

Converting an Oracle Stored Procedure to PostgreSQL

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 ?

Upvotes: 3

Views: 9283

Answers (1)

user330315
user330315

Reputation:

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

Upvotes: 4

Related Questions