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 = $1 and 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 |/