I've got a small question about marking functions working with decimal number types as either
IMMUTABLE or
STABLE. Below are a pair of trivial functions that show what I'm guessing. An int8/int8[] seems like it's going to be immutable forever. However, decimal types aren't quite so crisp and consistent. Does this mean that I need to mark such a function as
STABLE instead of
IMMUTABLE, like below?
I'm a bit hazy on exactly when some operations shift from
IMMUTABLE to
STABLE. For example, it seems fair that many time/date operations are not
IMMUTABLE because they vary based on the current time zone. Likewise, I think that text operations are generally not
IMMUTABLE since collations vary across versions and platforms.
Any clarification would be appreciated. I've been googling around and checking the archives, but haven't found these specific details addressed, so far.
Ah, and I have no clue how much difference it even makes to mark a function as IMMUTABLE instead of STABLE. If the difference is more theoretical than practical, I can feel comfortable using STABLE, when unclear.
Thank you!
-----------------------------------
-- array_sum(int8[]) : int8
-----------------------------------
CREATE OR REPLACE FUNCTION tools.array_sum(array_in int8[])
RETURNS int8 AS
$BODY$
SELECT SUM(element) AS result
FROM UNNEST(array_in) AS element;
$BODY$
LANGUAGE sql
IMMUTABLE;
-- Add a comment to describe the function
COMMENT ON FUNCTION tools.array_sum(int8[]) IS
'Sum an int8[] array.';
-- Set the function's owner to USER_BENDER
ALTER FUNCTION tools.array_sum(int8[]) OWNER TO user_bender;
-----------------------------------
-- array_sum(real[]]) : real
-----------------------------------
CREATE OR REPLACE FUNCTION tools.array_sum(array_in real[])
RETURNS real AS
$BODY$
SELECT SUM(element) AS result
FROM UNNEST(array_in) AS element;
$BODY$
LANGUAGE sql
STABLE; -- Decimal number types seem to change across versions and chips?
-- Add a comment to describe the function
COMMENT ON FUNCTION tools.array_sum(real[]) IS
'Sum an real[] array.';
-- Set the function's owner to USER_BENDER
ALTER FUNCTION tools.array_sum(real[]) OWNER TO user_bender;