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

Ivan Sergio Borgonovo wrote:
> > > I've a bunch of functions that operates on the basket (a smaller
> > > list of products with their attributes).
> > >
> > > So many functions ends up in repeating over and over a select
> > > similar to:
> > >
> > > select [list of columns] from baskets b
> > > join basket_items bi on b.basket_=bi.basket_id
> > > join items i on i.item_id=bi.item_id
> > > join item_attributes a a.item_id=i.item_id
> > > where b.basket_id=$1
> > >
> > > It would be nice if I could avoid to execute this query over and
> > > over. I'd have to find a way to pass this data across functions.
> >
> > You could pass arrays containing the selected rows between
> > functions. Something like:
> >
> > CREATE TYPE basket_row AS(id integer, name text, count
> > integer, ...); CREATE FUNCTION sell (items basket_row[]) RETURNS
> > boolean LANGUAGE plpgsql AS $$........$$;
>
>
> 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.

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.

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.

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?

Yours,
Laurenz Albe