Safely drop temporary table without dropping regular table with same name

 In PostgreSQL, it allows to create temporary table with same name as existing regular table, in such case, temporary table has higher "visibility" than regular table.

Once you drop the temporary table, you can see the data in regular table again. But, if you issue drop table twice, then you drop both temporary table and regular table.

How to prevent regular table being dropped? 

Here is a wrapper to safeguard this:

1. -- Function to drop a temporary table if it exists
2. CREATE OR REPLACE FUNCTION drop_temp_table_if_exists(table_name text)
3. RETURNS void AS $$
4. DECLARE
5. temp_schema text;
6. BEGIN
7. -- Find the temporary schema name for the current session
8. SELECT nspname INTO temp_schema
9. FROM pg_namespace
10. WHERE oid = pg_my_temp_schema();
12. -- Check if the table exists in the temporary schema
13. IF EXISTS (
14. SELECT 1
15. FROM pg_class c
16. JOIN pg_namespace n ON c.relnamespace = n.oid
17. WHERE n.nspname = temp_schema
18. AND c.relname = table_name
19. AND c.relkind = 'r' -- 'r' indicates a regular table (in this context, a temp table)
20. ) THEN
21. -- Construct the dynamic SQL to drop the table
22. EXECUTE format('DROP TABLE %I', table_name);
23. END IF;
24. END;
25. $$ LANGUAGE plpgsql;

Demonstration:

1. mytest=# create table t1 (n text);
2. CREATE TABLE
3. Time: 4.322 ms
4. mytest=# insert into t1 values ('regular');
5. INSERT 0 1
6. Time: 3.518 ms
7. mytest=# create temporary table t1 (n text);
8. CREATE TABLE
9. Time: 3.192 ms
10. mytest=# insert into t1 values ('temporary');
11. INSERT 0 1
12. Time: 2.869 ms
14. mytest=# select * from t1;
15. n
16. -----------
17. temporary
18. (1 row)
20. mytest=# SELECT drop_temp_table_if_exists('t1');
21. drop_temp_table_if_exists
22. ---------------------------
24. (1 row)
26. Time: 5.620 ms
27. mytest=# select * from t1;
28. n
29. ---------
30. regular
31. (1 row)
33. Time: 0.251 ms
34. mytest=# SELECT drop_temp_table_if_exists('t1');
35. drop_temp_table_if_exists
36. ---------------------------
38. (1 row)
40. Time: 0.740 ms
42. mytest=# select * from t1;
43. n
44. ---------
45. regular
46. (1 row)
48. Time: 0.410 ms