Not in all cases postgres=# create function non_im_immutable_function() returns float as $$ begin return *random()*; end; $$ language plpgsql *immutable*; CREATE FUNCTION
postgres=# select proname, provolatile from pg_proc where proname = 'random' or proname = 'non_im_immutable_function'; proname | provolatile ---------------------------+------------- random | v non_im_immutable_function | i postgres=# select non_im_immutable_function(); non_im_immutable_function --------------------------- 0.963812265079468 (1 row) postgres=# select non_im_immutable_function(); non_im_immutable_function --------------------------- 0.362834882922471 (1 row) Per definition of immutable functions, the function's output shouldn't depend upon a volatile function e.g. random(). On Wed, Jan 7, 2015 at 5:03 AM, Jim Nasby <jim.na...@bluetreble.com> wrote: > On 1/6/15, 1:00 AM, Ashutosh Bapat wrote: > >> >> Even checking whether the output of the function is in the right order or >> not, has its cost. I am suggesting that we can eliminate this cost as well. >> For example, PostgreSQL does not check whether a function is really >> immutable or not. >> > > Actually, it does: > > select test(); > ERROR: UPDATE is not allowed in a non-volatile function > CONTEXT: SQL statement "UPDATE i SET i=i+1" > PL/pgSQL function test() line 3 at SQL statement > STATEMENT: select test(); > ERROR: UPDATE is not allowed in a non-volatile function > CONTEXT: SQL statement "UPDATE i SET i=i+1" > PL/pgSQL function test() line 3 at SQL statement > > > -- > Jim Nasby, Data Architect, Blue Treble Consulting > Data in Trouble? Get it in Treble! http://BlueTreble.com > -- Best Wishes, Ashutosh Bapat EnterpriseDB Corporation The Postgres Database Company