在PostgreSQL中，一个不可变函数可以访问其代码中的表吗

Question

我正在编写一个PostgreSQL函数。我想写一个不可变的函数。

以下是PostgreSQL手册的摘录。

IMUTABLE表示函数不能修改数据库，并且在给定相同的参数值时总是返回相同的结果；也就是说，它不进行数据库查找，也不使用参数列表中不直接存在的信息。如果给定此选项，则对具有所有常量参数的函数的任何调用都可以立即替换为函数值。

我对上面文章的理解是，当您必须访问函数中的表时，不允许使用IMMUTABLE。

但是，我对IMUTABLE函数的简单实验并没有给我任何错误或错误的结果当我确信表中的数据不会改变时，我想知道我是否可以编写不可变的函数。在生产环境中使用用户定义的不可变函数是否安全？

下面是我编写的脚本，用于检查具有表访问权限的不可变函数是否会导致任何错误或产生任何错误结果。

drop table if exists customer;
create table customer (
cust_no        numeric not null,
cust_nm        character varying(100),
register_date  timestamp(0),
register_dt    varchar(8),
cust_status_cd varchar(1),
register_channel_cd varchar(1),
cust_age       numeric(3),
active_yn      varchar(1),
sigungu_cd     varchar(5),
sido_cd        varchar(2)
);
insert into customer
select i, chr(65+mod(i,26))||i::text||'CUST_NM'
, current_date - mod(i,10000)
, to_char((current_date - mod(i,10000)),'yyyymmdd') as register_dt
, mod(i,5)+1 as cust_status_cd
, mod(i,3)+1 as register_channel_cd
, trunc(random() * 100) +1 as age
, case when mod(i,22) = 0 then 'N' else 'Y' end as active_yn
, case when mod(i,1000) = 0 then '11007'
when mod(i,1000) = 1 then '11006'
when mod(i,1000) = 2 then '11005'
when mod(i,1000) = 3 then '11004'
when mod(i,1000) = 4 then '11003'
when mod(i,1000) = 5 then '11002'
else '11001' end                  as sigungu_cd
, case when mod(i,3) = 0 then '01'
when mod(i,3) = 1 then '02'
when mod(i,3) = 2 then '03' end as sido_cd
from generate_series(1,1000000) a(i);
ALTER TABLE customer ADD CONSTRAINT customer_pk
PRIMARY KEY (cust_no);
create table com_code (
group_cd  varchar(10),
cd        varchar(10),
cd_nm     varchar(100));
insert into com_code values ('G1','11001','SEOUL')
,('G1','11002','PUSAN')
,('G1','11003','INCHEON')
,('G1','11004','DAEGU')
,('G1','11005','JAEJU')
,('G1','11006','ULEUNG')
,('G1','11007','ETC');
insert into com_code values ('G2','1','Infant')
,('G2','2','Child')
,('G2','3','Adolescent')
,('G2','4','Adult')
,('G2','5','Senior');
insert into com_code values ('G3','01','Jeonbuk')
,('G3','02','Kangwon')
,('G3','03','Chungnam');
alter table com_code add constraint com_code_pk
primary key (group_cd, cd);

下面是创建一个稳定函数的脚本。

CREATE OR REPLACE FUNCTION F_GET_NM_STABLE (
p_cust_no  IN NUMERIC)
RETURNS VARCHAR
LANGUAGE PLPGSQL STABLE
AS
$$
DECLARE
v_out_name  VARCHAR(100);
BEGIN
SELECT B.CD_NM
INTO v_out_name
FROM CUSTOMER A LEFT JOIN COM_CODE B
ON (A.SIGUNGU_CD = B.CD
AND A.CUST_NO = p_cust_no)
WHERE B.GROUP_CD = 'G1';
RETURN v_out_name;
END;
$$

下面是创建一个不可变函数的脚本。

CREATE OR REPLACE FUNCTION F_GET_NM_IMMUTABLE (
p_cust_no  IN NUMERIC)
RETURNS VARCHAR
LANGUAGE PLPGSQL IMMUTABLE
AS
$$
DECLARE
v_out_name  VARCHAR(100);
BEGIN
SELECT B.CD_NM
INTO v_out_name
FROM CUSTOMER A LEFT JOIN COM_CODE B
ON (A.SIGUNGU_CD = B.CD
AND A.CUST_NO = p_cust_no)
WHERE B.GROUP_CD = 'G1';
RETURN v_out_name;
END;
$$

下面是测试上述函数的脚本。

SELECT SIGUNGU_CD, F_GET_NM_STABLE(CUST_NO)
FROM CUSTOMER
WHERE CUST_NO BETWEEN 1 AND 5;
SELECT SIGUNGU_CD, F_GET_NM_IMMUTABLE(CUST_NO)
FROM CUSTOMER
WHERE CUST_NO BETWEEN 1 AND 5;

当我运行上面的查询时，我没有看到任何错误。

我试图创建一个不可变函数，因为它在一些使用where子句中的不可变函数的查询中具有极高的性能。

Answer 1

您想要不可变函数的主要原因是这样您就可以在索引中使用它们。如果你承诺某个东西是不可变的，数据库就会相信你。但是，如果结果证明你对不变性的看法是错误的，那么这样的索引可能会返回不正确的结果。