DECLARE Statement - IBM DB2 to PostgreSQL Migration

In DB2 the DECLARE statement allows you to declare variables, handlers, exception and cursors in stored procedures. You have to use it inside a BEGIN-END block, for example:

IBM DB2:

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

PostgreSQL requires you to put declarations into the DECLARE block that goes outside BEGIN-END block, the corresponding example:

PostgreSQL:

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

For more examples, please see IBM DB2 to PostgreSQL Migration Reference