Re: HOWTO caching data across function calls: temporary tables, cursor?

On Tue, 1 Apr 2008 14:00:39 +0200
"Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)at> wrote:

> Ivan Sergio Borgonovo wrote:

> > It doesn't look as I can do the same stuff with array and
> > tables/records.

> > Many times I use joint or aggregates on the basket.

> Sorry, my example was unclear.

I was the first not to be clear ;)

> The Type I declare should not hold one row from a single table, but
> a result row from the 4 table join you wrote above.

yes... but it is not just a matter of caching the data but rather
being able to exploit them with SQL.

> You said that you have to do the same select over and over in each
> function, and my idea is to execute the query only once and store
> the results in an array.

I've to deal with a very small subset of a larger one over and over.
I think picking up the attributes of all items in a basket and use
aggregates, join on that small subset but having to refer to the
larger set has its cost.

I'd like to pay this cost just one time. Actually I even don't know
if this approach is worth, but still I won't know if it works unless
I'll have a working implementation.

I could even retrieve a larger set of columns so that I could use the
same slice for many purposes. After all I think it should be faster
to retrieve a larger set of columns one time rather than retrieving
the same set divided into multiple requests.
This way the query will actually be the same and it should be easier
to cache the result.

> That should work just fine.
>
> > Is there any good tutorial/example on how to use cursors or temp
> > tables in such circumstance?

> Don't know about temporary tables, but the docs have enough about
> "refcursor":
> http://www.postgresql.org/docs/current/static/plpgsql-cursors.html

> A cursor is actually a good idea for this, I hadn't thought of it.
> You'd need scrollable cursors though, and these did not exist in
> PL/pgSQL before version 8.3.

> You could just pass a variable of type refcursor between your
> functions and each function could do a
> MOVE FIRST FROM curs;
> FOR var IN FETCH ALL FROM curs LOOP
> ....
> END LOOP;

> Does that help?

Well it does look as if I could refine a search in a cursor.

A possible way could be to encapsulate the temp table in a function,
but still I'd like to find a tutorial/howto etc... that will point
out the troubles I'm going to meet with uniqueness of the name,
visibility, garbage collection...

Suppose I've a function that return a setof record

I could use that function inside other function as in

select into [vars,...] [cols,...] from AFunction(...) where cols1<7...

Now AFuncion(...) is going to retrieve over and over the same record
set inside a transaction.

AFuncion will be used inside several other functions.

It would be nice if I could cache the result of AFunction.
For me it's not clear if adding some additional caching system (eg.
create a temp table inside the function) would obtain the same result
as marking the function STABLE.

thx

--
Ivan Sergio Borgonovo
http://www.webthatworks.it