函数稳定性参数的三种状态 IMMUTABLE,STABLE,VOLATILE

函数稳定性参数的三种状态 IMMUTABLE,STABLE,VOLATILE
IMMUTABLE
STABLE
VOLATILE
These attributes inform the query optimizer about the behavior of the function. At most one choice can be specified. If none of these appear, VOLATILE is the default assumption.

IMMUTABLE indicates that the function cannot modify the database and always returns the same result when given the same argument values; that is, it does not do database lookups or otherwise use information not directly present in its argument list. If this option is given, any call of the function with all-constant arguments can be immediately replaced with the function value.

STABLE indicates that the function cannot modify the database, and that within a single table scan it will consistently return the same result for the same argument values, but that its result could change across SQL statements. This is the appropriate selection for functions whose results depend on database lookups, parameter variables (such as the current time zone), etc. (It is inappropriate for AFTER triggers that wish to query rows modified by the current command.) Also note that the current_timestamp family of functions qualify as stable, since their values do not change within a transaction.

VOLATILE indicates that the function value can change even within a single table scan, so no optimizations can be made. Relatively few database functions are volatile in this sense; some examples are random(), currval(), timeofday(). But note that any function that has side-effects must be classified volatile, even if its result is quite predictable, to prevent calls from being optimized away; an example is setval().

官方文档：https://www.postgresql.org/docs/12/sql-createfunction.html

db_sjz=# create sequence ljc;
CREATE SEQUENCE

db_sjz=#  select proname,provolatile from pg_proc where proname='nextval';
 proname | provolatile 
---------+-------------
 nextval | v
(1 row)
    
复制

注： pg_proc存放有关函数、过程、聚集函数以及窗口函数（共称为例程）的信息

db_sjz=# select nextval('ljc') from generate_series(1,3);
 nextval 
---------
       1
       2
       3
(3 rows)

db_sjz=#
复制

注： pg中有一个很有用处的内置函数 generate_series，可以按不同的规则产生一系列的填充数据

官方文档： https://www.postgresql.org/docs/12/functions-srf.html

db_sjz=#  alter function nextval  immutable;
ALTER FUNCTION
db_sjz=#  select proname,provolatile from pg_proc where proname='nextval';
 proname | provolatile 
---------+-------------
 nextval | i
(1 row)

db_sjz=# select nextval('ljc') from generate_series(1,3);
 nextval 
---------
       4
       4
       4
(3 rows)

db_sjz=# 
复制

db_sjz=#  alter function nextval  STABLE;
ALTER FUNCTION
db_sjz=# select nextval('ljc') from generate_series(1,3);
 nextval 
---------
       5
       6
       7
(3 rows)

db_sjz=# 
复制

稳定性：
Immutable> stable> volatile