HOWTO caching data across function calls: temporary tables, cursor? - Mailing list pgsql-general

I made a similar question but maybe it was not that clear.

I've a large table (items) linked with other tables (attributes).

Some product ends into a basket.

create table items(
  item_id serial primary key,
  attributes...
);

create table item_attributes(
  item_id int references items (item_id)
  attributes...
);

create table baskets(
  basket_id serial primary key,
  ...other stuff
);
create table basket_items(
  item_id int references items (item_id),
  basket_id int references baskets (basket_id),
  ...
);


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.

One way would be to put this data in a temporary table, but many
things are unclear to me.
I still have to find a way to reference these tables across functions
(there will be different basket_id, and each transaction should see
the same temp table and not "steal" the one of other transactions).
I've to take care of name clash and visibility.
I need to take care of garbage collection at the right time.
I've no idea of the performance gain.
Caching of queries in function (relation with OID ##### does not
exist) and all the above make the use of temp tables a bit
overwhelming.

It seems that another way would be to use cursors... but I haven't
been able to find any example.

I think this is a common problem but I can't find general guidelines.

I'm on 8.1

thanks

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