Are operations on real values IMMUTABLE or STABLE? - Mailing list pgsql-hackers

From Morris de Oryx
Subject Are operations on real values IMMUTABLE or STABLE?
Date
Msg-id CAKqncchnewXq3t6pLwTjPXN0xA-hju1qAi294EQYDkhNxwy+Sg@mail.gmail.com
Whole thread Raw
Responses Re: Are operations on real values IMMUTABLE or STABLE?
List pgsql-hackers
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;


pgsql-hackers by date:

Previous
From: Pavel Stehule
Date:
Subject: Re: Improving information_schema._pg_expandarray()
Next
From: Pavel Stehule
Date:
Subject: Re: broken master regress tests