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

On Tue, 1 Apr 2008 12:01:21 +0200
"Albe Laurenz" <laurenz.albe@wien.gv.at> wrote:

> 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.

Is there any good tutorial/example on how to use cursors or temp
tables in such circumstance?


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