temp.out 1.55 KB
Newer Older
1 2 3 4 5 6 7
--
-- TEMP
-- Test temp relations and indexes
--
-- test temp table/index masking
CREATE TABLE temptest(col int);
CREATE INDEX i_temptest ON temptest(col);
8 9 10 11 12 13 14
CREATE TEMP TABLE temptest(tcol int);
CREATE INDEX i_temptest ON temptest(tcol);
SELECT * FROM temptest;
 tcol 
------
(0 rows)

15 16
DROP INDEX i_temptest;
DROP TABLE temptest;
17 18 19 20 21
SELECT * FROM temptest;
 col 
-----
(0 rows)

22 23 24 25 26
DROP INDEX i_temptest;
DROP TABLE temptest;
-- test temp table selects
CREATE TABLE temptest(col int);
INSERT INTO temptest VALUES (1);
27 28
CREATE TEMP TABLE temptest(tcol float);
INSERT INTO temptest VALUES (2.1);
29
SELECT * FROM temptest;
30 31 32
 tcol 
------
  2.1
33 34
(1 row)

35 36 37 38 39
DROP TABLE temptest;
SELECT * FROM temptest;
 col 
-----
   1
40 41
(1 row)

42 43
DROP TABLE temptest;
-- test temp table deletion
44
CREATE TEMP TABLE temptest(col int);
45 46
\c regression
SELECT * FROM temptest;
47
ERROR:  relation "temptest" does not exist
48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80
-- Test ON COMMIT DELETE ROWS
CREATE TEMP TABLE temptest(col int) ON COMMIT DELETE ROWS;
BEGIN;
INSERT INTO temptest VALUES (1);
INSERT INTO temptest VALUES (2);
SELECT * FROM temptest;
 col 
-----
   1
   2
(2 rows)

COMMIT;
SELECT * FROM temptest;
 col 
-----
(0 rows)

DROP TABLE temptest;
-- Test ON COMMIT DROP
BEGIN;
CREATE TEMP TABLE temptest(col int) ON COMMIT DROP;
INSERT INTO temptest VALUES (1);
INSERT INTO temptest VALUES (2);
SELECT * FROM temptest;
 col 
-----
   1
   2
(2 rows)

COMMIT;
SELECT * FROM temptest;
81
ERROR:  relation "temptest" does not exist
82 83 84
-- ON COMMIT is only allowed for TEMP
CREATE TABLE temptest(col int) ON COMMIT DELETE ROWS;
ERROR:  ON COMMIT can only be used on TEMP tables