Thread: Caching/Indexing Function Output

Hello,

I've written several functions using the SPI_ interface, some of them are to
simply ease inserting into a few tables-

ie- I wrote a function sp_host('10.0.0.1'); which returns the id of the
given
ip address from a ip_addresses table if it exists, otherwise, it inserts it,
and
returns that id. We use this to keep the logic simple when inserting new
entries
into a weblogs table-

    insert into weblogs (..., host) values (..., sp_host('10.0.0.1'));

We also use this for a dozen or so other similar tables-

My question is- is there anyway, since a call to sp_host('10.0.0.1') will
always
return the same value (the primary key/sequence value from the ip_addresses
table), to cache/index that value somehow, so anytime I ever call that same
function, with the same argument, it returns a cached/indexed value?

ie- I make an initial call to sp_host('10.0.0.1'), which returns the id
1000; the
system caches this, and stores:

function=sp_host, arg='10.0.0.1', return=1000

or

key=sp_host('10.0.0.1'), value=1000

the next time I call sp_host, with the same arg, it simply returns 1000,
instead
of actually calling the function?

clearly I don't want to do this for *all* the functions I've written, but it
would
probably speed these functions up considerably?

Thanks!

Mike Pultz
mike@mrhost.ca

On Thu, Dec 23, 2004 at 01:22:42PM -0800, Mike wrote:

> My question is- is there anyway, since a call to sp_host('10.0.0.1') will
> always
> return the same value (the primary key/sequence value from the ip_addresses
> table), to cache/index that value somehow, so anytime I ever call that same
> function, with the same argument, it returns a cached/indexed value?

Some procedural languages (PL/Tcl, PL/Python; also PL/Perl in 8.0)
have global data that you could use as a cache.  The function would
still be called each time, but you could at least use the cache to
save the cost of a database query.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

> On Thu, Dec 23, 2004 at 01:22:42PM -0800, Mike wrote:
>
> > My question is- is there anyway, since a call to sp_host('10.0.0.1')
will
> > always
> > return the same value (the primary key/sequence value from the
ip_addresses
> > table), to cache/index that value somehow, so anytime I ever call that
same
> > function, with the same argument, it returns a cached/indexed value?
>
> Some procedural languages (PL/Tcl, PL/Python; also PL/Perl in 8.0)
> have global data that you could use as a cache.  The function would
> still be called each time, but you could at least use the cache to
> save the cost of a database query.

All the functions I wrote are in C-

So yeah, I guess I could have a global key/value hash that I keep up from
inside the
functions, and if the ip address exists, then return the value, otherwise
actually
execute the function.

I could probably use something like libmm if I didn't have access to global
vars, and
store them in shared memory- I was just hoping to have something that
postgres
would manage automagically for me. :)

It would also be cool if the planner could decide which to use- ie it would
be
considered cheaper to use the cache then call the function if the ip address
was
listed.

Thanks-

Mike