Alter a stored procedure

After you define a stored procedure on the system, you can use the ALTER PROCEDURE command to change some aspects of the procedure.

You can modify a stored procedure to change the following aspects of the procedure:
  • RETURNS value
  • Execution user property (EXECUTE AS OWNER versus EXECUTE AS CALLER)
  • The body of the procedure
  • The owner of the procedure

You cannot change the procedure name or argument type list. You must drop the existing procedure and create a procedure with the new name and argument type list.

For example, the following sample commands can be used to change the “customer” procedure. To change the return value type, use a command similar to the following example:
TEST.TESTSCH(USR)=> ALTER PROCEDURE customer() RETURNS INT8;
ALTER PROCEDURE
To change the owner of the procedure to user, use a command similar to the following example:
TEST.TESTSCH(USR)=> ALTER PROCEDURE customer() OWNER TO user ;
ALTER PROCEDURE
To change the user execution property to EXECUTE AS CALLER, use a command similar to the following example:
TEST.TESTSCH(USR)=> ALTER PROCEDURE customer() EXECUTE AS CALLER;
ALTER PROCEDURE
To change the procedure definition, use a command similar to the following example:
TEST.TESTSCH(USR)=> ALTER PROCEDURE customer() AS
BEGIN_PROC
  BEGIN
    RAISE NOTICE 'The customer name is beta';
  END;
END_PROC;
ALTER PROCEDURE