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