ALTER PROC

功能描述

修改自定义存储过程的属性。

注意事项

  • 本章节只包含shark新增的语法，原openGauss的语法未做删除和修改。原openGauss的ALTER PROCEDURE语法请参考章节ALTER PROCEDURE。
  • 新增支持通过ALTER PROC方式修改自定义存储过程的属性，功能和ALTER PROCEDURE方式保持一致。
  • ALTER PROCEDURE/PROC COMPILE仅在A库生效，在D库报错不支持。

语法格式

  • 修改自定义存储过程的附加参数。

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

其中附加参数action子句语法为:

1. {CALLED ON NULL INPUT | STRICT} 2. | {IMMUTABLE | STABLE | VOLATILE} 3. | {SHIPPABLE | NOT SHIPPABLE} 4. | {NOT FENCED | FENCED} 5. | [ NOT ] LEAKPROOF 6. | { [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER } 7. | AUTHID { DEFINER | CURRENT_USER } 8. | COST execution_cost 9. | ROWS result_rows 10. | SET configuration_parameter { { TO | = } { value | DEFAULT }| FROM CURRENT} 11. | RESET {configuration_parameter | ALL} 12. | COMMENT 'text' - 修改自定义存储过程的名称。

1. ALTER { PROCEDURE | PROC } proname ( [ { [ argname ] [ argmode ] argtype} [, ...] ] ) 2. RENAME TO new_name; - 修改自定义存储过程的所属者。

1. ALTER { PROCEDURE | PROC } proname ( [ { [ argname ] [ argmode ] argtype} [, ...] ] ) 2. OWNER TO new_owner; - 修改自定义存储过程的模式。

1. ALTER { PROCEDURE | PROC } proname ( [ { [ argname ] [ argmode ] argtype} [, ...] ] ) 2. SET SCHEMA new_schema; - 重编译存储过程。

1. ALTER { PROCEDURE | PROC } procedure_name COMPILE;

仅在A库生效，在D库报错不支持。

参数说明

  • PROC

新增通过ALTER PROC方式修改自定义存储过程的属性，功能和ALTER PROCEDURE方式保持一致。

示例

1. create schema test_proc;
2. set current_schema to test_proc;

4. create procedure p1()
5. is
6. begin
7. RAISE INFO 'call procedure: p1';
8. end;
9. /

11. create proc p2()
12. is
13. begin
14. RAISE INFO 'call procedure: p2';
15. end;
16. /

18. alter procedure p1() stable;
19. alter proc p2() stable;

21. select provolatile from pg_proc where proname = 'p1';
22. provolatile
23. -------------
24. s
25. (1 row)

27. select provolatile from pg_proc where proname = 'p2';
28. provolatile
29. -------------
30. s
31. (1 row)

33. alter procedure p1() rename to new_p1;
34. alter proc p2() rename to new_p2;

36. \df new_p1();
37. List of functions
38. Schema   |  Name  | Result data type | Argument data types |  Type  | fencedmode | propackage | prokind
39. -----------+--------+------------------+---------------------+--------+------------+------------+---------
40. test_proc | new_p1 | void             |                     | normal | f          | f          | p
41. (1 row)

43. \df new_p2();
44. List of functions
45. Schema   |  Name  | Result data type | Argument data types |  Type  | fencedmode | propackage | prokind
46. -----------+--------+------------------+---------------------+--------+------------+------------+---------
47. test_proc | new_p2 | void             |                     | normal | f          | f          | p
48. (1 row)

50. create user test_proc_user with password 'xxxxxxxx';
51. grant all privileges to test_proc_user;

53. alter procedure new_p1() owner to test_proc_user;
54. alter proc new_p2() owner to test_proc_user;

56. select usename from pg_user a, pg_proc b where a.usesysid = b.proowner and b.proname = 'new_p1';
57. usename
58. ----------------
59. test_proc_user
60. (1 row)

62. select usename from pg_user a, pg_proc b where a.usesysid = b.proowner and b.proname = 'new_p2';
63. usename
64. ----------------
65. test_proc_user
66. (1 row)

68. create schema new_schema;

70. alter procedure new_p1() set schema new_schema;
71. alter proc new_p2() set schema new_schema;

73. select nspname from pg_namespace a, pg_proc b where a.oid = b.pronamespace and b.proname = 'new_p1';
74. nspname
75. ------------
76. new_schema
77. (1 row)

79. select nspname from pg_namespace a, pg_proc b where a.oid = b.pronamespace and b.proname = 'new_p2';
80. nspname
81. ------------
82. new_schema
83. (1 row)

85. alter procedure new_p1 compile;
86. ERROR:  This operation is not supported.
87. alter procedure new_p1() compile;
88. ERROR:  This operation is not supported.
89. alter proc new_p2 compile;
90. ERROR:  This operation is not supported.
91. alter proc new_p2() compile;
92. ERROR:  This operation is not supported.

94. call new_schema.new_p1();
95. INFO:  call procedure: p1
96. new_p1
97. --------

99. (1 row)

101. call new_schema.new_p2();
102. INFO:  call procedure: p2
103. new_p2
104. --------

106. (1 row)

相关链接

ALTER PROCEDURE