SUMMARY: This article gives five examples of converting procedures from Oracle’s PL/SQL procedural language into PostgreSQL’s PL/pgSQL language.
1. Printing “Hello World” with a procedure
2. Using argument modes IN and INOUT
3. Using cursors in procedures when fetching more than one row
4. Using commit/rollback inside a procedure
5. Using procedures containing EXECUTE IMMEDIATE statements
In this post, we are going to see how we can easily convert Oracle procedures into PostgreSQL procedures. As we all know, PL/SQL is an Oracle procedural language and PL/pgSQL (Procedural Language/PostgreSQL) is a PostgreSQL procedural language where you can perform more complex tasks than in SQL—like easy computation—and also make use of loops, functions, and triggers. PL/pgSQL code is managed in blocks (block structured code) into which anonymous blocks, functions, and procedures are organized.
For companies who want to move from Oracle to PostgreSQL, changing their application which is written/developed in PL/SQL language to PL/PGSQL is a challenge. Here we are going to see some code which is written in PL/SQL and how we convert that into PL/pgSQL without much effort.
PostgreSQL started supporting procedure support with version 11. The CALL command is used to invoke a procedure.
Example 1: Printing “Hello World” with a procedure
An Oracle-style (PL/SQL) procedure for printing “hello world” would look like this:
SQL> create or replace procedure pro1
as
begin
dbms_output.put_line('Hello World');
end;
/
Procedure created.
SQL> exec pro1;
Hello World
PL/SQL procedure successfully completed.
We can convert this procedure into a PostgreSQL-style procedure (PL/pgSQL) like this:
postgres=# create or replace procedure pro1()
language plpgsql
as $$
begin
raise notice 'Hello world';
end;
$$;
CREATE PROCEDURE
postgres=# call pro1();
NOTICE: Hello world
CALL
Example 2: using argument modes IN and INOUT
Argument modes:
IN: Send values to program
OUT: Not currently supported for procedures
INOUT: Send and get values
Oracle-style procedure:
SQL> create or replace procedure pro2( n in int,n1 in out int)
as
begin
select 50 into n1 from dual where 5=n;
dbms_output.put_line('The result is ' || n1);
end;
/
Procedure created.
SQL> declare
x int;
begin
pro2(5,x);
end;
/
The result is 50
PL/SQL procedure successfully completed.
If we don’t specify the argument mode, then it will take IN by default.
PostgreSQL-style procedure:
postgres=# create or replace procedure pro2(n in int,n1 in out int)
language plpgsql
as $$
begin
select 50 into n1 where 5=n;
raise notice 'The result is %',n1;
end;
$$;
CREATE PROCEDURE
postgres=# do
$$
declare x int;
begin
call pro2(5,x);
end;
$$;
NOTICE: The result is 50
DO
Example 3: Using cursors in procedures, when fetching more than one row
Create table and insert rows into Oracle and PG databases:
create table tab1(x int,x1 int);
insert into tab1 values (1,1);
insert into tab1 values (2,2);
insert into tab1 values (3,3);
insert into tab1 values (4,4);
insert into tab1 values (5,5);
Oracle-style procedure:
SQL>Create or replace procedure pro3
Is
Cursor tt is select * from tab1;
Begin
For t in tt loop
dbms_output.put_line(t.x || t.x1);
End loop;
End;
/
Procedure created.
SQL>
SQL> exec pro3;
11
22
33
44
55
PL/SQL procedure successfully completed.
PostgreSQL-style procedure:
postgres=# Create or replace procedure pro3()
Language plpgsql as $$
Declare
tt cursor is select * from tab1;
Begin
For t in tt loop
raise notice '% %',t.x,t.x1;
End loop;
End;
$$;
CREATE PROCEDURE
postgres=#
postgres=# call pro3();
NOTICE: 1 1
NOTICE: 2 2
NOTICE: 3 3
NOTICE: 4 4
NOTICE: 5 5
CALL
postgres=#
Example 4: Using commit/rollback inside a procedure
Oracle-style procedure:
SQL> create or replace procedure pro4 as
n int;
begin
delete from tab1;
rollback;
select count(*) into n from tab1;
dbms_output.put_line(n); --should print 5 as rollback happened
delete from tab1;
commit;
select count(*) into n from tab1;
dbms_output.put_line(n); --0 record as commit happened
End;
/
Procedure created.
SQL> exec pro4;
5
0
PL/SQL procedure successfully completed.
PostgreSQL-style procedure:
postgres=# create or replace procedure pro4()
language plpgsql as $$
declare
n int;
begin
delete from tab1;
rollback;
select count(*) into n from tab1;
raise notice '%',n;
delete from tab1;
commit;
select count(*) into n from tab1;
raise notice '%',n;
end;
$$;
CREATE PROCEDURE
postgres=# call pro4();
NOTICE: 5
NOTICE: 0
CALL
postgres=#
Example 5: Using procedures containing EXECUTE IMMEDIATE statements
Oracle-style procedure:
SQL> create or replace procedure pro5 is
n varchar2(100);
begin
n:='create table test(n int)';
execute immediate n;
End;
Procedure created.
SQL> exec pro5;
PL/SQL procedure successfully completed.
SQL> desc test
Name Null? Type
----------------------------------------- -------- ----------------------------
N NUMBER(38)
SQL>
PostgreSQL-style procedure:
postgres=# create or replace procedure pro5()
language plpgsql as $$
declare
n varchar(100);
begin
n:='create table test(n int)';
execute n;
end;
$$;
postgres=# call pro5();
CALL
postgres=# \d test
Table "public.test"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
n | integer | | |
postgres=#
Please refer to the PostgreSQL documentation for more information on procedures:
https://www.postgresql.org/docs/12/sql-createprocedure.html
Please refer to the PostgreSQL documentation for more help with porting ORACLE PL/SQL:
https://www.postgresql.org/docs/12/plpgsql-porting.html
Hope it helps!