sql generate unique table/view name

Question

I want to create some mad robust code. I want to take a query as a string, create a temporary view / table to store the results, use it, then drop the table. I want to use a name that is guaranteed to not already exist in the database. Is there an SQL command to generate a unique table name? I'm using postgresql if this is implementation-specific.

Answer 1

I don't know a SQL command to do that but you could just generate one kind of like generating a temp filename: put in the date, some random number, process ID, user's birthday: $table_name = join("_", "temp", $$, rand()*0x1000, time)

In all SQL databases I've used, each session has its own namespace of temporary tables. So you can just create a temp table called "foo" and not worry about it clashing with another thread doing the same thing (on a different connection!). In Oracle, this is specifically a "LOCAL temporary table"- whereas a "GLOBAL temporary table" is seen by all sessions: but only its schema, not its data. In MSSQL, table names are internally bodged to be distinct, but constraint names aren't (are weren't as of SQL Server 2000).

Answer 2

I don't know postgres very well, but could you do (excuse the pseudo-code):

SET @name = GetBigRandomNumber();

WHILE TableExists(@name)
BEGIN
    SET @name = GetBigRandomNumber();
END

Answer 3

You could use a temporary table. Temporary tables are invisible to other connections. If a permanent table with the same name as the temporary table exists, the permanent table is invisible to the current connection.

CREATE TEMPORARY TABLE table_name  
(  
 column_name1 data_type(length) constraints,  
 column_name2 data_type(length) constraints,  
 ...

See the PostgreSQL manual page.