Execute Stored Procedures - Oracle to PostgreSQL Migration

In Oracle, you can execute a stored procedure from another stored procedure just specifying its name and parameters. In PostgreSQL, you have to use the CALL statement.

Oracle:

  -- Sample procedure with parameters 
  CREATE OR REPLACE PROCEDURE sp1(param1 VARCHAR2, param2 VARCHAR2)
  IS
     var1 VARCHAR2(60);
  BEGIN
    var1 := param1 || ', ' || param2 || '!';
  END;
  /
 
  -- Sample procedure without parameters 
  CREATE OR REPLACE PROCEDURE sp2
  IS
     var1 VARCHAR2(60);
  BEGIN
    var1 := 'Hello, world!';
  END;
  /
 
  -- Executing procedures from another procedure
  CREATE OR REPLACE PROCEDURE sp3
  IS
  BEGIN
    sp1('Hello', 'world');
 
    -- You can call the procedure without parameters without or with ()
    sp2();
    sp2;
  END;
  /

PostgreSQL:

  -- Sample procedure with parameters 
  CREATE OR REPLACE PROCEDURE sp1(param1 VARCHAR(4000), param2 VARCHAR(4000))
  AS $$
  DECLARE
     var1 VARCHAR(60);
  BEGIN
    var1 := concat(param1 , ', ' , param2 , '!');
  END;
  $$ LANGUAGE plpgsql;
 
  -- Sample procedure without parameters 
  CREATE OR REPLACE PROCEDURE sp2()
  AS $$
  DECLARE
     var1 VARCHAR(60);
  BEGIN
    var1 := 'Hello, world!';
  END;
  $$ LANGUAGE plpgsql;
 
  -- Executing procedures from another procedure
  CREATE OR REPLACE PROCEDURE sp3()
  AS $$
  BEGIN
    CALL sp1('Hello', 'world');
 
    -- CALL keyword and () are required
    CALL sp2();
    CALL sp2();
  END;
  $$ LANGUAGE plpgsql;

Note that the CALL keyword was added before the procedure name, and () are required in PostgreSQL.

For more information, see Oracle to PostgreSQL Migration.