Skip to main content

ALTER PROCEDURE

Redefines a procedure.

Syntax​

ALTER PROCEDURE <name> [ ( [ [<arg_mode>] [<arg_name>] <arg_type> [, ...] ] ) ] 
   <action> [, ... ] [RESTRICT]

ALTER PROCEDURE <name> [ ( [ [<arg_mode>] [<arg_name>] <arg_type> [, ...] ] ) ]
   RENAME TO <new_name>

ALTER PROCEDURE <name> [ ( [ [<arg_mode>] [<arg_name>] <arg_type> [, ...] ] ) ]
   OWNER TO { <new_owner> | CURRENT_USER | SESSION_USER }

ALTER PROCEDURE <name> [ ( [ [<arg_mode>] [<arg_name>] <arg_type> [, ...] ] ) ]
   SET SCHEMA <new_schema>

ALTER PROCEDURE <name> [ ( [ [<arg_mode>] [<arg_name>] <arg_type> [, ...] ] ) ]
   DEPENDS ON EXTENSION <extension_name>

where <action> can be:

    { [EXTERNAL] SECURITY INVOKER | [EXTERNAL] SECURITY DEFINER }
    SET <config_param> { TO | = } { <value> | DEFAULT }
    SET <config_param> FROM CURRENT
    RESET <config_param>
    RESET ALL

Description​

You can run ALTER PROCEDURE to redefine the procedure that you own.

To change the schema of a procedure, you must have the CREATE privilege on the new schema.

To change the owner of a procedure, you must have the membership in the new owning role. The new owning role must have the CREATE privilege on the schema to which the procedure belongs.

note

Relyt has limitations on using STABLE and VOLATILE functions. For details, see CREATE FUNCTION.

Parameters​

  • <name>

    The name of the procedure. You can specify the name with the schema qualification. If the procedure contains no argument, its name must be unique in the schema to which it belongs.

  • <action>

    The action to perform on the procedure.

    For details about the description of supported actions, see section "Parameters" in ALTER FUNCTION.

  • <arg_mode>

    The mode of the argument, which can be IN or VARIADIC. If this parameter is not specified, the default value IN is used.

  • <arg_name>

    The new name of the argument.

    Note that ALTER PROCEDURE does not use argument names to identify the procedure.

  • <arg_type>

    The data type of the argument.

  • RESTRICT

    Ignored to keep conform to the SQL standard.

  • <new_name>

    The new name of the procedure.

  • <new_owner>, CURRENT_USER, or SESSION_USER

    The new owner of the procedure.

    • new_owner: explicitly specifies the new owning role.

      If the procedure is a SECURITY DEFINER, it will then run as the new owner.

    • CURRENT_USER: alters the owner to the current user.

    • SESSION_USER: alters the owner to the current session user.

  • <new_schema>

    The new schema of the procedure.

  • <extension_name>

    The name of the extension on which the procedure depends.

    If you run ALTER PROCEDURE ... DEPENDS ON EXTENSION, the procedure will be automatically dropped if the specified extension is dropped.

Examples​

Rename the procedure record with two arguments of types text and integer to insert_record:

ALTER PROCEDURE record(text, integer) RENAME TO insert_record;

Change the owner of the procedure insert_record with two arguments of types text and integer to [email protected]:

ALTER PROCEDURE insert_record(text, integer) OWNER TO "[email protected]";

To change the schema of the procedure insert_record with two arguments of types text and integer to public:

ALTER PROCEDURE insert_record(text, integer) SET SCHEMA public;

Mark the procedure insert_record of types text and integer as being dependent on the extension gp_exttable_fdw:

ALTER PROCEDURE insert_record(text, integer) DEPENDS ON EXTENSION gp_exttable_fdw;

Change the search path for procedure insert_record with two arguments of types text and integer to DEFAULT:

ALTER FUNCTION insert_record(text, integer) SET search_path to DEFAULT;

SQL standard compatibility​

ALTER PROCEDURE in Relyt is partially compatible with ALTER PROCEDURE in the SQL standard. Standard SQL ALTER PROCEDURE allows you to modify more properties of a procedure than ALTER PROCEDURE in Relyt, but ALTER PROCEDURE in Relyt allows you to alter the name, owner, schema, and volatility of a procedure, change the defaults of configuration parameters for a procedure, and make a procedure a security definer.