[9.0] On temporary tables - Mailing list pgsql-general

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