1-- 2-- TEMP 3-- Test temp relations and indexes 4-- 5-- test temp table/index masking 6CREATE TABLE temptest(col int); 7CREATE INDEX i_temptest ON temptest(col); 8CREATE TEMP TABLE temptest(tcol int); 9CREATE INDEX i_temptest ON temptest(tcol); 10SELECT * FROM temptest; 11 tcol 12------ 13(0 rows) 14 15DROP INDEX i_temptest; 16DROP TABLE temptest; 17SELECT * FROM temptest; 18 col 19----- 20(0 rows) 21 22DROP INDEX i_temptest; 23DROP TABLE temptest; 24-- test temp table selects 25CREATE TABLE temptest(col int); 26INSERT INTO temptest VALUES (1); 27CREATE TEMP TABLE temptest(tcol float); 28INSERT INTO temptest VALUES (2.1); 29SELECT * FROM temptest; 30 tcol 31------ 32 2.1 33(1 row) 34 35DROP TABLE temptest; 36SELECT * FROM temptest; 37 col 38----- 39 1 40(1 row) 41 42DROP TABLE temptest; 43-- test temp table deletion 44CREATE TEMP TABLE temptest(col int); 45\c 46SELECT * FROM temptest; 47ERROR: relation "temptest" does not exist 48LINE 1: SELECT * FROM temptest; 49 ^ 50-- Test ON COMMIT DELETE ROWS 51CREATE TEMP TABLE temptest(col int) ON COMMIT DELETE ROWS; 52-- while we're here, verify successful truncation of index with SQL function 53CREATE INDEX ON temptest(bit_length('')); 54BEGIN; 55INSERT INTO temptest VALUES (1); 56INSERT INTO temptest VALUES (2); 57SELECT * FROM temptest; 58 col 59----- 60 1 61 2 62(2 rows) 63 64COMMIT; 65SELECT * FROM temptest; 66 col 67----- 68(0 rows) 69 70DROP TABLE temptest; 71BEGIN; 72CREATE TEMP TABLE temptest(col) ON COMMIT DELETE ROWS AS SELECT 1; 73SELECT * FROM temptest; 74 col 75----- 76 1 77(1 row) 78 79COMMIT; 80SELECT * FROM temptest; 81 col 82----- 83(0 rows) 84 85DROP TABLE temptest; 86-- Test ON COMMIT DROP 87BEGIN; 88CREATE TEMP TABLE temptest(col int) ON COMMIT DROP; 89INSERT INTO temptest VALUES (1); 90INSERT INTO temptest VALUES (2); 91SELECT * FROM temptest; 92 col 93----- 94 1 95 2 96(2 rows) 97 98COMMIT; 99SELECT * FROM temptest; 100ERROR: relation "temptest" does not exist 101LINE 1: SELECT * FROM temptest; 102 ^ 103BEGIN; 104CREATE TEMP TABLE temptest(col) ON COMMIT DROP AS SELECT 1; 105SELECT * FROM temptest; 106 col 107----- 108 1 109(1 row) 110 111COMMIT; 112SELECT * FROM temptest; 113ERROR: relation "temptest" does not exist 114LINE 1: SELECT * FROM temptest; 115 ^ 116-- ON COMMIT is only allowed for TEMP 117CREATE TABLE temptest(col int) ON COMMIT DELETE ROWS; 118ERROR: ON COMMIT can only be used on temporary tables 119CREATE TABLE temptest(col) ON COMMIT DELETE ROWS AS SELECT 1; 120ERROR: ON COMMIT can only be used on temporary tables 121-- Test foreign keys 122BEGIN; 123CREATE TEMP TABLE temptest1(col int PRIMARY KEY); 124CREATE TEMP TABLE temptest2(col int REFERENCES temptest1) 125 ON COMMIT DELETE ROWS; 126INSERT INTO temptest1 VALUES (1); 127INSERT INTO temptest2 VALUES (1); 128COMMIT; 129SELECT * FROM temptest1; 130 col 131----- 132 1 133(1 row) 134 135SELECT * FROM temptest2; 136 col 137----- 138(0 rows) 139 140BEGIN; 141CREATE TEMP TABLE temptest3(col int PRIMARY KEY) ON COMMIT DELETE ROWS; 142CREATE TEMP TABLE temptest4(col int REFERENCES temptest3); 143COMMIT; 144ERROR: unsupported ON COMMIT and foreign key combination 145DETAIL: Table "temptest4" references "temptest3", but they do not have the same ON COMMIT setting. 146-- Test manipulation of temp schema's placement in search path 147create table public.whereami (f1 text); 148insert into public.whereami values ('public'); 149create temp table whereami (f1 text); 150insert into whereami values ('temp'); 151create function public.whoami() returns text 152 as $$select 'public'::text$$ language sql; 153create function pg_temp.whoami() returns text 154 as $$select 'temp'::text$$ language sql; 155-- default should have pg_temp implicitly first, but only for tables 156select * from whereami; 157 f1 158------ 159 temp 160(1 row) 161 162select whoami(); 163 whoami 164-------- 165 public 166(1 row) 167 168-- can list temp first explicitly, but it still doesn't affect functions 169set search_path = pg_temp, public; 170select * from whereami; 171 f1 172------ 173 temp 174(1 row) 175 176select whoami(); 177 whoami 178-------- 179 public 180(1 row) 181 182-- or put it last for security 183set search_path = public, pg_temp; 184select * from whereami; 185 f1 186-------- 187 public 188(1 row) 189 190select whoami(); 191 whoami 192-------- 193 public 194(1 row) 195 196-- you can invoke a temp function explicitly, though 197select pg_temp.whoami(); 198 whoami 199-------- 200 temp 201(1 row) 202 203drop table public.whereami; 204-- types in temp schema 205set search_path = pg_temp, public; 206create domain pg_temp.nonempty as text check (value <> ''); 207-- function-syntax invocation of types matches rules for functions 208select nonempty(''); 209ERROR: function nonempty(unknown) does not exist 210LINE 1: select nonempty(''); 211 ^ 212HINT: No function matches the given name and argument types. You might need to add explicit type casts. 213select pg_temp.nonempty(''); 214ERROR: value for domain nonempty violates check constraint "nonempty_check" 215-- other syntax matches rules for tables 216select ''::nonempty; 217ERROR: value for domain nonempty violates check constraint "nonempty_check" 218reset search_path; 219