--- On Thu, 9/30/10, Vincenzo Romano <vincenzo.rom...@notorand.it> wrote:
From: Vincenzo Romano <vincenzo.rom...@notorand.it> Subject: [GENERAL] [9.0] On temporary tables To: "PostgreSQL General" <pgsql-general@postgresql.org> Date: Thursday, September 30, 2010, 11:09 AM Hi all. This is my case: -- begin snippet -- reset search_path; drop table if exists session cascade; create table session ( name text primary key, valu text not null ); create or replace function session_init() returns void language plpgsql as $body$ declare t text; begin select valu into t from session where name='SESSION_ID'; if not found then create temporary table session ( like public.session including all ); insert into session values ( 'SESSION_ID',current_user ); end if; end; $body$; SELECT * from session; SELECT * from session_init(); SELECT * from session; SELECT * from session_init(); -- end snippet -- The output from the last four queries is: -- tmp2=# SELECT * from session; name | valu ------+------ (0 rows) tmp2=# SELECT * from session_init(); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "session_pkey" for table "session" CONTEXT: SQL statement "create temporary table session ( like public.session including all )" PL/pgSQL function "session_init" line 6 at istruzione SQL session_init -------------- (1 row) tmp2=# SELECT * from session; name | valu ------------+------ SESSION_ID | enzo (1 row) tmp2=# SELECT * from session_init(); ERROR: relation "session" already exists CONTEXT: SQL statement "create temporary table session ( like public.session including all )" PL/pgSQL function "session_init" line 6 at istruzione SQL -- This means that the "if not found then" in the function body didn't work well. The idea is to create a temporary table to store session variables only of there's no temporary table with that name. Any hint on this? -- Vincenzo Romano at NotOrAnd Information Technologies Software Hardware Networking Training Support Security -- NON QVIETIS MARIBVS NAVTA PERITVS -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general check if the temp_table alredy exist select 1 from pg_class where relname = 'prueba3'
