Re: Static functions

["Gurjeet Singh" <singh.gurjeet@xxxxxxxxx>]

On Sat, Oct 4, 2008 at 8:49 AM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:

"Gurjeet Singh" <singh.gurjeet@xxxxxxxxx> writes:
> Shouldn't PG make all efforts to not execute something when the result is
> already known?

Not if said effort would cost more than is saved, which would be by far
the most likely result if we tried to cache all function results.

Sorry Tom, I confused STABLE  with IMMUTABLE; my bad.

Joseph, you can cloak the STABLE function inside an IMMUTABLE function, then this whole thing will be executed only once. Use this advice only after you understand what you are doing.

Here's an example:

create or replace function f_stable() returns int as $$ begin raise NOTICE 'stable'; return 1; end; $$ stable language plpgsql;

create or replace function f_immutable() returns int as $$ begin raise NOTICE 'immutable'; perform f_stable(); return 1; end; $$ IMMUTABLE language plpgsql;

postgres=> select f_stable() from generate_series( 1, 2 );
NOTICE:  stable
NOTICE:  stable
 f_stable
----------
        1
        1
(2 rows)

postgres=> select f_immutable() from generate_series( 1, 2);
NOTICE:  immutable
NOTICE:  stable
CONTEXT:  SQL statement "SELECT  f_stable()"
PL/pgSQL function "f_immutable" line 1 at PERFORM
 f_immutable
-------------
           1
           1
(2 rows)

postgres=>


You can see that if STABLE function is called directly, it is invoked for each row; but if we hide the STABLE function inside an IMMUTABLE function, there is going to be just one invocation of both these functions for the whole command.

HTH.

Best regards,
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB      http://www.enterprisedb.com

Mail sent from my BlackLaptop device