Drop temporary table when exiting a function

Question

I use a temp table in a function with the 'on commit drop' option. My problem is, in certain cases, a more global function can call the first one twice, so the "create temp table" is called twice before the commit - so I have the normal error "relation [my_temp_table] already exists".

I use the temp table at the end of the function to return its rows in the "return query", so I can't manually drop the table before I leave the function.

CREATE OR REPLACE FUNCTION my_function(_value text)
RETURNS setof my_table AS $$
DECLARE 
    resultCount integer := 0;
BEGIN

    create temp table my_temp_table on commit drop as
    select *
    from my_table 
    where value = _value ;

    select count(*) into resultCount from my_temp_table;
    if (resultCount = 0) then 
        raise exception 'value not found';
        end if;

    return query
    select * from my_temp_table;

END;$$ LANGUAGE plpgsql VOLATILE COST 100;
ALTER FUNCTION my_function(text) OWNER TO postgres

If you wonder why I use a temp table and not my_table in a direct way it's because I need a really fast response and my_table is very big (several dozens of millions rows) so this way I can request it only once instead of three times (search, count and return).

I found a workaround not using temp table and creating a type, but the structure of my_table will change many times, and actually I have dozens of "my table" and the concerned "my function", so this was a way to not write again all my functions each time the structure of my tables will change.

The function has to return the same structure than the table it requests.

How can I can drop the table when leaving the function? Or is there a better workaround?

Answer 1

... return its rows in the "return query", so I can't manually drop the table before I leave the function.

Actually you can. You can use several RETURN QUERY in your case. After manual:

When a PL/pgSQL function is declared to return SETOF [...] the individual items to return are specified by a sequence of RETURN NEXT or RETURN QUERY commands, and then a final RETURN command with no argument is used to indicate that the function has finished executing

So you could do:

RETURN QUERY
       SELECT * FROM my_temp_table;
DROP TABLE my_temp_table;
RETURN;

Answer 2

You could drop the table just in case:

...
BEGIN
    drop table if exists my_temp_table;
    create temp table my_temp_table on commit drop as
    ....

but... in fact you do not need a temporary table. Try this code:

...
    return query
    select *
    from my_table 
    where value = _value ;

    if not found then
        raise exception 'value not found';
    end if;
...