Altering stored procedures

You can alter a stored procedure and retain its grants with ALTER PROCEDURE.

Examples

The examples below use the following procedure:

=> CREATE PROCEDURE echo_integer(IN x int) LANGUAGE PLvSQL AS $$
BEGIN
    RAISE INFO 'x is %', x;
END;
$$;

By default, stored procedures execute with the privileges of the caller (invoker), so callers must have the necessary privileges on the catalog objects accessed by the stored procedure. You can allow callers to execute the procedure with the privileges, default roles, user parameters, and user attributes (RESOURCE_POOL, MEMORY_CAP_KB, TEMP_SPACE_CAP_KB, RUNTIMECAP) of the definer by specifying DEFINER for the SECURITY option.

To execute the procedure with privileges of the...

• Definer (owner):

  => ALTER PROCEDURE echo_integer(int) SECURITY DEFINER;
  

• Invoker:

  => ALTER PROCEDURE echo_integer(int) SECURITY INVOKER;
  

To change a procedure's source code:

=> ALTER PROCEDURE echo_integer(int) SOURCE TO $$
    BEGIN
        RAISE INFO 'the integer is: %', x;
    END;
$$;

To change a procedure's owner (definer):

=> ALTER PROCEDURE echo_integer(int) OWNER TO u1;

To change a procedure's schema:

=> ALTER PROCEDURE echo_integer(int) SET SCHEMA s1;

To rename a procedure:

=> ALTER PROCEDURE echo_integer(int) RENAME TO echo_int;