Postgresql – Can a function be immutable while calling a volatile function

functionspostgresql

I have a function that does some mathematical calculations. It calls another function, which is volatile. I would like to know if the calling function can be declared as either immutable or stable, or it must necessarily be volatile as well.

Best Answer

The documentation describes IMMUTABLE as

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. [...]

This means you can do the following:

CREATE OR REPLACE FUNCTION immutablerandom() 
    RETURNS integer 
    LANGUAGE plpgsql 
    IMMUTABLE
AS $$
DECLARE a numeric;
BEGIN
    a := random();
    RETURN 1;
END;
$$;

random() is a volatile function, and immutablerandom() fulfills the criteria of the definition of IMMUTABLE. If this actually makes sense is a different thing - you have to discard the results of the volatile function altogether, and in practice I cannot see a case where I could use it.