Re: Static functions

On Sat, Oct 4, 2008 at 8:49 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "Gurjeet Singh" <singh(dot)gurjeet(at)gmail(dot)com> 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[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device