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
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot