Thread: Calling the same function more than once with the same arguments

I'm building a function caller() in which a certain function called()
will be called many times in from clauses with the same arguments and
I'm wondering if is there a performance penalty for that or if the sql
engine is smart enough to call called() only once.

I tried to substitute called() in the from clauses for a temporary
table created first in caller() but it refuses to build caller() with
the message "ERROR:  relation "temp_table_name" does not exist". It
does not exist in build time but it will exist in run time.

Regards, Clodoaldo Pinto

Look into the immutable flag on function creation:

http://www.postgresql.org/docs/8.1/interactive/xfunc-volatility.html

On Wed, 19 Apr 2006, Clodoaldo Pinto wrote:

> I'm building a function caller() in which a certain function called()
will be called many times in from clauses with the same arguments and
I'm wondering if is there a performance penalty for that or if the sql
engine is smart enough to call called() only once.

I tried to substitute called() in the from clauses for a temporary
table created first in caller() but it refuses to build caller() with
the message "ERROR:  relation "temp_table_name" does not exist". It
does not exist in build time but it will exist in run time.

Regards, Clodoaldo Pinto

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

                http://archives.postgresql.org

"Clodoaldo Pinto" <clodoaldo.pinto@gmail.com> writes:
> I'm building a function caller() in which a certain function called()
> will be called many times in from clauses with the same arguments and
> I'm wondering if is there a performance penalty for that or if the sql
> engine is smart enough to call called() only once.

No, it isn't.

            regards, tom lane

2006/4/19, Ben <bench@silentmedia.com>:
> Look into the immutable flag on function creation:
>
> http://www.postgresql.org/docs/8.1/interactive/xfunc-volatility.html
>

That is what I needed to know, Thanks.

Regards, Clodoaldo Pinto

On Wed, 2006-04-19 at 18:23, Tom Lane wrote:
> "Clodoaldo Pinto" <clodoaldo.pinto@gmail.com> writes:
> > I'm building a function caller() in which a certain function called()
> > will be called many times in from clauses with the same arguments and
> > I'm wondering if is there a performance penalty for that or if the sql
> > engine is smart enough to call called() only once.
>
> No, it isn't.

HOWEVER, depending on how you declare the function, you should be able
to index it.

2006/4/19, Ben <bench@silentmedia.com>:
> Look into the immutable flag on function creation:

I have read that section and I'm still not sure about it.

This is the caller() function:

create or replace function caller(int4)
returns some_type as

$body$

select
  array(select distinct a from called($1)) as a
  ,
  array(select distinct b from called($1)) as b
;

$body$
language 'sql' stable strict;

Since called() will read from a table that can be modified it is
declared as stable and not as immutable.

This is what is in section 32.6:
" A STABLE function cannot modify the database and is guaranteed to
return the same results given the same arguments FOR ALL ROWS WITHIN A
SINGLE STATEMENT."

Is called() called from within a single statement in caller()?

Will the optimizer optimize the multiple calls (there will be about
30) to a single call?

Regards, Clodoaldo Pinto