Create a temp table (if not exists) for use into a custom procedure
Solution 1
DROP Table each time before creating TEMP table as below:
BEGIN
DROP TABLE IF EXISTS temp_table1;
create temp table temp_table1
-- Your rest Code comes here
Solution 2
The problem of temp tables is that dropping and recreating temp table bloats pg_attribute heavily and therefore one sunny morning you will find db performance dead, and pg_attribute 200+ gb while your db would be like 10gb.
So we're very heavy on temp tables having >500 rps and async i\o via nodejs and thus experienced a very heavy bloating of pg_attribute because of that. All you are left with is a very aggressive vacuuming which halts performance. All answers given here do not solve this, because they all bloat pg_attribute heavily.
So the solution is elegantly this
create temp table if not exists my_temp_table (description) on commit delete rows;
So you go on playing with temp tables and save your pg_attribute.
Solution 3
You want to DROP term table after commit (not DELETE ROWS), so:
begin
create temp table temp_table1
on commit drop
...
Comments
-
Incerteza almost 2 years
I'm trying to get the hang of using temp tables:
CREATE OR REPLACE FUNCTION test1(user_id BIGINT) RETURNS BIGINT AS $BODY$ BEGIN create temp table temp_table1 ON COMMIT DELETE ROWS as SELECT table1.column1, table1.column2 FROM table1 INNER JOIN -- ............ if exists (select * from temp_table1) then -- work with the result return 777; else return 0; end if; END; $BODY$ LANGUAGE plpgsql;
I want the row
temp_table1
to be deleted immediately or as soon as possible, that's why I addedON COMMIT DELETE ROWS
. Obviously, I got the error:ERROR: relation "temp_table1" already exists
I tried to add
IF NOT EXISTS
but I couldn't, I simply couldn't find working example of it that would be the I'm looking for.Your suggestions?