Updated on 2025-09-04 GMT+08:00

IMMUTABLE

IMMUTABLE is an attribute used to declare that the result of a stored procedure is determined solely by input parameters and remains independent of the database status. In certain scenarios, stored procedures with the IMMUTABLE attribute may be optimized to execute only once, and improper use may lead to unexpected results.

When using stored procedures and functions with the IMMUTABLE attribute, you are advised to avoid accessing information in tables or databases to ensure that the behavior meets expectations. For details about attributes, see "SQL Reference > SQL Syntax > C > CREATE FUNCTION" in Developer Guide.

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); -- This is only an example and is not recommended.
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