1-- 2-- Regression tests for schemas (namespaces) 3-- 4 5CREATE SCHEMA test_ns_schema_1 6 CREATE UNIQUE INDEX abc_a_idx ON abc (a) 7 8 CREATE VIEW abc_view AS 9 SELECT a+1 AS a, b+1 AS b FROM abc 10 11 CREATE TABLE abc ( 12 a serial, 13 b int UNIQUE 14 ); 15 16-- verify that the objects were created 17SELECT COUNT(*) FROM pg_class WHERE relnamespace = 18 (SELECT oid FROM pg_namespace WHERE nspname = 'test_ns_schema_1'); 19 20INSERT INTO test_ns_schema_1.abc DEFAULT VALUES; 21INSERT INTO test_ns_schema_1.abc DEFAULT VALUES; 22INSERT INTO test_ns_schema_1.abc DEFAULT VALUES; 23 24SELECT * FROM test_ns_schema_1.abc; 25SELECT * FROM test_ns_schema_1.abc_view; 26 27ALTER SCHEMA test_ns_schema_1 RENAME TO test_ns_schema_renamed; 28SELECT COUNT(*) FROM pg_class WHERE relnamespace = 29 (SELECT oid FROM pg_namespace WHERE nspname = 'test_ns_schema_1'); 30 31-- test IF NOT EXISTS cases 32CREATE SCHEMA test_ns_schema_renamed; -- fail, already exists 33CREATE SCHEMA IF NOT EXISTS test_ns_schema_renamed; -- ok with notice 34CREATE SCHEMA IF NOT EXISTS test_ns_schema_renamed -- fail, disallowed 35 CREATE TABLE abc ( 36 a serial, 37 b int UNIQUE 38 ); 39 40DROP SCHEMA test_ns_schema_renamed CASCADE; 41 42-- verify that the objects were dropped 43SELECT COUNT(*) FROM pg_class WHERE relnamespace = 44 (SELECT oid FROM pg_namespace WHERE nspname = 'test_ns_schema_renamed'); 45