ALTER PROCEDURE

Function Description

Alters the attributes of a customized stored procedure.

Precautions

Compared with the original openGauss, Dolphin modifies the ALTER PROCEDURE syntax as follows:

1. The modifiable LANGUAGE option is added.

2. The modifiable item { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } is added.

3. The modifiable item SQL SECURITY { DEFINER | INVOKER } is added.

Syntax

• Modify the additional parameters of the customized stored procedure.

  ALTER PROCEDURE procedure_name ( [ { [ argname ] [ argmode ] argtype} [, ...] ] )
      action [ ... ] [ RESTRICT ];
  

  The syntax of the action clause is as follows:

  {CALLED ON NULL INPUT  | STRICT}
   | {IMMUTABLE | STABLE | VOLATILE}
   | {SHIPPABLE | NOT SHIPPABLE}
   | {NOT FENCED | FENCED}
   | [ NOT ] LEAKPROOF
   | { [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER }
   | AUTHID { DEFINER | CURRENT_USER }
   | COST execution_cost
   | ROWS result_rows
   | SET configuration_parameter { { TO | = } { value | DEFAULT }| FROM CURRENT}
   | RESET {configuration_parameter | ALL}
   | COMMENT 'text'
   | LANGUAGE lang_name
   | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  

Parameter Description

• LANGUAGE lang_name

  Name of the language used to implement the stored procedure. This parameter is compatible only with the syntax and has no actual effect.

• SQL SECURITY INVOKER

​ Specifies that the stored procedure is to be executed with the permissions of the user that calls it. This parameter can be omitted.

​ The functions of SQL SECURITY INVOKER and SECURITY INVOKER and AUTHID CURRENT_USER are the same.

• SQL SECURITY DEFINER

  Specifies that the stored procedure is to be executed with the privileges of the user that created it.

  The functions of SQL SECURITY DEFINER and AUTHID DEFINER and SECURITY DEFINER are the same.

• CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA

  Syntax compatibility item.

Example

    --Specify NO SQL.
    openGauss=# ALTER PROCEDURE proc1() NO SQL;

    --Specify CONTAINS SQL.
    openGauss=# ALTER PROCEDURE proc1() CONTAINS SQL;

    --Specify LANGUAGE SQL.
    openGauss=# ALTER PROCEDURE proc1() CONTAINS SQL LANGUAGE SQL ;

    --Specify MODIFIES SQL DATA.
    openGauss=# ALTER PROCEDURE proc1() CONTAINS SQL MODIFIES SQL DATA;

    --Specify SECURITY INVOKER.
    openGauss=# ALTER PROCEDURE proc1() SQL SECURITY INVOKER;

Helpful Links

ALTER PROCEDURE