IMMUTABLE

IMMUTABLE是存储过程的一个属性，用于声明该存储过程的结果仅依赖于输入参数，而不依赖于数据库的当前状态。在某些场景下，使用 IMMUTABLE 属性的存储过程可能会被优化为仅执行一次，如果使用不当，可能会造成不符合预期的结果。

建议在使用 IMMUTABLE 属性的存储过程和函数时，避免访问表或数据库中的信息，以确保其行为符合预期。属性详情请参见《开发指南》中“SQL参考 > SQL语法 > C > CREATE FUNCTION”章节。

gaussdb=# create schema best_practices_for_procedure;
CREATE SCHEMA
gaussdb=# create table best_practices_for_procedure.tb1(a int, b int);
CREATE TABLE
gaussdb=# create or replace procedure best_practices_for_procedure.proc1(a int, b int) immutable as
begin
insert into best_practices_for_procedure.tb1 values(a, b); --仅做示例，不推荐使用
end;
/
CREATE PROCEDURE
gaussdb=# call best_practices_for_procedure.proc1(2, 5);
ERROR:  INSERT is not allowed in a non-volatile function
CONTEXT:  SQL statement "insert into best_practices_for_procedure.tb1 values(a, b)"
PL/pgSQL function best_practices_for_procedure.proc1(integer,integer) line 3 at SQL statement
gaussdb=# create or replace function best_practices_for_procedure.func1(a int, b int) return int immutable as
begin
return a * b;
end;
/
CREATE PROCEDURE
gaussdb=# call best_practices_for_procedure.func1(2, 5);
 func1
-------
    10
(1 row)

gaussdb=# drop schema best_practices_for_procedure cascade;
NOTICE:  drop cascades to 3 other objects
DETAIL:  drop cascades to table best_practices_for_procedure.tb1
drop cascades to function best_practices_for_procedure.proc1(integer,integer)
drop cascades to function best_practices_for_procedure.func1(integer,integer)
DROP SCHEMA