Feature Request - Create temporary table if it doesn't exist.

Hello,

I notice that this is a post for a feature request but I do see something that could help in the mean time. To address this issue, you can consider using Common Table Expressions (CTEs) instead of temporary tables. CTEs allow you to create a temporary result set within a query, without the need to explicitly create and drop physical tables. This can make your queries more portable and easier to manage.

Here is an example of how you can use CTEs in Amazon Redshift:

-- Common Table Expression 1 (CTE1)
WITH cte1 AS (
  SELECT column1, column2
  FROM table1
  WHERE condition1
)

-- Common Table Expression 2 (CTE2)
, cte2 AS (
  SELECT column3, column4
  FROM table2
  WHERE condition2
)

-- Main Query using the CTEs
SELECT cte1.column1, cte1.column2, cte2.column3, cte2.column4
FROM cte1
JOIN cte2 ON cte1.column1 = cte2.column3;

By using CTEs, you avoid creating physical temporary tables and the associated "DROP TABLE" statements. Instead, the CTEs exist only within the scope of the main query, and they are automatically discarded after the query finishes execution.

However, do keep in mind that CTEs have some limitations. They are useful for temporary intermediate results, but if you need to use the same result set multiple times within a larger query or across different queries, temporary tables may still be necessary.

Ultimately, the choice between temporary tables and CTEs depends on the complexity of your queries and the specific use case at hand. In some cases, you might still need to use temporary tables, but in others, CTEs can be a cleaner and more portable solution.