The ALTER PROCEDURE command

Use the ALTER PROCEDURE command to change a stored procedure.

You can change the return value, execution setting, owner, and body of a procedure, but you cannot change the name or argument list with this command. You can add or remove the VARARGS value in an otherwise empty argument list.

To change the name or argument list of a stored procedure, you must drop the procedure and create a procedure with the new name or argument type list.

Synopsis

ALTER PROCEDURE <name> (<args>) [ RETURNS <type>]
[ EXECUTE AS OWNER | EXECUTE AS CALLER ] [AS <procedure_body>];
ALTER PROCEDURE <name> (<args>) OWNER TO <user>;

Inputs

The ALTER PROCEDURE command takes the following inputs:

Table 1. ALTER PROCEDURE input
Input Description
name The name of the stored procedure that you want to change. You cannot change the name of the procedure. The procedure must be defined in the database to which you are connected.

For systems that support multiple schemas, you can specify a name in the format schema.procedure to change a procedure in a different schema of the current database.
args A list of input argument data types for the stored procedure. You can also specify the VARARGS value to create a variable argument procedure where users can input up to 64 values of any supported data type. VARARGS is a mutually exclusive value; you cannot specify any other arguments in the list.

You cannot change the argument list or sizes. You can remove VARARGS from the argument list, or add it to an otherwise empty argument list.
RETURNS <type> Specifies the type of data returned by the procedure. The <type> value can be a Netezza Performance Server data type or the value REFTABLE (<table-name>) to indicate that it returns a result set that looks like the specified table. The table must exist, and it continues to exist after the procedure. The table can be empty, but it must exist in the database.
EXECUTE AS OWNER If specified, the stored procedure uses the procedure owner ID for all access control and permission checks. This is the default.
EXECUTE AS CALLER If specified, the stored procedure uses the ID of the user who called the procedure for all access control and permission checks.
procedure_body Specifies the text or body of the procedure. The body must be enclosed with single quotation marks or enclosed by a BEGIN_PROC/END_PROC pair.

When you alter the procedure, you can obfuscate the body to mask the content from users who have permission to show the procedure.

Outputs

The ALTER PROCEDURE command has the following output

Table 2. ALTER PROCEDURE Output
Output Description
ALTER PROCEDURE The message that the system returns if the command is successful.
ERROR: replacing procedure: permission denied. The message indicates that the user does not have Alter permission on the procedure.
Error: FunctionAlter: existing UDX NAME(ARGS) differs in size of string/numeric arguments This error indicates that a stored procedure exists with the name but has different sizes specified for string or numeric arguments.

To alter the stored procedure, make sure that you specify the exact argument type list with correct sizes.
ERROR: FunctionAlter: function NAME does not exist with that signature This error indicates that the specified procedure name does not exist in the database.
ERROR: Can't specify arguments to a varargs procedure You cannot specify both the VARARGS value and any other argument value in the arguments list. The VARARGS value is mutually exclusive.

Description

You cannot alter a stored procedure that is currently in use in an active query. After the transaction completes for an active query, the Netezza Performance Server system processes the ALTER PROCEDURE command.

Privileges required
To alter a procedure, you must meet one of the following criteria:
  • You must have the Alter privilege on the PROCEDURE object.
  • You must have the Alter privilege on the specific procedure.
  • You must own the procedure.
  • You must be the database admin user or own the current database or the current schema on systems that supports multiple schemas.
Common tasks
You can use the ALTER PROCEDURE command to change the execution user ID of the procedure, its return value, or the procedure body itself.
You can also use the ALTER PROCEDURE command to change the owner of a procedure as follows:
ALTER PROCEDURE <name> (<arguments>) OWNER TO <name>;

Usage

To change the execution ID from over to caller, enter: 
   system(admin)=> ALTER PROCEDURE myproc(int4) EXECUTE AS CALLER;

To change the owner for a procedure in a different schema, enter: 

MYDB.MYSCH(USER)=> ALTER PROCEDURE schtwo.myproc(int4) OWNER TO
user2;