Re: [9.0] On temporary tables

--- On Thu, 9/30/10, Vincenzo Romano <vincenzo(dot)romano(at)notorand(dot)it> wrote:

From: Vincenzo Romano <vincenzo(dot)romano(at)notorand(dot)it>
Subject: [GENERAL] [9.0] On temporary tables
To: "PostgreSQL General" <pgsql-general(at)postgresql(dot)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(at)postgresql(dot)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'