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
