CREATE PROCEDURE Statement - IBM DB2 to PostgreSQL Migration

SQLines SQL Converter can help you convert IBM DB2 stored procedures to PostgreSQL:

IBM DB2:

  --#SET TERMINATOR /
 
  CREATE PROCEDURE sp_updateDeptLoc (IN p_loc VARCHAR(25), IN p_deptno CHARACTER(5)) 
  LANGUAGE SQL
  BEGIN 
    UPDATE dept SET location = p_loc WHERE deptno = p_deptno;
  END
  /

The corresponding code in PostgreSQL:

PostgreSQL:

  CREATE OR REPLACE PROCEDURE sp_updateDeptLoc (IN p_loc VARCHAR(25), IN p_deptno CHARACTER(5)) 
  AS $$
  BEGIN 
    UPDATE dept SET location = p_loc WHERE deptno = p_deptno;
  END;
  $$ LANGUAGE plpgsql;

CREATE PROCEDURE statement is available since PostgreSQL 11 only so for compatibility with older PostgreSQL versions you can also convert it to CREATE FUNCTION as follows:

PostgreSQL:

  CREATE OR REPLACE FUNCTION sp_updateDeptLoc (IN p_loc VARCHAR(25), IN p_deptno CHARACTER(5)) 
  RETURNS VOID 
  AS $$
  BEGIN 
    UPDATE dept SET location = p_loc WHERE deptno = p_deptno;
  END;
  $$ LANGUAGE plpgsql;

Note that RETURNS VOID was added in CREATE FUNCTION.

For more information, see IBM DB2 to PostgreSQL Migration.