Calling the same function more than once with the same arguments

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