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

Ivan Sergio Borgonovo wrote:
[wants to cache query results in a temporary table for use in
several functions]

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

Oh, I see, you want to select/join with the cached data.

Then neither arrays nor cursors can help; you need a temporary table.

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

You can
CREATE TEMPORARY TABLE basket_123 [ON COMMIT DROP] AS SELECT ....
If you do not include ON COMMIT DROP, the table will be dropped at the end
of your database session.

To have more than one table and have a unique name, you can
use a sequence to construct the table name. Tha means you will have
to use dynamic SQL.

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

Since you are looking for a sample, maybe something like that can get you started:

CREATE SEQUENCE temp_names;

CREATE FUNCTION mkcache(param1 integer, param2 text) RETURNS name
LANGUAGE plpgsql VOLATILE STRICT AS
$$DECLARE
i integer;
BEGIN
SELECT nextval('temp_names') INTO i;
EXECUTE 'CREATE TEMPORARY TABLE basket_' || i || '(col1, col2, ...) AS SELECT .....';
RETURN 'basket_' || i;
END;$$;

CREATE FUNCTION getcache(tabname IN name, col1 OUT integer, col2 OUT text, ...) RETURNS SETOF RECORD
LANGUAGE plpgsql IMMUTABLE STRICT AS
$$BEGIN
FOR col1, col2, ... IN EXECUTE 'SELECT col1, col2, ... FROM ' || tabname LOOP
RETURN NEXT;
END LOOP;
RETURN;
END;$$;

Sample use:

SELECT mkcache(1, 'test');
mkcache
----------
basket_1
(1 row)

SELECT * FROM getcache('basket_1');
col1 | col2 | ...
------+-------+-----
27 | item1 | ...
....
(n rows)

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

No, STABLE doen't help here - that is only a hint for the optimizer.

Yours,
Laurenz Albe