I was looking through some of the plpgsql functions in our codebase and realized that most of them cannot be set IMMUTABLE
only because they contain reference to now()
, e.g.
CREATE OR REPLACE FUNCTION find_nearest_time (
target_times time without time zone[],
target_dows int[],
timezone_name text
)
RETURNS timestamp with time zone
AS $$
DECLARE
target_time time without time zone;
nearest_time timestamp with time zone;
next_time timestamp with time zone;
BEGIN
IF (
array_length(target_times, 1) = 0 OR
array_length(target_dows, 1) = 0
) THEN
RETURN null;
END IF;
FOR add_day IN 0..6
LOOP
FOREACH target_time IN ARRAY find_nearest_time.target_times
LOOP
next_time := (date_trunc('day', (now() at time zone find_nearest_time.timezone_name) - target_time::interval) + interval '1 day' + target_time::interval) at time zone find_nearest_time.timezone_name;
next_time := next_time + interval '1 day' * add_day;
IF (
extract(isodow from next_time) = ANY(target_dows) AND
(
nearest_time IS NULL OR
nearest_time > next_time
)
) THEN
nearest_time := next_time;
END IF;
END LOOP;
IF (nearest_time IS NOT NULL) THEN
RETURN nearest_time;
END IF;
END LOOP;
RETURN next_time;
END
$$
LANGUAGE plpgsql;
However, we can easily rewrite the above function to where now()
is passed as a parameter, e.g.
CREATE OR REPLACE FUNCTION find_nearest_time (
seed_time timestamp with time zone,
target_times time without time zone[],
target_dows int[],
timezone_name text
)
RETURNS timestamp with time zone
AS $$
DECLARE
target_time time without time zone;
nearest_time timestamp with time zone;
next_time timestamp with time zone;
BEGIN
IF (
array_length(target_times, 1) = 0 OR
array_length(target_dows, 1) = 0
) THEN
RETURN null;
END IF;
FOR add_day IN 0..6
LOOP
FOREACH target_time IN ARRAY find_nearest_time.target_times
LOOP
next_time := (date_trunc('day', (find_nearest_time.seed_time at time zone find_nearest_time.timezone_name) - target_time::interval) + interval '1 day' + target_time::interval) at time zone find_nearest_time.timezone_name;
next_time := next_time + interval '1 day' * add_day;
IF (
extract(isodow from next_time) = ANY(target_dows) AND
(
nearest_time IS NULL OR
nearest_time > next_time
)
) THEN
nearest_time := next_time;
END IF;
END LOOP;
IF (nearest_time IS NOT NULL) THEN
RETURN nearest_time;
END IF;
END LOOP;
RETURN next_time;
END
$$
LANGUAGE plpgsql
IMMUTABLE;
This way the function can be marked as IMMUTABLE
.
What is the benefit of marking function as IMMUTABLE
in this case?