Re: Function result cacheing - any comments?

OK - I assume from everybody else's silence that they either (a) agree with 
the idea, or (b) think Tom hit the idea on the head, so they feel they 
don't need to respond.

So what I would like to do is implement a simple version of this to attempt 
to justify my claims of performance gains. The sort of trivial places where 
I think gains *may* be had are:

create table departments(id integer, name text, manager_id integer);
create table people(id integer, department_id, name text);

create function get_manager_name(integer) returns text as    'select name from departments d, people p     where d.id =
$1and p.id = d.manager_id';
 

select name,get_manager_name(department_id) from people;

This is obviously a case where a LOJ or column-select would do the trick, 
*but* it does represent a class of problems that people frequently write 
procedures to perform a single (sometimes complex) action. Using a function 
also encapsulates some knowledge of the data structures, resulting in more 
maintainable code.

eg. even the above simple example becomes a lot less readable and maintainable:

select name,    (select m.name from departments d, people m        where d.id = p.department_id and m.id =
d.manager_id)as manager_name from people p;
 

if a function is not used.

My theory is that if such a piece of code gets a performance gain, then the 
code is probably worth including, assuming that the function manager does 
not need to be butchered to achieve the desired goal. Does that sound 
reasonable?

So the obvious question is - in the opinion of people who know the code, 
can a function-result-cache be implemented with a lifetime of a single 
statement, without butchering the function manager?



----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/