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;
