Create temp table in a STABLE stored procedure in postgresql

Question

I would like to create a temp table in a stored procedure which has a STABLE volatility category setted to store the result of a select for later usage in the stored procedure. At the end of the stored procedure this temp table is deallocated and i am sure that this temp table does not have any affect on the database, because as far as i know with this volatility category i ensure the optimizer that this stored procedure will not affect the database.

So i would like to do something like this:

Create a stored procedure which returns with a query:

CREATE OR REPLACE FUNCTION storedproc() 
RETURNS TABLE 
(Egy TEXT,
 Ketto TEXT) 
AS $$
BEGIN
        RETURN QUERY SELECT * FROM temptable;
END;
$$ LANGUAGE plpgsql;

Create a stored procedure which is using the previous query:

CREATE OR REPLACE FUNCTION stablefunction() 
RETURNS TABLE 
(Egy TEXT,
 Ketto TEXT) 
AS $$
BEGIN
        -- I would like to store the results here for later usage
        CREATE TEMP TABLE buba AS select * from storedproc();

        -- Do other stuff
        -- ...
        -- Reuse the results here which was stored before

END;
$$ LANGUAGE plpgsql
STABLE;

But when i want to execute this stored procedure as this:

DO
$$
BEGIN
    perform stablefunction() ;
END;
$$ LANGUAGE plpgsql;

i get the following error message:

ERROR: CREATE TABLE AS is not allowed in a non-volatile function

Maybe this is not the intended usage of the stored procedures, but then is there a way for store the result of a query inside of the stored procedure for later usage in the same stored procedure, maybe like a handle or somethings?

Maybe you can store the results in an array of that table type? I suspect there's a better way to accomplish what you want to do anyway, but can't say without seeing the actual code. — 404
– 404, Commented Apr 9, 2018 at 22:06

klin · Accepted Answer · 2018-04-10 00:33:48Z

4

The documentation states clearly: A stable function cannot modify the database. A temporary table is a part of a database as well, so you cannot create it, insert into, delete from etc. Your concept seems a bit strange but I don't want to judge it. There is a trick that allows what you want to do. Perfom all actions on the temp table using other functions that do not have to be stable. Example:

create or replace function create_my_temp_table()
returns void language plpgsql volatile as $$
begin
    create temp table temp_table(id int);
    insert into temp_table values (123);
end $$;

create or replace function stable_function()
returns text language plpgsql stable as $$
begin
    perform create_my_temp_table();
    return 'ok';
end $$;

Test:

select stable_function();

 stable_function 
-------------
 ok
(1 row)

select * from temp_table;

 id  
-----
 123
(1 row)

answered Apr 10, 2018 at 0:33

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Csuszmusz Over a year ago

Thanks for your idea, and thanks for not judging! : ) I agree maybe there is a problem with my concept, maybe its coming from my OOP background which can not be adapted in a procedural language world. The problem which i wanted to solve is that the main query is very complex and if i would want to nest selects into selects and joints it would be very complex and hard to maintain. Thanks again for your answer. :)

klin Over a year ago

Using temporary tables in a function is not unusual, it can greatly facilitate the processing of data, especially in the case of very complicated queries. I do not know, however, why the function must be stable.

Csuszmusz Over a year ago

I would like to improve the performance for a stored procedure which will be called over and over again just with different args only for reading from the database and as far as i know the stable volatility category is exactly for this purpose which will help the optimizer and improve the performance.

klin Over a year ago

This performance improvement concerns the results of a function which can be cached when the function is repeatedly executed with the same arguments. I do not know your case but I'm afraid that you pin too much hope on this optimization.

Collectives™ on Stack Overflow

Create temp table in a STABLE stored procedure in postgresql

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related