I am writing a PostgreSQL function. I want to write an immutable function.
The following is an excerpt from a PostgreSQL manual.
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.
My understanding of the passage above is that you are not allowed to use IMMUTABLE when you have to access a table within the function.
But my simple experimentation with IMMUTABLE functions does not give me any errors or faluty results. I wonder I may write immutable functions when I am certain that the data in the table are not going to change. Is it safe to use a User Defined Immutable function in a production environment?
Below is the script I wrote to check if immutable functions with a table access cause any errors or produce any faulty results.
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);
The following is the script to create a stable function.
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;
$$
The following is the script to create an immutable function.
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;
$$
The following is the script to test those functions above.
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;
When I run the queries above I don't see any errors.
I am trying to create an immutable function because it is extremely performant in some queries which use the immutable function in the where clause.
