1-- 2-- Regression tests for schemas (namespaces) 3-- 4CREATE SCHEMA test_schema_1 5 CREATE UNIQUE INDEX abc_a_idx ON abc (a) 6 CREATE VIEW abc_view AS 7 SELECT a+1 AS a, b+1 AS b FROM abc 8 CREATE TABLE abc ( 9 a serial, 10 b int UNIQUE 11 ); 12-- verify that the objects were created 13SELECT COUNT(*) FROM pg_class WHERE relnamespace = 14 (SELECT oid FROM pg_namespace WHERE nspname = 'test_schema_1'); 15 count 16------- 17 5 18(1 row) 19 20INSERT INTO test_schema_1.abc DEFAULT VALUES; 21INSERT INTO test_schema_1.abc DEFAULT VALUES; 22INSERT INTO test_schema_1.abc DEFAULT VALUES; 23SELECT * FROM test_schema_1.abc; 24 a | b 25---+--- 26 1 | 27 2 | 28 3 | 29(3 rows) 30 31SELECT * FROM test_schema_1.abc_view; 32 a | b 33---+--- 34 2 | 35 3 | 36 4 | 37(3 rows) 38 39ALTER SCHEMA test_schema_1 RENAME TO test_schema_renamed; 40SELECT COUNT(*) FROM pg_class WHERE relnamespace = 41 (SELECT oid FROM pg_namespace WHERE nspname = 'test_schema_1'); 42 count 43------- 44 0 45(1 row) 46 47-- test IF NOT EXISTS cases 48CREATE SCHEMA test_schema_renamed; -- fail, already exists 49ERROR: schema "test_schema_renamed" already exists 50CREATE SCHEMA IF NOT EXISTS test_schema_renamed; -- ok with notice 51NOTICE: schema "test_schema_renamed" already exists, skipping 52CREATE SCHEMA IF NOT EXISTS test_schema_renamed -- fail, disallowed 53 CREATE TABLE abc ( 54 a serial, 55 b int UNIQUE 56 ); 57ERROR: CREATE SCHEMA IF NOT EXISTS cannot include schema elements 58LINE 2: CREATE TABLE abc ( 59 ^ 60DROP SCHEMA test_schema_renamed CASCADE; 61NOTICE: drop cascades to 2 other objects 62DETAIL: drop cascades to table test_schema_renamed.abc 63drop cascades to view test_schema_renamed.abc_view 64-- verify that the objects were dropped 65SELECT COUNT(*) FROM pg_class WHERE relnamespace = 66 (SELECT oid FROM pg_namespace WHERE nspname = 'test_schema_renamed'); 67 count 68------- 69 0 70(1 row) 71 72