Postgres Pro
Downloads
Home   >   mailing lists

Thread: Possible to create table name with a variable? Temp tables?

Possible to create table name with a variable? Temp tables?

From
Godshall Michael
Date:
Hi,
 
I use pgadminII and if I create a temporary table in a function, call that function more than once in a pgadminII session I always get an error message saying the temp table does not exist the second time I execute the function.  If I close out of pgadminII, re-open it, execute the function again it works. 
 
My function using a temporary table is like this:
 
CREATE function delete_me() RETURNS text AS '
 
DECLARE
BEGIN
IF ((select count(relname) from pg_class where relname = ''temp_merged_results'') = 1) THEN
drop table temp_merged_results;
END IF;
 
IF ((select count(relname) from pg_class where relname = ''temp_merged_results'') = 0) THEN
   CREATE TABLE temp_merged_results (column_name varchar(50));
END IF;
 
insert into temp_merged_results
select some_column_name
from a_different_table;
return ''test'';

END;
'  LANGUAGE 'plpgsql' STABLE;
 
 
I was thinking maybe this would be a work around.
 
Is it possible to create a table getting its name from a variable populated in a function in psql?
 
Something like this:
 
CREATE FUNCTION me() RETURNS text AS

DECLARE

    counter bigint;

    random_table_name text;

BEGIN

    counter := 0;

    counter := (SELECT nextval(''my_sequence_function_counter''));

    random_table_name := (SELECT CAST(counter as text));

    random_table_name := ''some_table_name'' || random_table_name;

    IF ((select count(relname) from pg_class where relname = random_table_name) = 1) THEN

       drop table random_table_name;

   END IF;

   IF ((select count(relname) from pg_class where relname = random_table_name) = 0) THEN

      CREATE TABLE random_table_name ( some_column varchar(50) );

   END IF;

return random_table_name;

END;

' LANGUAGE 'plpgsql' STABLE;

 
 
Mike

Re: Possible to create table name with a variable? Temp

From
Stephan Szabo
Date:
On Fri, 22 Aug 2003, Godshall Michael wrote:

You probably need to use EXECUTE.

EXECUTE ''DROP TABLE '' || random_table_name;
...
EXECUTE ''CREATE TABLE '' || random_table_name || ...
or
EXECUTE ''CREATE TEMP TABLE '' || random_table_name || ...

If you want to use the table from another function,
I think you'll probably need to wrap the uses of it
with EXECUTE in those functions as well.  Or you might
be better off in another pl language (maybe pltcl or
plpython) because plpgsql caches the query plans and
gets somewhat confused when tables not used in EXECUTE
are dropped between calls.