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!